IVR Database Integration

Please note that database access can be done using the built-in Database action. This sample is included here to show it can be done through Java action also.

This sample extends the Password Protected Info sample by checking the caller password using a relational database.

You can open this design by selecting Library > Password Protected Info (Use Database) from the program main menu. You can also
download the call flow and java file here.

The call flow consists of two Digits elements, two Prompt elements, and one Go-To element. The fist Digits element (Account Number) asks a caller to enter his or her account number; the second
Digits element (Enter Password) asks for password; The password is then checked against information saved in a relational database. If the password is correct, the secret information is played over the phone. If not, the system asks
the caller to enter account number and password again for a maximum of three times.

You can extend Voicent IVR platform by integrating it with web sites or any other software products. There are two basic methods (actions): HTTP or Java. HTTP is the protocol for the Internet. It is best used for web site
integration. Java is a general programming language. It can be used to extend Voicent IVR platform with virtually no limit. In this sample, we use the Java method to connect to a relational database using JDBC.

The user account number and password are saved in two application variables: account_number and account_password. These two variables are declared in
the root element; account_number is set in element Enter Password by setting

account_number = __LAST_VG_RESULT__

The __LAST_VG_RESULT__ is a system variable. It contains the latest caller DTMF input or recordings. Please note that this variable is normally collect in one element (Account Number) and used by the action of the
next element (Enter Password). The reason is that actions are executed before any other operations for an element.

The account_password variable is set in a similar fashion.

Open the property page (Right mouse click on an element, then choose Properties…
from the popup menu.) of the Protected Info element, select the Action tab, then click the Edit button.

The Java action for checking database is shown below:

Here the Java action is invoking Java method check of the class voicent.sample.MyDbPasswordChecker. The class is listed below and included in the Jar file: dbinteg.jar and it also requires additional JDBC driver jar
file: sqljdbc.jar. (This is Microsoft SQL Server 2005 driver. You need to include different JDBC driver jar file if you are using different relational databases, such as MYSQL)

The check method has two arguments, both of which have the type java.lang.String and the values are account_number and account_password respectively.

When the system executes the action, it first loads all the Jar files; then it loads the Java Class specified and creates an instance of the class, and finally executes the specified method with the arguments.

If you want to check or use return values of a Java action, the return must be the Properties Class. The returned value can be access in the IVR Studio using the dot notation. For example, if the Java action
is named check_password, and the status return code is named “status_code”, then it can be accessed as check_password.status_code. We can use the return value
for prompt or as an argument for other actions.

The default return value is named “result”. This is used for methods with primitive return type. For example, the return type of check method is boolean,
the system automatically saves the return value in “result”.

In this sample, the result is saved in a variable passed in the following action:

passed = check_password.result

The passed variable is then used in the transition from element Enter Password to elementProtected Info as shown in the call flow diagram.

This sample assumes you have the relational database, such as Microsoft SQL server or MySQL, setup already. What you need is to have the correct JDBC driver for your database.

This sample assume you have a database instance called: AP, and a table called AccountPassword, and two columns: Account_Id, and Account_Password. You should populate some initial data like the following:

If you are using Microsoft SQL Server, please make sure you have enabled the TCP/IP connection for your database. Select Microsoft SQL Server 2005>Configuration Tools>SQL Server Configuration Manager. In
the SQL Server Configuration Manager select the node SQL Server 2005 Network Configuration>Protocols for SQLEXPRESS. Right-click on the TCP/IP node and select Enable. Restart the
SQL Server (SQLEXPRESS) service. In Adminstrative Tools>Services, right-click on the SQL Server (SQLEXPRESS) service and select Restart.

Connection URL for the default SQL Server 2005 database is jdbc:sqlserver://localhost:<port>. In the connection URL, <port> is obtained from the SQL Server configuration Manager.
When the SQL Server 2005 SQLEXPRESS is restarted the port number changes. To obtain the <port> in the SQL Server Configuration Manager, select the node SQL Server 2005 Network
Configuration > Protocols for SQLEXPRESS. Right-click on TCP/IP node and select Properties. Select the IP Addresses tab. In IP ALL, the TCP Dynamic Ports specifies the <port> value.

The Java class is included here. You can find the dbinteg.jar file inside the folder: C:\Program Files\Voicent\IVRStudio\Samples.

package voicent.sample;

import java.sql.*;

public class MyDbPasswordChecker
{
  // replace the following with your settings

  static final String CONN_URL =
                                    
“jdbc:sqlserver://localhost:2424;databaseName=AP;”;
  static final String DB_USER = “your database username”;
  static final String DB_PASSWORD = “your database password”;

  // to return multiple values, use
java.util.Properties as return type
  // please see ivr studio tutorial or document for more details

  public boolean check(String accountNumber, String passwd)
  {
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;

    boolean passwdOK = false;

    try {
      // Establish the connection.
     
Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);
      con = DriverManager.getConnection(CONN_URL,
DB_USER, DB_PASSWORD);

      // Create and execute an
SQL statement
      String sqlst = “SELECT Account_Password FROM
AccountPassword ” +
                         
” WHERE Account_Id = ‘” + accountNumber + “‘”;

      stmt = con.createStatement();
      rs = stmt.executeQuery(sqlst);

      // Check the return data
      if (rs.next()) {
        String passwdSaved =
rs.getString(1).trim();
        if (passwdSaved.equals(passwd.trim()))
          passwdOK = true;
      }

      rs.close();
      stmt.close();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    finally {
      if (con != null) try { con.close(); }
catch(Exception e) {}
    }

    return passwdOK;
  }

  // test and customize this program independent of
IVR Studio
  // requires MSSQL 2005 JDBC driver library: sqljdbc.jar (free download
from   microsoft)

  public static void main(String args[])
  {
    if (args.length < 2)
      return;

    MyDbPasswordChecker checker = new MyDbPasswordChecker();
    boolean rc = checker.check(args[0], args[1]);

    System.out.println(“The result is: ” + Boolean.toString(rc));
  }
}
 

1). To use this class, you first need to
configure the following values correctly:

  static final String CONN_URL
=
                                    
“jdbc:sqlserver://localhost:2424;databaseName=AP;”;
  static final String DB_USER = “your database username”;
  static final String DB_PASSWORD = “your database password”;

For more information, please check the
documentation for your JDBC driver for the connection URL and
other connection parameters.

2). Once set, you should compile this
class:

  javac -classpath %CP% -d .
MyDbPasswordChecker.java

Your class path %CP% should include the
jdbc driver jar file.

Please note that the dot after -d
refers to the current directory.

Please note that javac is not
included in the installed JVM (Java Virtual Machine). Java
compiler (javac) is included in Java SDK, which can be
downloaded from Sun Microsystem’s website.

3). Once compiled, you should test the
class:

  java -classpath %CP%
voicent.sample.MyDbPasswordChecker 101 123

4). Making a Jar file:

  jar -cvf dbinteg.jar
voicent

You can use the sample application as is. To deploy the application, select Deploy > Submit to Gateway… from the program main menu. If the menu item is grayed out, select Validate… first.

If you have multiple IVR application deployed, please see IVR select application for more information.

Once deployed, restart the gateway to have the changes to take effect.

When an incoming call is answered by the gateway, the caller is asked to enter account number and password, once the password is verified with the database, a secret information is played back to the caller.