Database Lesson 2

We are going to create a telephony system which asks the user for a four digit PIN code. Once we have a valid value, we will query our database to find the age for the particular PIN code entry. The age will then be said, using TTS.

Sample Files:
- Samples\database_2.py
Connecting to the database

The MySQL database offers a socket based connection for updating and sending queries. This gives us the advantage of having the database server on any remote machine, not necessarily the UAS server host.

To connect to the database system, use the MySQLdb.connect() function. This function takes in several optional arguments, including the hostname of the machine with the MySQL server, the user and password for a particular user set up with access to the database, and the default database name to use when connected.

With a connection to the database, we can now create a "cursor" object. This object then allows us to execute statements to the database. Creating the cursor is as simple as calling the cursor() function.

Getting a PIN code

For this application, we have use the channel.DTMFDetector.get_digits() function to acquire a four digit pin code. This string value will be part of the query we will submit to the database.

Making a query

Our query is made up of a "select" statement and a "where" clause, specifying the pin number found by our get_digits() function. We have to call the fetchall() function after making an execute subroutine, which returns the result set.

The result is returned as a tuple, and in Python, we can determine the number of rows returned from checking the "len" value. In our example, we check that the number of rows returned is one, then say the returned age value of the query.

"""
This application is part of the online database tutorial.
"""

# mysql is available at https://dev.mysql.com/downloads/connector/python
try:
    import mysql.connector
except:
    raise Exception("database_2 Needs mysql which is available at https://dev.mysql.com/downloads/connector/python")

from prosody.uas import Hangup, Error

__uas_version__ = "0.0.1"
__uas_identify__ = "application"

def main(channel, application_instance_id, file_man, my_log, application_parameters):
    return_code = 0

    try:

        channel.ring(2)
        channel.answer()

        channel.FilePlayer.say("Welcome to the database example. Please enter a four digit PIN code")

        pin = channel.DTMFDetector.get_digits(count=4, seconds_predigits_timeout=30)

        if channel.DTMFDetector.cause() != channel.DTMFDetector.Cause.COUNT:
            channel.FilePlayer.say("No valid input detected")
        else:
            conn = mysql.connector.connect(host="localhost", user="root", passwd="aculab", db="test")
            c = conn.cursor()
            c.execute("""select * from users where pin={0};""".format(pin))
            data = c.fetchall()

            if len(data) == 1:
                # One found, that's normal!
                channel.FilePlayer.say("You have been found in the database. Your age entry value is {0}.".format(data[0][1]))
            else:
                channel.FilePlayer.say("No entry in database")

        channel.FilePlayer.say("Good bye")

    except Hangup as exc:
        my_log.info("Hangup exception reports: {0}".format(exc))
         # in this app a hangup is not an error, return a positive value
        return_code = 100

    except Error as exc:
        # for error conditions return a negative value
        my_log.error("Error exception reports: {0}".format(exc))
        return_code = -101

    except Exception as exc:
        # an unexpected exception, return a negative value
        my_log.exception("Unexpected exception reports: {0}".format(exc))
        return_code = -102

    finally:
        if channel.state() != channel.State.IDLE:
            channel.hang_up()

    return return_code

Sample Files:
- Samples\C#\Database2\Database2.cs
Developing with SQL

Once you've created your project using the UASInboundApplication template, you will need to add a reference to allow you to develop Database applications. In Visual Studio Solution Explorer, right click on References and select Add Reference.... Highlight the System.Data entry in the .NET section and select OK. Make sure the System.Data entry appears in the References list of your project before proceeding.

Connection to the Database

We create a connection to our database by invoking an instance of the SqlConnection class. This requires a string parameter, formed from the server instance (SQLEXPRESS), a Trusted_Connection argument, the default database name and a connection timeout. We then open the connection by using the Open() method on this object.

Making a Query

After reading a PIN code using DTMFDetector, we create an SQL command from an SQL query string and the database connection.

Execute the Statement

Once the query has been generated we execute the statement using the SqlCommand.ExecuteReader() method. We can check if any items have been returned using the HasRows property.

Reading the Results

