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\UASApplications\Database2\src\main\java\com\aculab\uas\app\Database2.java

      Connection to the Database

      We create a connection to our local database 'test' using the java.sql.DriverManager. Then we execute a 'use test' command.

      Making a Query

      After answering the call and reading a PIN code using the DTMFDetector, we prepare an SQL command to extract the age for the user specified by the entered PIN code.

      Execute the Statement

      We execute the statement using the executeQuery method and check the returned results to see if any items have been returned.

      Reading the Results

      We iterate through the results to obtain the age field of the selected entry.

      Closing the Database

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

    • package com.aculab.uas.app;
      
      // 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.
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.PreparedStatement;
      import java.sql.ResultSet;
      
      import com.aculab.amsapi.*;
      import com.aculab.uasapi.*;
      
      // The application class.
      // This must extend either UASInboundApplication or UASOutboundApplication and
      // override the run method.
      // It must be packaged in a jar file of the same name together with a manifest file.
      public class Database2 extends UASInboundApplication
      {
          // Success Codes:
          final int Success = 0;
          // ... any positive integer
      
          // Fail Codes:
          // -1 to -99 reserved
          final int ExceptionThrown = -100;
      
          // This is the entry point for the application
          @Override
          public int run(UASCallChannel channel,
                         String applicationParameters)
              throws Exception
          {
              this.getTrace().traceInfo("Start - appParms [%s]", applicationParameters);
              int returnCode = Success;
      
              Connection myConnection = null;
      
              try
              {
                  // Create Database Connection Object
                  String databaseName = "test";
                  String con = String.format("jdbc:mysql://localhost/%s?user=%s&password=%s",
                          databaseName, "user", "password");
                  Class.forName("com.mysql.jdbc.Driver");
                  myConnection = DriverManager.getConnection(con);
      
                  PreparedStatement myCommand = myConnection.prepareStatement("use " + databaseName);
                  myCommand.execute();
      
                  // Answer the call
                  CallState state = channel.answer();
                  if (state == CallState.Answered)
                  {
                      this.getTrace().traceInfo("Call answered");
      
                      // Prompt for a pin code, allowing barge in
                      channel.getFilePlayer().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 = channel.getDtmfDetector().getDigits(4, 30);
                      DtmfDetectorCause cause = channel.getDtmfDetector().getCause();
                      if (cause == DtmfDetectorCause.Count)
                      {
                          // Create the SQL search query
                          PreparedStatement command = myConnection.prepareStatement(
                                  String.format("select age from users where pin=%s", digits));
                          ResultSet results = command.executeQuery();
      
                          // Read the results
                          if (results.first())
                          {
                              do
                              {
                                  String age = results.getString("age");
                                  this.getTrace().traceInfo("Data %s", age);
      
                                  // Say the age corresponding to the supplied pin code
                                  channel.getFilePlayer().say("You have been found in the database. Your age entry value is " + age);
      
                                  // Also send the age via dtmf digits
                                  channel.getDtmfPlayer().play(age);
                              } while (results.next());
      
                              // Close the results object.
                              results.close();
                          }
                          else
                          {
                              channel.getFilePlayer().say("No entry in database");
                          }
                      }
                      else
                      {
                          channel.getFilePlayer().say("No valid input detected");
                      }
      
                      channel.getFilePlayer().say("Goodbye.");
                  }
                  else
                  {
                      channel.getFilePlayer().say("No valid input detected");
                  }
              }
              catch (Exception except)
              {
                  this.getTrace().traceError("Exception thrown %s", except.getMessage());
                  returnCode = ExceptionThrown;
              }
              finally
              {
                  // Close the database connection
                  if (myConnection != null)
                  {
                      myConnection.close();
                  }
      
                  // Hang up the call
                  channel.hangUp();
              }
      
              this.getTrace().traceInfo("Completed with return code %d", returnCode);
              return returnCode;
          }
      }
                                  
    • 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