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.

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

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.

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 XE 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:

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

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.

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.

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.

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:

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.

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:

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.

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...

• Appointment Reminder Examples: IVR Examples
• OzML reference book: OzML Commands
• How to make an Appointment Reminder with OzML
• How to make an IVR system with OzML