Advanced IVR Tutorial Lesson 2: Get More Information From Database

The result of a Java action is saved in the return variable of the action. The system basically expects a list of name value pairs defined in a object of class java.util.Properties. If the return is of a different type, the system tries to create one using the toString method of the return object and assigns it to the name result.

We can easily update the application so the information is retrieved from the AccountPassword table.

Update the Database Table

Add a new column named Account_Info, then populate some initial data.

Account_Id	Account_Password	Account_Info
101	123	The secret formula of Coke is sugar
102	3456	The secret formula of Coke is water

Update the Java Class

The updated class is listed here. The updated lines are shown in red.

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

        boolean passwdOK = false;
        String info = "";

        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, Account_Info 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;
                    info = rs.getString(2).trim();
                }
            }

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

        Properties props = new Properties();
        props.setProperty("result", passwdOK ? "true" : "false");
        props.setProperty("info", info);

        return props;
    }

Create a new variable to hold the information

To hold the information returned from the database we need to add a variable secret_info. This is to be added to the top element. In the Check Password element, add another action to save the return value (use Set Variable action).

secret_info = check_password.info

Advanced IVR Tutorial Lesson 2: Get More Information From Database

Update the Database Table

Update the Java Class

Create a new variable to hold the information

Use the variable as a prompt