Database Lesson 4

One thing we haven't covered so far in our telephony application is the ability of users to add themselves to the database if no entry already exists.

Sample Files:
- Samples\database_4.py
Updating the code

We now have a general get_age() function which does the DTMF detection for an age, and strips out the end key before returning the string.

Updating a PIN code has been moved to a separate subroutine, update_pin_code.

Insertion into the DB

For inserting into the database, we first check if the user has a PIN code in the database. If there is no entry found, the user is asked if they would like to add an entry using the keypad. Once the age has been entered, the create_new_entry subroutine is called.

This creates a query string with the insert into method, with the PIN code and age as arguments.

"""
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_4 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 get_age(channel):
    channel.FilePlayer.say("Please enter an age entry, followed by the hash key")
    dtmf = channel.DTMFDetector.get_digits(end='#', clear=True,
                                           seconds_predigits_timeout=10, seconds_interdigit_timeout=30)
    cause = channel.DTMFDetector.cause()

    if cause == channel.DTMFDetector.Cause.END:
        # Strip off the last end character
        return dtmf[:-1]
    else:
        return None

def update_pin_code(channel, pin, cursor):
    age = get_age(channel)
    if age:
        cursor.execute("""update users set age={0} where pin={1};""".format(age,pin));

def create_new_entry(channel, pin, cursor, my_log):
    age = get_age(channel)
    if age:
        cursor.execute("""insert into users values({0},{1});""".format(pin,age));

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")
            cursor = conn.cursor()
            cursor.execute("""select * from users where pin={0};""".format(pin))
            data = cursor.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]))

                channel.FilePlayer.say("Press 1 to alter this entry")
                digit = channel.DTMFDetector.get_digits(count=1, seconds_predigits_timeout=30)
                if channel.DTMFDetector.cause() == channel.DTMFDetector.Cause.COUNT:
                    if digit == '1':
                        update_pin_code(channel, pin, cursor)
                        conn.commit()
            else:
                channel.FilePlayer.say("No entry in database. Press 1 to create a new entry with this pin code")
                digit = channel.DTMFDetector.get_digits(count=1, seconds_predigits_timeout=30)
                if channel.DTMFDetector.cause() == channel.DTMFDetector.Cause.COUNT:
                    if digit == '1':
                        create_new_entry(channel, pin, cursor, my_log)
                        conn.commit()

        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#\Database4\Database4.cs
Updating the Code

We now have a general GetAge() method that performs the DTMF detection to obtain a DTMF string from the caller and strips out the end key before returning the new age value.

Updating a PIN code has been moved to a separate method UpdatePINCode.

Insertion into the Database

To create a new entry, the new method CreateNewEntry has been added. This works primarily in the same way that UpdatePINCode() does, apart from the SQL query. It uses an insert command here, taking the PIN code and entered age as parameters.

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.
// The caller can then choose to enter a new age into the database.
// If the pin code isn't in the database the caller has the option to add
// the new pin code and enter an age to be associated with it.
namespace Database4
{
    // 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 Database4 : UASInboundApplication
    {
        // Possible return codes
        enum ReturnCode
        {
            // Success Codes:
            Success = 0,
            // ... any positive integer

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

        private string GetAge(UASCallChannel channel)
        {
            channel.FilePlayer.Say("Please enter an age entry, " +
                   "followed by the hash key", true);

            // Allow the user to enter an age value
            string age;
            DtmfDetectorCause cause = channel.DtmfDetector.GetDigits(
                                    0, new char[] { '#', '*' }, out age, false, 10, 30);

            if (cause == DtmfDetectorCause.End)
            {
                return age.Remove(age.Length - 1, 1);
            }
            return null;
        }

        private ReturnCode UpdatePINCode(UASCallChannel channel, string PIN, SqlConnection db)
        {
            ReturnCode reply = ReturnCode.Success;

            string age = GetAge(channel);
            if (age != null)
            {
                string command = "update users set age=" + age +
                    " where pin=" + PIN + ";";

                try
                {
                    SqlCommand myCommand = new SqlCommand(command, db);
                    myCommand.ExecuteNonQuery();

                    channel.FilePlayer.Say("The database has been updated.");
                }
                catch (Exception except)
                {
                    this.Trace.TraceError("Exception thrown {0}", except.Message);
                    reply = ReturnCode.ExceptionThrown;
                }
            }

            return reply;
        }

        private ReturnCode CreateNewEntry (UASCallChannel channel, string PIN, SqlConnection db)
        {
            ReturnCode reply = ReturnCode.Success;

            string age = GetAge(channel);
            if (age != null)
            {
                string command = "insert into users values (" + PIN + "," + age + ")";

                try
                {
                    SqlCommand myCommand = new SqlCommand(command, db);
                    myCommand.ExecuteNonQuery();

                    channel.FilePlayer.Say("The database has been updated.");
                }
                catch (Exception except)
                {
                    this.Trace.TraceError("Exception thrown {0}", except.Message);
                    reply = ReturnCode.ExceptionThrown;
                }
            }

            return reply;
        }


        // 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);
                            }

                            // Close the reader object.
                            myReader.Close();

                            channel.FilePlayer.Say("Press 1 to alter this entry", true);

                            string menu;
                            cause = channel.DtmfDetector.GetDigits(1, out menu, 30);
                            if (cause == DtmfDetectorCause.Count)
                            {
                                if (menu[0] == '1')
                                {
                                    reply = UpdatePINCode(channel, digits, myConnection);
                                }
                            }
                        }
                        else
                        {
                            // Close the reader object
                            myReader.Close();

                            channel.FilePlayer.Say("No entry in database. Press 1 to create a " +
                                "new entry with this pin code", true);

                            string menu;
                            cause = channel.DtmfDetector.GetDigits(1, out menu, 30);
                            if (cause == DtmfDetectorCause.Count)
                            {
                                if (menu[0] == '1')
                                {
                                    reply = CreateNewEntry(channel, digits, myConnection);
                                }
                            }
                        }
                    }
                    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();
                }

                // Hang up the call
                channel.HangUp();
            }

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

Sample Files:
- Samples\VB\Database4\Database4.vb
Updating the Code

We now have a general GetAge() method that performs the DTMF detection to obtain a DTMF string from the caller and strips out the end key before returning the new age value.

Updating a PIN code has been moved to a separate method UpdatePINCode.

Insertion into the Database

To create a new entry, the new method CreateNewEntry has been added. This works primarily in the same way that UpdatePINCode() does, apart from the SQL query. It uses an insert command here, taking the PIN code and entered age as parameters.

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.
' The caller can then choose to enter a new age into the database.
' If the pin code isn't in the database the caller has the option to add
' the new pin code and enter an age to be associated with it.
Namespace Database4

    ' 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 Database4
        Inherits UASInboundApplication

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

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

        Private Function GetAge(ByVal channel As UASCallChannel) As String
            channel.FilePlayer.Say("Please enter an age entry, " + _
                   "followed by the hash key", True)

            ' Allow the user to enter an age value
            Dim age As String = ""
            Dim cause = channel.DtmfDetector.GetDigits(0, New Char() {"#", "*"}, age, False, 10, 30)

            If cause = DtmfDetectorCause.End Then
                Return age.Remove(age.Length - 1, 1)
            End If

            Return Nothing
        End Function

        Private Function UpdatePINCode(ByVal channel As UASCallChannel, ByVal PIN As String, ByVal db As SqlConnection) As ReturnCode
            Dim reply = ReturnCode.Success

            Dim age = GetAge(channel)
            If Not age Is Nothing Then
                Dim command = "update users set age=" + age + _
                    " where pin=" + PIN + ";"

                Try
                    Dim myCommand = New SqlCommand(command, db)
                    myCommand.ExecuteNonQuery()

                    channel.FilePlayer.Say("The database has been updated.")
                Catch except As Exception
                    Me.Trace.TraceError("Exception thrown {0}", except.Message)
                    reply = ReturnCode.ExceptionThrown
                End Try
            End If

            Return reply
        End Function

        Private Function CreateNewEntry(ByVal channel As UASCallChannel, ByVal PIN As String, ByVal db As SqlConnection) As ReturnCode
            Dim reply = ReturnCode.Success

            Dim age = GetAge(channel)
            If Not age Is Nothing Then
                Dim command = "insert into users values (" + PIN + "," + age + ")"

                Try
                    Dim myCommand = New SqlCommand(command, db)
                    myCommand.ExecuteNonQuery()

                    channel.FilePlayer.Say("The database has been updated.")
                Catch except As Exception
                    Me.Trace.TraceError("Exception thrown {0}", except.Message)
                    reply = ReturnCode.ExceptionThrown
                End Try
            End If

            Return reply
        End Function

        ' 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

                            ' Close the reader object.
                            myReader.Close()

                            channel.FilePlayer.Say("Press 1 to alter this entry", True)

                            Dim menu As String = ""
                            cause = channel.DtmfDetector.GetDigits(1, menu, 30)
                            If cause = DtmfDetectorCause.Count Then
                                If (menu(0) = "1") Then
                                    reply = UpdatePINCode(channel, digits, myConnection)
                                End If
                            End If
                        Else
                            ' Close the reader object
                            myReader.Close()

                            channel.FilePlayer.Say("No entry in database. Press 1 to create a " + _
                                                   "new entry with this pin code", True)

                            Dim menu As String = ""
                            cause = channel.DtmfDetector.GetDigits(1, menu, 30)
                            If cause = DtmfDetectorCause.Count Then
                                If (menu(0) = "1") Then
                                    reply = CreateNewEntry(channel, digits, myConnection)
                                End If
                            End If
                        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

That's all...

In this tutorial you will have seen how to use a database system with an Aculab Cloud UAS application. Making queries, updating and inserting data are the fundamentals of databases. Complex queries, such as linking multiple tables together are not covered by this tutorial, but are certainly possible using the query commands mentioned above.