How to setup SQL SMS

Ozeki Phone System enables you to send and receive SMS messages, using your database server. For using this feature, you only need to handle two tables: insert a message into the outbox table for sending, and find incoming SMSs in the inbox table. After sending an SMS, delivery state information appear automatically to help you track outgoing SMS traffic efficiently In this guide you will see how to install and configure SQL SMS API in Ozeki Phone System.

Step 1: How to install SQL SMS API
Step 2: How to configure SQL SMS API
Step 3: Table structure
Step 4: Create table scripts

Step 1: How to install SQL SMS API

On the next page, click on the Install button right next to the SQL SMS API (Figure 4).

how to install sql sms in ozeki phone system
Figure 1 - Install SQL SMS

Step 2: How to configure SQL SMS API

In this step you will see how to configure SQL SMS API. Under the Database Connection tab you should to setup the following options.

First you need to enter the Connection name which will be the phone number of the extension by default. 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. (If you selected Odbc or OleDb data sources you can reach only the Advanced configuration mode.) The Simple 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. You can find different connection strings related to a lot of database servers here.

database connection in ozeki phone system
Figure 2 - Database Connection

In order to send or receive SMS messages using Ozeki Phone System, you need to have an outside line that is able to do this, such as a GSM Modem or an SMPP Connection.

In addition, you need to setup a Message routing rule that will forwards the messages between the SQL SMS API and the outside line.

In case of the SQL SMS API, you will see two further tabs: SQL for sending and SQL for receiving.

Under the SQL for sending tab, you can set the following:

  • Use this database connection for sending outgoing messages: If this checkbox is enabled, you can use this SQL API for sending SMS messages.
  • Check the ozekimessageout table for outgoing messages every: The ozekimessageout table will be queried in every 30 seconds. If there are messages with send status, those messages will be sent out.
  • Maximum number of messages to send with one poll: With one poll 10 (or the specified) messages will be sent out.
  • SQL templates: In the SQL templates section, you can setup the queries to be used by Ozeki Phone System XE.

SQL queries:

  • Polling: This query selects the messages with send status. When you want to send messages, first you need to insert them with send status.
  • Sending: The PBX find these messages (with send status) and updates the status to sending.
  • Sent: The Ozeki PBX tries to send them to the mobile network. If it accepted them, the message status changes to sent.
  • Notsent: If the mobile network rejects a message, it will be in notsent status.
  • Delivered: When the delivery report is received, the Ozeki PBX updates the database record to delivered status.
  • Undelivered: If the mobile network cannot send out the messages to the recipient's phone, it will send an error report, and Ozeki updates the states to Undelivered.

Columns in the ozekimessageout table:

  • sender: The sender ID.
  • receiver: The phone number of the receiver.
  • msg: The text of the message.
  • senttime: The time when the sms was sent.
  • receivedtime: The time when the sms was received to the receiver's phone.
  • reference: The reference id of the message. If you wish to store it, you need to modify the SET section of the following queries: Sending, Sent, Delivered, Undelivered with this: ", reference='$callbackid'" (e.g. UPDATE ozpbxmessageout SET status='sent', senttime='$senttime', reference='$callbackid' WHERE id='$id').
  • status: The state in which the message is at the given moment (e.g. send, sending, notsent, sent, delivered, undelivered).
  • msgtype: The type of the message (e.g. SMS:TEXT)
  • operator: The name of the outside line connection, that can be used to send this message.
  • errormsg: The error reason whether the message was not sent.

sql query for sending sms messages
Figure 3 - SQL query for sending SMS messages

Under the SQL for receiving tab, you can configure the followings:

  • SQL insert: This SQL query is used for inserting incoming messages into the database.
  • Replace character or string: It is possible that you need to change special characters in your SQL queries. In the left textbox write the character to be replaced, and in the right textbox insert the character that will replace the character on the left.

Columns in the ozekimessageout table:

  • sender: The phone number of the sender.
  • receiver: The phone number of the Outside line of the PBX.
  • msg: The text of the message.
  • senttime: The time when the sms was sent from the receiver's phone.
  • receivedtime: The time when the sms was received to the PBX.
  • extension: The extension name to which the message was forwarded.

sql queries for receiving sms messages
Figure 4 - SQL queries for receiving SMS messages

Step 3: Table structure

After the SQL SMS API has been configured in the Ozeki Phone System, you need to create the database tables for SQL SMS API. Use the following table layout:

  • ozpbxmessageout table
