This article tries to explain how to use the built-in database action in Voicent IVR Studio through a simple sample app. The following figure shows the call flow.
For an incoming call, the app first checks a database table and find all entries. If the total number of entries is greater than 0, then it reads back each entry one-by-one. If there is no entry, it informs the caller.
In this sample, we use mysql database engine. First we create a database named test, then we create a table named test. It does not matter what how you define the table as long as you have a column called name. A sample table could look like the following:
Setup JDBC Driver
Select the root element “Incoming Call”, right click on properties…, then click the Setup JDBC… button.
Make sure you choose the correct JDBC connection string and other info for your application.
From the same root element’s property window, select the Variable tab. Add the following 4 variables as follows:
|rc||jsoninit()||The variable to hold the database query result|
|entry||jsoninit()||The variable to hold one row of the query|
|total||0||The total number of entries returned|
|index||0||Loop variable to go through the query result|
The result of a database query is saved in a JSON array. In this case, variable rc is used to hold the value. To initialize a JSON variable, use jsoninit().
The Database Action
In this sample, we use a simple select all statement from the test table.
The result is then saved in the variable rc by using a setvar action, rc = getentry, where getentry is the name of the database action.
Check Total Rows Returned
We use the built-in function jsonsize to check the total rows returned. To make it easier to later actions, we assign the result to a variable total, where total = jsonsize(rc).
Get One Specific Row
To get a specific row, use the array format. For this example, we use the setvar action to set entry = rc[index]. You can then access the name column by entry.name notation.
The Sample IVR App
The sample app can be download here: IVR Database Action Sample.