How to make an OzML Autodialer
with SQL OzML

This guide below describes how to create a great Autodialer using an SQL OzML script. For making a new IVR application you need to insert a new record into your ozmlscript table. If you have enough phone numbers of the clients in your database table, it is time to call them with the help of an Autodialer.

SQL OzML makes it possible to create a new Autodialer and manage the calls. Of course you have the possibility that the SQL OzML reads different messages to each client. Furthermore it is not a problem, either, if the client does not answer. The Autodialer will call the next one.

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 tables are now, the application would start the calls as soon as the SQL OzML API checks the database for numbers. So if we want, we can insert a new column into ozmlout table which will let us set up a timer for our calls. Figure 5 shows how to do it.

adding scheduletime as new column
Figure 5 - Adding ScheduleTime as new column

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

saving your database
Figure 6 - 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 XE and log in.

login page
Figure 7 - Login Page

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

main page
Figure 8 - Main Page

Then click on Install.

sql api page
Figure 9 - SQL API page

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

sql api types
Figure 10 - SQL API Types

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

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

Select the Outgoing Calls and complete the "Query template for outgoing call requests:" field with the following:

schedule time settings
Figure 12 - ScheduleTime settings

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 some new rows into ozmlscripts table. These rows include the OzML scripts, which will be executed when the called phones are picked up. You can see an example for inserting new rows below.

row inserting scripts
Figure 13 - Row inserting scripts

This is an example of a simple read out text, without any interactive functions. If you are intrested in more complicated OzmL scripts, here is an example.

<Response> 
    <UserInput Timeout="5" Repeat="true"> 
        <InitialCommands> 
			<Play>welcome.wav</Play>
            <Speak>
                Your customer account number is 454565. Press 1 to send it as an 
                SMS to your mobile, press 2 if you have recorded it and you 
                don't wish to receive it as an SMS
            </Speak>
        </InitialCommands> 
        <Inputs> 
            <Input Key="1"> 
				<SendSms Recipient="+062554670">
                    Your customer account number is 454565.
                </SendSms>
				<Speak>We sent you the SMS. Good bye!</Speak>
            </Input> 
            <Input Key="2"> 
                <Speak>Good bye!</Speak>
            </Input> 
        </Inputs> 
    </UserInput> 
</Response>

Code example 2 - Interactive OzML Script

You can see that the OzML script can play .wav files, read out the text you give between <Speak> and </Speak> , it takes in the pressed buttons and can send SMS too.

Step 4: Setup outbound routing rule

We need to add a Routing rule, because the calls have to be transmitted from the SQL OzML Extension. So to set up an outbound routing rule open the Ozeki Phone System XE and log in. Then add a new Dial plan.

adding dial plan
Figure 14 - Adding Dial plan

The next step is to select the Outbound/internal call from Rule Scope. Now we have to select the SQLOzML extension from the Specific extensions. You will notice that the number next to the extension changes after selecting the SQLOzML extension to the number of the extension we have created before.

dial plan setup
Figure 15 - Dial plan setup

With this we are finished with setting up the Dial plan. Let's move on the next step.

Step 5: Setup the Softphones

We will need two softphones to test the autodialer application. You can use any softphones you want, the only thing is that we have to set them up to SIP 500 and 501 for this example.

If you did everything according to what is written, then after setting up the softphones, you will have to see the following in the Extensions tab of Ozeki Phone Systems XE:

correct extensions
Figure 16 - Correct extensions

Step 6: Inserting the phone numbers

To test your autodialer application, you have to insert some phone numbers to the ozmlout table. You can do so by executing the following SQL script on the ozekipbx database by clickin on it with the right mouse button and selecting New Query.

adding phone numbers
Figure 17 - Adding phone numbers

After executing the script above, two rows will be added to the ozmlout table, which you can see by right clicking on the ozmlout table and selecting Refresh. Then right click again on the table and select Edit Top 200 Rows. You will see the following:

phone number in the ozmlout table
Figure 18 - Phone numbers in the ozmlout table

This way the numbers are ready for the calls and the SQL OzML API will execute the calls when the ScheduleTime comes.

After the calls were executed and answered the ozmlout table will change the state of the calls from "call" to "Completed" and the duration and the starttime will be given.

successful calls
Figure 19 - Successful calls

You are ready with the initial steps.

The SQL Extension will poll your database with a configured time interval. When the script on Figure 12 is true for a record, then the Extension creates a call automatically with the call parameters from the mentioned record.

When the called user answers to the call, then the OzML response (what is joined by ScriptID) from the ozmlscripts table will be executed.

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