Name Type
id int (primary key, auto increment)
sender varchar(30)
receiver varchar(30)
msg text
senttime varchar(100)
receivedtime varchar(100)
reference varchar(100)
status varchar(20)
msgtype varchar(160)
operator varchar(100)
errormsg varchar(250)
  • ozpbxmessagein table
Name Type
id int (primary key, auto increment)
sender varchar(30)
receiver varchar(30)
msg text
senttime varchar(100)
receivedtime varchar(100)
extension varchar(100)

Step 4: Create table scripts

Microsoft SQL Server
SQL SMS 
CREATE TABLE ozpbxmessageout
(
id int identity(1,1) PRIMARY KEY,
sender varchar(30) NULL,
receiver varchar(30) NULL,
msg text NULL,
senttime varchar(100) NULL,
receivedtime varchar(100) NULL,
reference varchar(100) NULL,
status varchar(20) NULL,
msgtype varchar(160) NULL,
operator varchar(100) NULL,
errormsg varchar(250) NULL
);

CREATE TABLE ozpbxmessagein (
id int identity(1,1) PRIMARY KEY,
sender varchar(30) NULL,
receiver varchar(30) NULL,
msg text NULL,
senttime varchar(100) NULL,
receivedtime varchar(100) NULL,
extension varchar(100) NULL
);
Microsoft SQL Express
SQL SMS 
CREATE TABLE ozpbxmessageout
(
id int identity(1,1) PRIMARY KEY,
sender varchar(30) NULL,
receiver varchar(30) NULL,
msg text NULL,
senttime varchar(100) NULL,
receivedtime varchar(100) NULL,
reference varchar(100) NULL,
status varchar(20) NULL,
msgtype varchar(160) NULL,
operator varchar(100) NULL,
errormsg varchar(250) NULL
);

CREATE TABLE ozpbxmessagein (
id int identity(1,1) PRIMARY KEY,
sender varchar(30) NULL,
receiver varchar(30) NULL,
msg text NULL,
senttime varchar(100) NULL,
receivedtime varchar(100) NULL,
extension varchar(100) NULL
);
Oracle
SQL SMS 
CREATE TABLE ozpbxmessageout
(
id integer PRIMARY KEY,
sender varchar(30) default NULL,
receiver varchar(30) default NULL,
msg nclob default NULL,
senttime varchar(100) default NULL,
receivedtime varchar(100) default NULL,
reference varchar(100) default NULL,
status varchar(20) default  NULL,
msgtype varchar(160) default NULL,
operator varchar(100) default NULL,
errormsg varchar(250) default NULL
)

CREATE SEQUENCE id_seq START WITH 1 INCREMENT BY 1;

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

CREATE TABLE ozpbxmessagein
(
id integer PRIMARY KEY,
sender varchar(30) default NULL,
receiver varchar(30) default NULL,
msg nclob default NULL,
senttime varchar(100) default NULL,
receivedtime varchar(100) default NULL,
extension varchar(100) default NULL
)

CREATE SEQUENCE id_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE ozpbxmessagein_insert
BEFORE INSERT ON ozpbxmessagein
FOR EACH ROW
BEGIN
SELECT id_seq.nextval INTO :new.id FROM dual;
END;
/
MySQL
SQL SMS 
CREATE TABLE `ozpbxmessageout` (
`id` int(11) auto_increment,
`sender` varchar(30) default NULL,
`receiver` varchar(30) default NULL,
`msg` text default NULL,
`senttime` varchar(100) default NULL,
`receivedtime` varchar(100) default NULL,
`reference` varchar(100) default NULL,
`status` varchar(20) default NULL,
`msgtype` varchar(160) default NULL,
`operator` varchar(100) default NULL,
`errormsg` varchar(250) default NULL,
INDEX (`id` ASC),
PRIMARY KEY (id))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE `ozpbxmessagein` (
`id` int(11) auto_increment,
`sender` varchar(30) default NULL,
`receiver` varchar(30) default NULL,
`msg` text default NULL,
`senttime` varchar(100) default NULL,
`receivedtime` varchar(100) default NULL,
`extension` varchar(100) default NULL,
INDEX (`id` ASC),
PRIMARY KEY (id))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
PostgreSQL
SQL SMS 
CREATE TABLE ozpbxmessageout 
(
id integer PRIMARY KEY DEFAULT nextval('serial'),
sender varchar(30) NULL,
receiver varchar(30) NULL,
msg text NULL,
senttime varchar(100) NULL,
receivedtime varchar(100) NULL,
reference varchar(100) NULL,
status varchar(20) NULL,
msgtype varchar(160) NULL,
operator varchar(100) NULL,
errormsg varchar(250) NULL
)

