How to work with Database

To easily work with almost all databases you will need the Apstel Integration Server (IS) – free of charge and easy to install application server that extends Asterisk dial plan functionality. It is specially designed to simplify working with third party servers (database servers like MS SQL, MySQL, Postgres, Sybase, email servers etc.).

In this tutorial we will put emphasis on the Integration Server (IS) database module and Visual Dialplan DbQuery component that works with IS database module.

IS database module is designed to simplify SQL query execution from within the dial plan against any database that provides JDBC drivers ( MS SQL, MySQL, Postgres, Sybase, HSQL, etc.). The module will handle connection to the database server, execute the SQL query and manage the result set.

In the following five steps you will be explained how to do this:

1. Instalation process and firewall rules
2. Creating connection to the database server
3. Integration Server SQL query editor
4. How to use DbQuery component
5. Create database drive dial plan

1. Instalation process and firewall rules

Integration server can be downloaded and installed in two different ways.
First way is to download Visual Dialplan (Visual Dialplan can be downloaded from our web site, www.apstel.com/download ) and during the installation process when asked ‘select the embedded components that should be installed’ check the Embedded Integration Server box and press next. Embedded Integration Server would be installed on the same machine with Visual Dialplan. This is good option for training and development purposes.

The second way is to download and install Integration Server separately from Visual Dialplan, at separate machine, probably the same machine where you installed Asterisk server (Integration Server can be downloaded from http://www.apstel.com/download/is/ ). This is good option for production purposes.

Integration Server installation is easy. Just double click on the installation file (or run the installation file in text mode) and follow on-screen instructions.

The Integration Server use the following ports so please make sure these ports are accessible (open) at the machine where you installed IS:
9110 – default port for direct access via Visual Dialplan
9111 – default port for IS HTTP interface
9112 – default port for embedded database server
9113 – default port for embedded email server

2. Creating connection to the database server

The connection to the database server is done through the Integration Server, so you should first connect to the Integration Server.

To connect to Integration Server open the Integration Server view within Visual Dialplan (icon located at the lower left side of the Visual Dialplan main window – see the picture below).

 

You can use already created connection to the Integration Server you installed during the Visual Dialplan installation (if you selected option to install Integration Server while installing Visual Dialplan) or you can create new connection by right clicking on the area on the left side and selecting “New Integration Server …” option from the drop down menu. Fill in the Name, Host, Username and Password information and test connection to the Integration Server.

Default Username is admin, and default password is also admin.

Now you are ready to create a connection to the database server.
Right click on the selected Integration Server node and select “Create” and then “New Database …” option from the drop down menu.

Database properties window looks like this:

Fill in the information about your remote database (the window above shows connection to the MS SQL server) and click on the “Test connection” button to make sure the connection works correctly.

Once you connect to the database you can select “Structure” node to load database structure or “Queries” node (SQL query editor) to test various SQL queries.

3. Integration Server SQL query editor

This is the place to type and test SQL queries before using them in the dial plan.
Simply type in the SQL query, click on the Execute query button (or press ctrl+Enter) and the query will be executed.

You can create as many SQL queries as you wish.
Just right click on the “Queries” node and select “New Query” from the drop down menu to create new query.

You can also use parameters (dial plan variables) within SQL query.
At the design time (while you work in SQL query editor) you can set values for these parameters.
For example if you want to assign number 11 to parameter id you should have the following line in your SQL query:
@@${id}=11

At run time (when call arrives at Asterisk) this value will be replaced with the real id variable value from the Asterisk dial plan. This means you should define this variable in your dial plan.

Here is an example.

— This is the comment.
— The following line is parameter definition used only at the design time.
@@${id}=11
— When you execute the query in the SQL query editor
— the parameter ${id} will be replaced with number 11.
— At runtime ${id} will be replaced with Asterisk variable ${id} value.
select * from Products where productid=${id}

Now let’s see how to use this in the dial plan.

4. How to use DbQuery component

This is powerful component that enables access to the database from the dial plan.
The component is located on the Integration Server sheet.
DbQuery component connects to the Integration Server that further connects to the database server, executes SQL query, returns result set back to the dial plan and disconnects from the database server.

Depending on the selection in the DbQuery component the result set will be:
1. stored in the variables (useful in case the result set is one row),
2. processed with the subroutine/macro (in case more than one row is returned from the database) or
3. result set will not be processed (in case of the update or insert SQL statement).

In order to work with DbQuery component you should have running Integration Server and configured connection to the database as described above.

OK, enough theory.
You are now ready to create your fist database drive dial plan.

5. Create database drive dial plan

5.1. Single record result set example

In this example we will show you how to execute SQL query from the dial plan and store the result set in the variable. We will use “is-db-single-select.vdp” dial plan provided with the Visual Dialplan (select “Open sample dial plan” at Quick start window when starting Visual Dialplan).

This dial plan works with table ORDERS. This table was automatically created when you have installed the Integration Server so there is no need for you to create the table.

Here are the steps we took to create this dial plan (you can do it on your own following the steps below or just load already created dial plan, “is-db-single-select.vdp”):

1. At IS view create SQL query “GetTotalAmount” for table ORDERS :
SELECT sum(amount) FROM ORDERS

2. Create the dial plan context that will execute the query, store the result set and play back the result set:
a. The first building block starts the dial plan and defines the DID (extension) that will be handled by this dial plan
b. Second building block executes the query and stores the query result in the variable
c. Third building block says the value of the result set (in our example it is “45”)
d. Fourth building block hangs up the call

3. Edit DbQuery component:
a. Select Embedded IS,
b. Select embedded database,
c. Select GetTotalAmount query you created earlier,
d. check field “Store results in variables” and
e. select variable “total” from the drop down list of available variables (variable “total” should be first created at the Channel Variables view)
After the building block execution the SQL query result will be stored in the variable “total”.
4. Edit Say block:

And that’s it. It is that simple.

We used just one building block to connect to database, execute SQL query and store result sate in the variable.

This dial plan will execute query “GetTotalAmount” and put the result in variable “total”, then Say block will play the value of the variable (45 in this case).

5.2. Multi-record result set example

In this sample we will show you how to execute SQL query from the dial plan and how to process result set when it contains several rows. In such case the macro/subroutine will be automatically executed several times, as many times as there are returned rows in the result set.

We will use “is-db-multi-select.vdp” example dial plan provided with the Visual Dialplan (select “Open sample dial plan” at Quick start window when starting Visual Dialplan).

Here are the steps we took to create this dial plan (you can do it on your own following the steps below or just load already created dial plan, “is-db-multi-select.vdp”):

1. In IS view create query “GetOrderst” for table ORDERS :
SELECT FROM ORDERS WHERE user_pin = ${pin}

2. Create the dial plan context that executes the query, process query result with macro/subroutine and plays the result set:
a. The first building block starts the dial plan and defines the DID (extension) that will be handled by this dial plan
b. Second building block stores DTMF digits into variable “pin” (you should first create this variable at Channel Variables view)
c. Third building block creates Channel Variable “total” with zero value (you should first create this variable at Channel Variables view)
d. Fourth building block executes the query and process result set with “list_orders” macro. To complete this step you should first create “list_orders” macro in Macro/Subroutine view (see steps under #3).
e. Fifth building block says the value of the result set (30)
f. Sixth (latest) building block hangs up the call

3. Create macro “list_orders” context that writes log and adds values from query “GetOrders” to variable “total”:
a. The first building block starts the macro
b. Second building block writes the following log on the Asterisk console:
Order: ${arg-order}, with amount:${arg-amount},
where ${arg-order} and ${arg-amount} represents respective values from ORDER_ID and AMOUNT column from the result set.
c. Third building block summarize values from query “GetOrders” to variable “total”
d. Fourt building block exits “list_orders” macro.

5. Edit DbQuery component:
a. Select Embedded IS,
b. Select embedded database,
c. Select GetOrders query you created earlier,
d. check field “Process results with Macro/subroutine” and
e. select macro “list_orders” from the drop down list of available macros .

6. Edit Say block:

This dial plan will execute query “GetOrders” and process the results with macro “list_orders”.
In macro “list_orders” variable “total” will summarize values from query “GetOrders”, then Say block will play the value of the variable (30 in this case).