Home
RECOMMENDED FOR YOU:
SMS Gateway software
Ozeki brings you outstanding
SMS Gateway technology. Use our SMS Server products on Windows,Linux, or Android

C# SMS API
Developers can use our C# SMS API to send SMS from C#.Net. The C# SMS API comes with full source code

PHP SMS API
The ozeki PHP SMS gateway software can be used to send SMS from PHP and to receive SMS usig PHP on your website

SMPP SMS Gateway
SMS service providers use our SMPP gateway solution, that offers a high performance SMPP server and SMPP client gateway with amazing routing capabilities
Ozeki Phone System 10

How to make IVR with SQL OzML

In this guide you can see how to make an Interactive Voice Response (IVR) with your database server using an OzML script. For making a new IVR application you need to insert a new record into your ozmlscript table. The following step-by-step guide demonstrates how to build an IVR system with OzML SQL API.

SQL OzML makes it possible to create a new IVR from your other IT system. Ozeki Phone System receives a call from any extension or outside line and select an OzML script from the ozmlscripts database table. Then the Ozeki Phone System will load the IVR menu to the caller.

sql ozml
Figure 1 - SQL OzML

Step 1: Configure your Microsoft SQL Express

Run the SQL Server Management Studio and connect to the server „Your pc’s name”\SAMPLE. In my case it is USER-PC\SAMPLE. Next comes creating the database we are going to use.

creating a new database
Figure 2 - Creating a new Database

Name your Database to ozekipbx with default Owner and select OK. With this the Database is created and we can create the corresponding Login user. For this right click on Security and select New and Login. The Login account settings are shown on Figure 3:

login setup
Figure 3 - Login Setup

After creating the User login, that we are going to use, we need to set it up as the owner of ozekipbx database. To do so we need to righ click on ozekipbx and select Properties, then select Files and click on the "..." next to the Ownerfield. Then click on "Browse..." and then mark the user ozeki checked.

Now we have the Database and the corresponding User, but still lack the tables to work with, so let’s see, how we create them. First roll down the Databases in SQL Server Management Studio. Right click on ozekipbx and select New Query. Figure 4 shows how to execute the table creating scripts.

create ozmlin table
Figure 4 - Create ozmlin table

Create the ozmlin, ozmlout and ozmlscripts tables by following the previous example with the help of the following scripts:

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  id int identity(1,1) PRIMARY KEY,
  callerid varchar(40) NOT NULL,
  scriptid int NOT NULL,
  duration int DEFAULT NULL,
  status varchar(40) DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime datetime DEFAULT NULL
);

-- ----------------------------------------------------- 
-- Table `ozekipbx`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE ozmlout
(
  id int identity(1,1) PRIMARY KEY,
  dialednumber varchar(40) NOT NULL,
  status varchar(40) DEFAULT NULL,
  duration int DEFAULT NULL,
  scriptid int DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime datetime DEFAULT NULL
);

-- -----------------------------------------------------
-- Table `ozekipbx`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE ozmlscripts
(
  scriptid int identity(1,1) PRIMARY KEY,
  ozml varchar(8000) NOT NULL
);
Code example 1 - SQL table creation Scripts

With this we have created the neccessary tables, to see them in SQL Server Management Studio roll down the ozekipbx database, right click on Tables and Refresh. Now roll down the Tables and you will see the ozmlin, ozmlout, ozmlscripts tables we have created.

As the final part of step 1 we have to save the work we done till now.

saving your database
Figure 5 - Saving your Database

Step 2: SQL OzML API installation and configuration

To install the required SQL OzML API first we have to open the Ozeki Phone System and log in.

login page
Figure 6 - Login Page

Then on the top left side select Productivity and click on SQL API.

main page
Figure 7 - Main Page

Then click on Install.

sql api page
Figure 8 - SQL API page

In the next window click on Install next to the SQL OzML API.

sql api types
Figure 9 - SQL API Types

Select OleDb as Data Source and give the following for Connection string:

sql ozml api connection string
Figure 10 - SQL OzML API Connection string

Press OK and we are finished with setting up the SQL OzML API. It will show you that it Connected succesfully to the database.

Step 3: Add OzML scripts to ozmlscripts table

We have to insert a new row into ozmlscripts table. This row includes the OzML script, that will be executed during the call. You can see the example for inserting a row below.

row inserting scripts
Figure 11 - Row inserting scripts

Step 4: Modify your SQL queries

You are able to select which OzML script should be executed, when you call the SQL OzML API. You can do so by giving a WHERE condition in the field of "Query OzML on incoming calls:", in the Incoming calls tab of Configure tab of the SQL OzML API.

ozml script selection
Figure 12 - OzML script selection

With this condition the script in the first row of the ozmlscripts table will be executed.

Step 5: Add a new inbound routing rule

In order to play the IVR for the caller, you need to setup a new routing rule that forwards the incoming call to the SQL OzML API. The following figure shows where an incoming call arrives from any Outside line, the call will be forwarded to an SQL OzML Extension.

inbound routing rule
Figure 13 - Inbound routing rule

Step 6: Setup a Softphone

We will need a softphone to test the Interactive Voice Response application. You can use any softphone you want, the only thing is that we have to set it up to SIP 501 for this example.

If you configured the Softphone successfully, you should see the following in the Extensions tab of Ozeki Phone Systems:

connected softphone
Figure 14 - Connected Softphone

Step 7: Testin the IVR application

The IVR application is finished, so its time to test it. For this you only have to open the Softphone you previously set up, and call the SIP 9998, that is the SQL OzML Extension.

If you did everything correctly till know, you should hear the IVR message and the following row should appear in the ozmlin table.

successful call in ozmlin table
Figure 15 - Successful call in ozmlin table

With this the application is truly finished and ready to answer calls.

Dig deeper!
People who read this also read...

More information