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.

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.

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:

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 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
);

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.

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.

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

Then click on Install.

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

Select OleDb as Data Source and give the following for 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.

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.

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.

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:

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.

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

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

• IVR Examples: IVR Examples
• OzML reference book: OzML Commands
• How to make IVR with SQL OzML: IVR with SQL OzML
• How to make IVR with HTTP OzML: IVR with HTTP OzML

More information

• How to make IVR with IVR Extension in Ozeki Phone System PBX
• How to make IVR with SQL OzML in Ozeki Phone System VoIP PBX
• How to make IVR with HTTP OzML in Ozeki Phone System VoIP PBX