To read the results, we use the Read() method, that returns a boolean to indicate if the command was successful. Table columns are associated with the returned data, so we use myReader["age"] in this example to access this field.

Closing the Database

After we have finished with the database, the connection should be closed using SqlConnection.Close().

using System;
using System.Threading;
using AMSClassLibrary;
using UASAppAPI;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

// An inbound application that opens a local database, answers the call and
// prompts the caller to enter a four digit pin code. It then queries the database
// for the age associated with the entered pin code and says it to the caller.
namespace Database2
{
    // The application class.
    // This must have the same name as the assembly and must inherit from either
    // UASInboundApplication or UASOutboundApplication.
    // It must override the Run method.
    public class Database2 : UASInboundApplication
    {
        // Possible return codes
        enum ReturnCode
        {
            // Success Codes:
            Success = 0,
            // ... any positive integer

            // Fail Codes:
            // -1 to -99 reserved
            ExceptionThrown = -100
        }

        // This is the entry point for the application
        public override int Run(UASCallChannel channel,
                                string applicationParameters)
        {
            this.Trace.TraceInfo("Started");
            ReturnCode reply = ReturnCode.Success;
            SqlConnection myConnection = null;

            try
            {
                // Create Database Connection Object
                myConnection = new SqlConnection("server=.\\SQLEXPRESS;" +
                                                 "Trusted_Connection=yes;" +
                                                 "database=test; " +
                                                 "connection timeout=30");

                // Make the connection
                myConnection.Open();

                // Answer the call
                CallState state = channel.Answer();
                if (state == CallState.Answered)
                {
                    this.Trace.TraceInfo("Call answered");

                    // Prompt for a pin code, allowing barge in
                    channel.FilePlayer.Say("Welcome to the database example. " +
                            "Please enter a four digit PIN code.", true);

                    // Allow the user to enter a four digit PIN code
                    string digits;
                    DtmfDetectorCause cause = channel.DtmfDetector.GetDigits(4, out digits, 30);
                    if (cause == DtmfDetectorCause.Count)
                    {
                        SqlDataReader myReader = null;

                        // Create the SQL search query
                        SqlCommand myCommand = new SqlCommand("select age from users where pin=" + digits,
                                                                 myConnection);
                        // Create a reader object
                        myReader = myCommand.ExecuteReader();

                        // Check if there are any elements returned
                        if (myReader.HasRows)
                        {
                            // Read the row data
                            while (myReader.Read())
                            {
                                string age = myReader["age"].ToString();
                                this.Trace.TraceInfo("Data {0}", age);

                                // Say the age corresponding to the supplied pin code
                                channel.FilePlayer.Say("You have been found in the database. Your age entry value is " + age);

                                // Also send the age via dtmf digits
                                channel.DtmfPlayer.Play(age);
                            }
                        }
                        else
                        {
                            channel.FilePlayer.Say("No entry in database");
                        }
                    }
                    else
                    {
                        channel.FilePlayer.Say("No valid input detected");
                    }

                    channel.FilePlayer.Say("Goodbye.");
                }
            }
            catch (Exception except)
            {
                this.Trace.TraceError("Exception thrown {0}", except.Message);
                reply = ReturnCode.ExceptionThrown;
            }
            finally
            {
                // Close the database connection
                if (myConnection != null)
                {
                    myConnection.Close();
                }

                // Hangup the call
                channel.HangUp();
            }

            this.Trace.TraceInfo("Completed");
            return (int)reply;
        }
    }
}

Sample Files:
- Samples\VB\Database2\Database2.vb
Developing with SQL

Once you've created your project using the UASInboundApplication template, you will need to add a reference to allow you to develop Database applications. In Visual Studio Solution Explorer, double click on My Project and select the References tab. Click on Add..., highlight the System.Data entry in the .NET section and select OK. Make sure the System.Data entry appears in the References list of your project before proceeding.

Connection to the Database

We create a connection to our database by invoking an instance of the SqlConnection class. This requires a string parameter, formed from the server instance (SQLEXPRESS), a Trusted_Connection argument, the default database name and a connection timeout. We then open the connection by using the Open() method on this object.

