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

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

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

create ozmlin table
Figure 4 - Create ozmlin table

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.

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:

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

editing rows
Figure 13 - Editing rows

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.

adding a new row
Figure 14 - Adding a new 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.

adding dial plan
Figure 15 - 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 16 - Dial plan setup

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:

correct extension
Figure 17 - Correct extensions

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:

reminder call setup
Figure 18 - Reminder call setup

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:

completed reminder
Figure 19 - Completed reminder

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