Setup Oracle
for sending and receiving SMS

In this chapter you can learn about how to send and receive SMS messages using a MySQL database server with your Ozeki Phone System. In this guide you can get information about the installation steps, the database connection string, the database connection type and the database table layout for Oracle database server.

During the configuration you should choose the following database connection type:

You should use the next database connection string properly for your OleDB driver:

The database connection string have to be modified to match your database. You should use the ip address or the hostname of your database server instead of the "127.0.0.1". Replace "OzekiPBX" to the name of you database and replace "ozeki" and "qwe123" to the username and password that can be used to connect to your database server.

The connection string and the connection type should be entered on the SQL SMS API configuration form.

Figure 2 shows the SQL queries that will send the SMS messages from your database.

Under the SQL for receiving tab, you need to provide an SQL query that will store the incoming SMS messages. Figure 3 shows the SQL query that you need to use.

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:

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessageout`
-- -----------------------------------------------------
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;
/

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessagein`
-- -----------------------------------------------------
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;
/

It is strongly recommended to maintain an index in the database for the 'id' field of the outgoing SMS table to support faster SQL updates.

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

Dig deeper!
People who read this also read...

• How to setup SMPP IP SMS connection in Ozeki Phone System
• How to connect your PBX to the mobile network
• How to send SMS from Ozeki Phone System by using Android SMPP SMS Gateway

More information

• Log call records
• Send and receive SMS
• Make phone calls
• Authenticate PBX users
• Log PBX events