Making a Query

After reading a PIN code using DTMFDetector, we create an SQL command from an SQL query string and the database connection.

Execute the Statement

Once the query has been generated we execute the command using the SqlCommand.ExecuteReader() method. We can check if any items have been returned using the HasRows property.

Reading the Results

To read the results, we use the Read() method, that returns a boolean to indicate if the command was successful. Table columns are associated with the returned data, so we use myReader("age") in this example to access this field.

Closing the Database

After we have finished with the database, the connection should be closed using SqlConnection.Close().

Imports AMSClassLibrary
Imports UASAppAPI
Imports System.Data.SqlClient

' An inbound application that opens a local database, answers the call and
' prompts the caller to enter a four digit pin code. It then queries the database
' for the age associated with the entered pin code and says it to the caller.
Namespace Database2

    ' The application class.
    ' This must have the same name as the assembly and must inherit from either
    ' UASInboundApplication or UASOutboundApplication.
    ' It must override the Run method.
    Public Class Database2
        Inherits UASInboundApplication

        ' Possible return codes
        Enum ReturnCode
            ' Success Codes:
            Success = 0
            ' ... any positive integer

            ' Fail Codes:
            ' -1 to -99 reserved
            ExceptionThrown = -100
        End Enum

        ' This is the entry point for the application
        Overrides Function Run(ByVal channel As UASCallChannel, _
                               ByVal applicationParameters As String) _
                               As Integer

            Me.Trace.TraceInfo("Started")
            Dim reply As ReturnCode = ReturnCode.Success
            Dim myConnection As SqlConnection = Nothing

            Try
                ' Create Database Connection Object
                myConnection = New SqlConnection("server=.\SQLEXPRESS;" + _
                                                 "Trusted_Connection=yes;" + _
                                                 "database=test; " + _
                                                 "connection timeout=30")

                ' Make the connection
                myConnection.Open()

                ' Answer the call
                Dim state As CallState
                state = channel.Answer()
                If state = CallState.Answered Then
                    Me.Trace.TraceInfo("Call answered")

                    ' Prompt for a pin code, allowing barge in
                    channel.FilePlayer.Say("Welcome to the database example. " + _
                                           "Please enter a four digit PIN code.", True)

                    ' Allow the user to enter a four digit PIN code
                    Dim digits As String = ""
                    Dim cause = channel.DtmfDetector.GetDigits(4, digits, 30)
                    If cause = DtmfDetectorCause.Count Then
                        Dim myReader As SqlDataReader = Nothing

                        ' Create the SQL search query
                        Dim myCommand = New SqlCommand("select age from users where pin=" + digits, _
                                                                 myConnection)
                        ' Create a reader object
                        myReader = myCommand.ExecuteReader()

                        ' Check if there are any elements returned
                        If myReader.HasRows Then
                            ' Read the row data
                            While myReader.Read()
                                Dim age = myReader("age").ToString()
                                Me.Trace.TraceInfo("Data {0}", age)

                                ' Say the age corresponding to the supplied pin code
                                channel.FilePlayer.Say("You have been found in the database. " + _
                                                       "Your age entry value is " + age)

                                ' Also send the age via dtmf digits
                                channel.DtmfPlayer.Play(age)
                            End While
                        Else
                            channel.FilePlayer.Say("No entry in database")
                        End If
                    Else
                        channel.FilePlayer.Say("No valid input detected")
                    End If

                    channel.FilePlayer.Say("Goodbye.")
                End If

            Catch ex As Exception
                Me.Trace.TraceError("Exception thrown {0}", ex.Message)
                reply = ReturnCode.ExceptionThrown
            Finally
                ' Close the database connection
                If Not myConnection Is Nothing Then
                    myConnection.Close()
                End If

                ' Hangup the call
                channel.HangUp()
            End Try

            Me.Trace.TraceInfo("Completed")
            Return reply

        End Function

    End Class

End Namespace

Next time...

Now that we have a basic structure of an application, we will next extend the program to be able to update the age value, from a value entered by the user.

Lesson 3