How to make an Appointment Reminder
with SQL OzML

The following guide will show you, how to create an appointment reminder application, with the help of SQL database and OzML scripts. The application will start reminder calls for the number you have set up previously. Of course you can customize the text of the reminders to your need.

Video tutorial: How to make an Appointment Reminder with 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.

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 what we need to type in and how to execute it.

Create the ozmlout and ozmlscripts table 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.

For the application to work properly, we will need another column in the ozmlout table. For that right click on ozmlout table and select Design. Figure 5 shows what to do next.

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 an OzML script to ozmlscripts table

For this we need to right click on ozmlscripts table and select edit top 200 rows as Figure 13 shows:

We can add a new row as you seen before, by simply clicking in the column and typing in the datas.
Figure 14 show the added row.

You can see that we need to add the scriptid, and the OzML script in the second column. For this example we used a simple voice reminder. You can see it's code below.

<Response> 
			<Speak>
				Hello, you have a new appointment in London Hotel at 2 PM!
			</Speak> 
</Response>

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. You can control your calls from different types of databases with this extension. If you would like expand your knowledge about the SQL OzML Extension please visit this page. So to setup an outbound routing rule open the Ozeki Phone System 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. This was only an example to setup routing rules for SQL OzML extension. You can find more informations about the routing rules if you visit our How to setup Call routing page.

If you would like to control route the way of the outside calls you need to install an API Extension. API Extension is a standard extension that provides a phone number for your client application connecting through Ozeki Phone System. Using the API, you can build call assistants, call center clients, IVRs or any custom Voice or Messaging application. If you click on this page you can find the full description of the API Extension and the installation of it.

Now let's move on the next step.

Step 5: Setup a Softphone

We will need a softphone to test the appointment reminder 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 did everything according to what is written, then after setting up the softphone, you will have to see the following in the Extensions tab of Ozeki Phone Systems XE:

Step 6: Testing the application

To test the appointment reminder first we have to insert a new row to the ozmlout table with the method you already know. So right click on ozmlout table and Edit Top 200 Rows, then just type in the followings:

As you can see we had to add the dialed number, call for status, the scriptid and the ScheduleTime for the reminder. We can leave the other columns empty, becausethey will be filled automatically when the reminder is executed.

After the reminder is executed and the call is answered, the following changes happen to the row we have added in ozmlout table:

You can see that, the status of the call changed to completed, because the call was answered, the duration became 4 and the starttime was given, that is the date when the phone was picked up.

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.

Due to SQL API it is easy to manage Ozeki Phone System through SQL statements by using your database. On the How to set up database connections page you can find all the supported databases and you can select your preferred one. You can read about how to log calls and any other PBX events, how to send/receive SMS messages and authenticate PBX users using your database.

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 HTTP OzML