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
                                  

Next time...

Now that the user is able to update the age value, we will next extend the program to allow new users to add themselves to the database.

Lesson 4