OZEKI OZEKI Phone System
Customizable PBX for Windows

Ozeki Phone System

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

login page of ozeki phone system — 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).

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

how to install extenstions in ozeki phone system — 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).

installation of oz ml extension — 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.

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.

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

sql ozml outgoing calls — 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).

sql ozml incoming calls — 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).

creating the ozmlout table — 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).

creating the ozmlscripts table — 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.

insert ozml script — 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).

creating the ozmlin table — 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.

Conclusion

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 info@ozekiphone.com

// A többi oldalhoz hasonlóan majd itt lesznek a create table scriptek. /*

Step 4: Create table scripts

Microsoft SQL Server

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  id int identity(1,1) PRIMARY KEY,
  callerid varchar(40) NOT NULL,
  scriptid int(10) NOT NULL,
  duration int(10) 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(10) DEFAULT NULL,
  scriptid int(10) 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(10000) NOT NULL
);

Microsoft SQL Express

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  id int identity(1,1) PRIMARY KEY,
  callerid varchar(40) NOT NULL,
  scriptid int(10) NOT NULL,
  duration int(10) 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(10) DEFAULT NULL,
  scriptid int(10) 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(10000) NOT NULL
);

Oracle

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  id integer PRIMARY KEY,
  callerid varchar(40) NOT NULL,
  scriptid integer NOT NULL,
  duration integer DEFAULT NULL,
  status varchar(40) DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime timestamp DEFAULT NULL
);

CREATE SEQUENCE id_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE ozmlin_insert
BEFORE INSERT ON ozmlin
FOR EACH ROW
BEGIN
    SELECT id_seq.nextval INTO :new.id FROM dual;
END;
/

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE ozmlout
(
  id integer PRIMARY KEY,
  dialednumber varchar(40) NOT NULL,
  status varchar(40) DEFAULT NULL,
  duration integer DEFAULT NULL,
  scriptid integer DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime timestamp DEFAULT NULL
);

CREATE SEQUENCE id_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE ozmlout_insert
BEFORE INSERT ON ozmlout
FOR EACH ROW
BEGIN
    SELECT id_seq.nextval INTO :new.id FROM dual;
END;
/

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE ozmlscripts
(
  scriptid integer PRIMARY KEY,
  ozml varchar(10000) NOT NULL
);

CREATE SEQUENCE scriptid_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE ozmlscripts_insert
BEFORE INSERT ON ozmlscripts
FOR EACH ROW
BEGIN
    SELECT scriptid_seq.nextval INTO :new.scriptid FROM dual;
END;
/

MySQL

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
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 AUTO_INCREMENT=0
DEFAULT CHARSET=utf8;

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
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,
  PRIMARY KEY (`id`)) 
ENGINE=InnoDB AUTO_INCREMENT=0
DEFAULT CHARSET=utf8;

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE `ozmlscripts` (
  `scriptid` int(10) NOT NULL AUTO_INCREMENT,
  `ozml` varchar(10000) NOT NULL,
  PRIMARY KEY (`scriptid`))
ENGINE=InnoDB AUTO_INCREMENT=0
DEFAULT CHARSET=utf8;

PostgreSQL

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  id serial PRIMARY KEY,
  callerid varchar(40) NOT NULL,
  scriptid integer NOT NULL,
  duration integer DEFAULT NULL,
  status varchar(40) DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime date DEFAULT NULL
)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE ozmlout
(
  id serial PRIMARY KEY,
  dialednumber varchar(40) NOT NULL,
  status varchar(40) DEFAULT NULL,
  duration integer DEFAULT NULL,
  scriptid integer DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime date DEFAULT NULL
)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE ozmlscripts
(
  scriptid serial PRIMARY KEY,
  ozml varchar(10000) NOT NULL
)

Sybase (SQL Anywhere)

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  "id" integer NOT NULL autoincrement,
  "callerid" varchar(40) NOT NULL,
  "scriptid" integer NOT NULL,
  "duration" integer DEFAULT NULL,
  "status" varchar(40) DEFAULT NULL,
  "recordurl" varchar(150) DEFAULT NULL,
  "starttime" datetime DEFAULT NULL
  PRIMARY KEY ("id")
)

go 
commit work 
go

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE ozmlout
(
  "id" integer NOT NULL autoincrement,
  "dialednumber" varchar(40) NOT NULL,
  "status" varchar(40) DEFAULT NULL,
  "duration" integer DEFAULT NULL,
  "scriptid" integer DEFAULT NULL,
  "recordurl" varchar(150) DEFAULT NULL,
  "starttime" datetime DEFAULT NULL
  PRIMARY KEY ("id")
)

go 
commit work 
go

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE ozmlscripts
(
  "scriptid" integer NOT NULL autoincrement,
  "ozml" varchar(10000) NOT NULL
  PRIMARY KEY ("scriptid")
)

go 
commit work 
go

DB2

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
 (id integer NOT NULL GENERATED ALWAYS AS 
     IDENTITY (START WITH 1 INCREMENT BY 1),
  callerid varchar(40) NOT NULL,
  scriptid integer NOT NULL,
  duration integer DEFAULT NULL,
  status varchar(40) DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime timestamp DEFAULT NULL)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE ozmlout
(
 (id integer NOT NULL GENERATED ALWAYS AS 
     IDENTITY (START WITH 1 INCREMENT BY 1),
  dialednumber varchar(40) NOT NULL,
  status varchar(40) DEFAULT NULL,
  duration integer DEFAULT NULL,
  scriptid integer DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime timestamp DEFAULT NULL)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE ozmlscripts
(
 (scriptid integer NOT NULL GENERATED ALWAYS AS 
     IDENTITY (START WITH 1 INCREMENT BY 1),
  ozml varchar(10000) NOT NULL)

Informix

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  id serial 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 serial 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 serial PRIMARY KEY,
  ozml varchar(10000) NOT NULL
)

FoxPro

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  id I AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY, 
  callerId C(40) NOT NULL,
  scriptId I NOT NULL,
  duration I DEFAULT NULL,
  status C(40) DEFAULT NULL,
  recordURL C(150) DEFAULT NULL,
  startTime T DEFAULT NULL
)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE ozmlout
(
  id I AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY, 
  dialedNumber C(40) NOT NULL,
  status C(40) DEFAULT NULL,
  duration I DEFAULT NULL,
  scriptId I DEFAULT NULL,
  recordUrl C(150) DEFAULT NULL,
  startTime T DEFAULT NULL
)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE ozmlscripts
(
  scriptid I AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY, 
  ozml C(10000) NOT NULL
)

*/ ?>

More information

< SQL SMS | Database logger >

+36 52 299 415

Home > Online manual > Developers guide > SQL API > Documentation > SQL OzML

Page: 5416 | 3.149.29.224 | 79.99.42.43 | Login

Privacy | Terms of use