How to setup SQL OzML
By using SQL OzML Extension you can insert a list of telephone numbers and an OzML script into your database to make calls automatically. To process incoming call, the PBX will execute an SQL select to find the apprpriate OzML script, it will serve the call and store the results. In this chapter you can learn about how to make and accept calls by using Ozeki Phone System SQL OzML Extension.
Step 1: How to install an SQL OzML extension in Ozeki Phone System
First of all, open Ozeki Phone System in a web browser, and login to the system with your username and password (Figure 1).
Figure 1 - Login page
On the main page of Ozeki Phone System at the right side of the screen you find your installed extensions. Click on the 'Add' button to add a new extension to your system (Figure 2).
Figure 2 - Main page
You find yourself on the extension installation page where you can install different type of Standard Extensions or PBX Services. You can find the SQL OzML extension in the PBX Services column section and you can install it with the 'Install' button (Figure 3).
Figure 3 - Install new extension
But this is not the only way you can reach the SQL OzML Extension installation section. If you are on the main page, choose the 'Productivity' option and than the 'SQL API'. After that, click on the 'Install' button on the SQL API page and install the SQL OzML Extension. It's your decision which one to choose, the result will be the same.
Step 2: How to configure the SQL OzML extension in Ozeki Phone System
If you are on the install page of the SQL OzML Extension you can notice some information on the left side of the screen and there is three tabs on the middle of the page. These are the Database connection, the Outgoing calls and the Incoming calls. The first one is for manage your database connection and give parameters for it, the Outgoing calls and the Incoming calls are for make setups for outbound and inbound call's queries and for execute them (Figure 4).
Figure 4 - SQL OzML Installation screen 1
First you need to enter the Connection name which will be the phone number of the extension.
Next choose the Data source. If you select the MSSQL or the MySQL you don't need to install
any other plugin or connector to your system, the Ozeki Phone System can handle these
databases easily. But if you choose Odbc or OleDb you need to install connector for them
to access database management systems.
At the Configuration mode section you can choose between Simple or Advanced configuration mode. The simple configuration mode can only be used when you connect Ozeki Phone System to MSSQL or MySQL data sources. This mode is intended to make the configuration easier for these connection types. If you select this option you need to add four informations:
- the Server is the IP address or URL of the database server.
- the Database name is the name of the databse to use by the system.
- the Username is for access the selected database.
- the Password.
Figure 5 - SQL OzML Installation screen 2
But if you would like to add more informations and parameters to connect to the database,
choose the advanced configuration mode and you can change the Connection string which is a description
of a database connection regarding the selected data source type. For more about connection strings,
please visit our page of the Connection strings.
If you selected Odbc or OleDb data sources you only can reach the advanced configuration mode.
Figure 6 - SQL OzML Installation screen 3
Now click on the Outgoing calls label to configure the outbound calls.
First you can add the Polling parameters like the time (in seconds) of the ozmlout
table checking frequency and the number of maximum simultaneous calls.
Under this section you can specify the SQL templates to be used for outgoing calls. The system has to do three things:
- start selecting from the ozmlout table and this query has to return the ID of the call request, number to dial, the status of the call request and the script ID to execute.
- select the specified OzML script where scrip ID equals with the ID of the call request.
- update the table with the calls status (Figure 7).
Figure 7 - SQL OzML Outgoing calls
The Incoming calls setup section is very similar to the outgoing calls part. You have to select the script ID and the OzML script from the incoming gall table of your database and you can add some conditions to the select. With the other SQL query the record of the current call will be updated identified by an ID. Parameters starts with $ sign will be replace with the actual parameters when the query will be executed. You can find the detailed description if you move the cursor on the question mark. Finally you need to update the table after the call has finished (Figure 8).
Figure 8 - SQL OzML Incoming Calls
Step 3: Create database tables by using MySQL
After you have configured the SQL OzML extension you need to creat the database tables
which the SQL OzML will use to Select from it, Insert into it and Update it.
Now I will show you how to creat database tables which the SQL OzML Extension can use for operation. I need you to open a database design tool to creat and manage databases. In my example I will use MySQL Workbench. First we will create the ozmlout table and we will store the outbound call data in it, like:
- the int type ID of the call
- the varchar type DialedNumber
- the Status of the call (varchar type)
- the int type Duration and ScriptId
- the RecordUrl (varchar type)
- the StartTime of the call (datetime)
- and you can add ScheduledTime column if you would like to start the call in an adjusted time
Execute this and refresh the table and you will see the new table in the Schemas section on the left side of the screen (Figure 9).
Figure 9 - Create ozmlout table
You can reach the code here:
USE test; CREATE TABLE `ozmlout` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `DialedNumber` varchar(40) NOT NULL, `Status` varchar(40) DEFAULT NULL, `Duration` int(10) DEFAULT NULL, `ScriptId` int(10) DEFAULT NULL, `RecordUrl` varchar(150) DEFAULT NULL, `StartTime` datetime DEFAULT NULL, `ScheduledTime` datetime DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
Now you created an empty table structure which will need to be filled later to initiate calls. But we need an other table which contains the OzML scripts. These will control the call automation of incoming or outgoing calls using OzML scripts. So make another table called ozmlscripts with the Create command just like you did before.This table will contain the scriptid (int type) and the ozml (varchar type) columns but you can add many other optional columns if you want to customize your system (Figure 10).
Figure 10 - Create ozmlscripts table
You can reach the ozmlscripts create code here:
USE test; CREATE TABLE `ozmlscripts` ( `scriptid` int(10) NOT NULL AUTO_INCREMENT, `ozml` varchar(10000) NOT NULL, PRIMARY KEY (`scriptid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
If you finished the cration of the ozmlscripts table, than it need to be filled.
This is one of the most important parts of the SQL OzML because in this section we
can setup that which operation execute when a user calls the SQL OzML Extension. With
the help of this feature you can easily customize the behaviour of the calls depending on the identity of the user.
Edit the ozml column content to an arbitary OzML script you would like to execute when somebody called the SQL OzML Extension. In my example it will be a simple OzML script sample (Figure 11):
<OzML> <Speak>Hello and welcome to Ozeki Ltd.</Speak> </OzML>
So when we call the phone number of the SQL OzML Extension or it calls us we will hear voice saying: „Hello and welcome to Ozeki Ltd”. You can read more information and references about the OzML languages if you visit our OzML reference book page.
Figure 11 - Insert OzML script
If you finished the ozml column insertion the only thing left is creating the ozmlin table which contains the incoming call values. Use the Create operation the usual way and make the columns:
- the int type ID of the call
- the varchar type CallerId
- int type ScriptId and Duration
- the varchar type Status of the call
- the varchar type RecordURL if you would like to record the converstation
- the StartTime of the call (datetime)
If you typed in the Create command of this table click on the execute button and refresh the table. If you made it right the ozmlin table will be there in the Schemas section (Figure 12).
Figure 12 - Create ozmlin table
The ozmlin code is here:
USE test; CREATE TABLE `ozmlin` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `CallerId` varchar(40) NOT NULL, `ScriptId` int(10) NOT NULL, `Duration` int(10) DEFAULT '0', `Status` varchar(40) DEFAULT NULL, `RecordURL` varchar(150) DEFAULT NULL, `StartTime` datetime DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
Now we finished the configuration of our database. If you call the phone number of the SQL OzML extension than you will hear what you have set up in your ozml column of the ozmlscripts table. Refresh the table and the set up informations of the call will be there in the ozmlin table (Figure 13).
Figure 13 - The updated ozmlin table with call informations
Important to mention this is just a simple example of using this feature of the Ozeki Phone System SQL
OzML Extension and these are the minimum setups for the operation of it. You can customize the database and the
OzML code to your own needs. For example if you have an incoming call from an extension you can specify ozml scripts
individually to different extensions, thus specify different behavior to the extension. But if you have a call from the
outside line, the OzML script can give informations about your corporation to this extension.
Because this is an extension so you can add a dial plan for this extension if you would like to configure a call routing. For example if you have an incoming call from a specified extension you can configure a routing rule and every call from this extension will be connect to the SQL OzML Extension. If you would like to know more about the call routings please click on this page.
By using SQL OzML Extension you can easily insert a list of telephone numbers and OZML scripts into your database to make calls automatically. The SQL OzML is an extension in Ozeki Phone System, the number of the extension is the phone number of the SQL OzML.
If you have any questions or need assistance, please contact us at email@example.com
People who read this also read...
- How to setup Database authenticator
- How to setup Call reporter
- Message reporter
- How to setup SQL SMS
- How to setup Database logger
- How to install MySQL ODBC
- Connection strings