Database Lesson 3

Once we've found and said the age of the PIN code received, it would be good if the user could update their age value if it's incorrect.

Sample Files:
- Samples\database_3.py
The application

As before, we create a connection to the database, ask the user for a PIN code, and make a query. We then say the age value. After using TTS, we then use DTMF detection to determine if the user wishes to update their value. If the DTMF value is the same as the entry we are looking for, we call the update_pin_code subroutine. This again uses the channel.DTMFDetector.get_digits() function to accept a DTMF string.

Update - a warning

The age string has the last end character stripped off of it, and then a query is compiled. We need to be extremely careful when using the update command. Unless a where clause is used, this command will update every single row with its specified value.

Verification

Our query does an update on the users table, with the age acquired from the DTMF input, with the PIN code we've already been told. You can use the MySQL command utility to do a select on the table after changing the age value from the telephony application, or simply ring the application again using the same PIN code.

"""
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_3 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 update_pin_code(channel, pin, cursor, my_log):

    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
        my_log.info("""update users set age={0} where pin={1};""".format(dtmf[:-1],pin))
        cursor.execute(sql);

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, my_log)
                        conn.commit()
            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#\Database3\Database3.cs
The Application

As before, we create a connection to the database, ask the user for a PIN code and make a query. We then say the age value back to the caller. We then use DTMF detection to determine if the user wishes to update their value. In the UpdatePINCode method channel.DTMFDetector.GetDigits() is used to accept a new age value from the caller.

Update - a Warning

The UpdatePINCode method generates a query string for updating the PIN number we're interested in. We need to be careful when using the update command. Unless a where clause is used, this command will update every single row of the database with its specified value.

Executing the Command

We execute the command string using the SqlCommand.ExecuteNonQuery() method. When using this call, we do not expect any response. The call is made in a try/catch block, as this call will throw an exception if an error occurs.

Verification

Our query performs an update on the users table, with the age acquired from the DTMF input, for the defined PIN code. To check the result you can use the MySQL command utility to do a select on the table after changing the age value from the telephony application, or simply ring the application again using the same PIN code.

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.
namespace Database3
{
    // 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 Database3 : UASInboundApplication
    {
        // Possible return codes
        enum ReturnCode
        {
            // Success Codes:
            Success = 0,
            // ... any positive integer

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

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

            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)
            {
                string command = "update users set age=" + age.Remove(age.Length - 1, 1) +
                    " 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;
        }

        // 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) && (menu[0] == '1'))
                            {
                                reply = UpdatePINCode(channel, digits, myConnection);
                            }
                        }
                        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();
                }

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

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

Sample Files:
- Samples\VB\Database3\Database3.vb
The Application

As before, we create a connection to the database, ask the user for a PIN code and make a query. We then say the age value back to the caller. We then use DTMF detection to determine if the user wishes to update their value. In the UpdatePINCode method channel.DTMFDetector.GetDigits() is used to accept a new age value from the caller.

Update - a Warning

The UpdatePINCode method generates a query string for updating the PIN number we're interested in. We need to be careful when using the update command. Unless a where clause is used, this command will update every single row of the database with its specified value.

Executing the Command

We execute the command string using the SqlCommand.ExecuteNonQuery() method. When using this call, we do not expect any response. The call is made in a try/catch block, as this call will throw an exception if an error occurs.

Verification

Our query performs an update on the users table, with the age acquired from the DTMF input, for the defined PIN code. To check the result you can use the MySQL command utility to do a select on the table after changing the age value from the telephony application, or simply ring the application again using the same PIN code.

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.
Namespace Database3

    ' 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 Database3
        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 UpdatePINCode(ByVal channel As UASCallChannel, ByVal PIN As String, ByVal db As SqlConnection) As ReturnCode
            Dim reply = ReturnCode.Success

            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
                Dim command = "update users set age=" + age.Remove(age.Length - 1, 1) + _
                              " 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

        ' 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) And (menu(0) = "1") Then
                                reply = UpdatePINCode(channel, digits, myConnection)
                            End If
                        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