CREATE TABLE ozpbxmessagein
(
id integer PRIMARY KEY DEFAULT nextval('serial'),
sender varchar(30) NULL,
receiver varchar(30) NULL,
msg text NULL,
senttime varchar(100) NULL,
receivedtime varchar(100) NULL,
extension varchar(100) NULL
)
Sybase (SQL Anywhere)
SQL SMS 
CREATE TABLE ozpbxmessageout (
id int NOT NULL autoincrement,
sender char(30) DEFAULT NULL,
receiver char(30) DEFAULT NULL,
msg text DEFAULT NULL,
senttime char(100) DEFAULT NULL,
receivedtime char(100) DEFAULT NULL,
reference char(100) DEFAULT NULL,
status char(20) DEFAULT NULL,
msgtype char(160) DEFAULT NULL,
operator char(100) DEFAULT NULL,
errormsg char(250) DEFAULT NULL,
PRIMARY KEY (id)
)

CREATE TABLE ozpbxmessagein (
id int NOT NULL autoincrement,
sender char(30) DEFAULT NULL,
receiver char(30) DEFAULT NULL,
msg text DEFAULT NULL,
senttime char(100) DEFAULT NULL,
receivedtime char(100) DEFAULT NULL,
extension char(100) DEFAULT NULL,
PRIMARY KEY (id)
)
DB2
SQL SMS 
CREATE TABLE ozpbxmessageout
(id integer NOT NULL GENERATED ALWAYS AS 
    IDENTITY (START WITH 1 INCREMENT BY 1),
sender        VARCHAR(30)  NULL,
receiver      VARCHAR(30)  NULL,
msg           CLOB         NULL,
senttime      VARCHAR(100) NULL,
receivedtime  VARCHAR(100) NULL,
reference     VARCHAR(100) NULL,
status        VARCHAR(20)  NULL,
msgtype       VARCHAR(160) NULL,
operator      VARCHAR(100) NULL,
errormsg      VARCHAR(250) NULL,
PRIMARY KEY (id))

CREATE UNIQUE INDEX INDEX ozpbxmessageoutindex ON 
                ozpbxmessageout (id)

CREATE TABLE ozpbxmessagein
(id integer NOT NULL GENERATED ALWAYS AS 
    IDENTITY (START WITH 1 INCREMENT BY 1),
sender        VARCHAR(30)  NULL,
receiver      VARCHAR(30)  NULL,
msg           CLOB         NULL,
senttime      VARCHAR(100) NULL,
receivedtime  VARCHAR(100) NULL,
extension     VARCHAR(100) NULL,
PRIMARY KEY (id))

CREATE UNIQUE INDEX INDEX ozpbxmessageinindex ON 
                ozpbxmessagein (id)
Informix
SQL SMS 
CREATE TABLE ozpbxmessageout
(
id serial PRIMARY KEY, 
sender char(30) NULL,
receiver char(30) NULL,
msg text NULL,
senttime char(100) NULL,
receivedtime char(100) NULL,
reference char(100) NULL,
status char(20) NULL,
msgtype char(160) NULL,
operator char(100) NULL,
errormsg char(250) NULL
)

CREATE TABLE ozpbxmessagein
(
id serial PRIMARY KEY,
sender char(30) NULL,
receiver char(30) NULL,
msg text NULL,
senttime char(100) NULL,
receivedtime char(100) NULL,
extension char(100) NULL
)
FoxPro
SQL SMS 
CREATE TABLE ozpbxmessageout 
(id I AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY, 
sender C(30) NULL,
receiver C(30) NULL,
msg N NULL,
senttime C(100) NULL,
receivedtime C(100) NULL,
reference C(100) NULL,
status C(20) NULL,
msgtype C(160) NULL,
operator C(100) NULL,
errormsg C(250) NULL)

CREATE TABLE ozpbxmessagein
(id I AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY, 
sender C(30) NULL,
receiver C(30) NULL,
msg N NULL,
senttime C(100) NULL,
receivedtime C(100) NULL,
extension C(100) NULL)

If you have any questions or need assistance, please contact us at info@ozekiphone.com.

More information