Use Oracle to send and receive SMS messages

Oracle database

Diafaan SMS Server can use OLE DB to connect to the Oracle database. For this article Oracle 10g Express Edition was used with the Oracle 10g Provider for OLE DB.

Install the Oracle OLE DB provider

Before Diafaan SMS Server can use the database you have to make sure the OLE DB provider is installed on the computer running Diafaan SMS Server. You can download the drivers from the Oracle website.

Create the SMSServer database

On the database home page of your Oracle server you can use the SQL commands to create the database tables. Of course it is also possible to use an existing database, but if you want to be sure you can use all options available in Diafaan SMS Server you may use the SQL commands below. Since Oracle does not support auto increment fields in the CREATE TABLE statements some sequences and triggers must be created to provide the same functionality.

CREATE DATABASE SMSServer;

CREATE TABLE MessageOut (
Id NUMBER(19) NOT NULL PRIMARY KEY,
MessageTo NVARCHAR2(80),
MessageFrom NVARCHAR2(80),
MessageText NCLOB,
MessageType NVARCHAR2(80),
Gateway NVARCHAR2(80),
UserId NVARCHAR2(80),
UserInfo NCLOB,
Priority NUMBER,
Scheduled DATE,
ValidityPeriod NUMBER,
IsSent NUMBER(1) DEFAULT 0,
IsRead NUMBER(1) DEFAULT 0);

CREATE INDEX IDX_IsRead ON MessageOut (IsRead);

CREATE SEQUENCE MessageOutSequence START WITH 1 INCREMENT BY 1 NOMAXVALUE;

CREATE TRIGGER MessageOutTrigger
BEFORE INSERT ON MessageOut
FOR EACH ROW
BEGIN
SELECT MessageOutSequence.NEXTVAL INTO :NEW.Id FROM DUAL;
END;

CREATE TABLE MessageIn (
Id NUMBER(19) NOT NULL PRIMARY KEY,
SendTime DATE,
ReceiveTime DATE,
MessageFrom NVARCHAR2(80),
MessageTo NVARCHAR2(80),
SMSC NVARCHAR2(80),
MessageText NCLOB,
MessageType NVARCHAR2(80),
MessageParts NUMBER,
MessagePDU NCLOB,
Gateway NVARCHAR2(80),
UserId NVARCHAR2(80));

CREATE SEQUENCE MessageInSequence START WITH 1 INCREMENT BY 1 NOMAXVALUE;

CREATE TRIGGER MessageInTrigger
BEFORE INSERT ON MessageIn
FOR EACH ROW
BEGIN
SELECT MessageInSequence.NEXTVAL INTO :NEW.Id FROM DUAL;
END;

CREATE TABLE MessageLog (
Id NUMBER(19) NOT NULL PRIMARY KEY,
SendTime DATE,
ReceiveTime DATE,
StatusCode NUMBER,
StatusText NVARCHAR2(80),
MessageTo NVARCHAR2(80),
MessageFrom NVARCHAR2(80),
MessageText NCLOB,
MessageType NVARCHAR2(80),
MessageId NVARCHAR2(80),
ErrorCode NVARCHAR2(80),
ErrorText NVARCHAR2(80),
Gateway NVARCHAR2(80),
MessageParts NUMBER,
MessagePDU NCLOB,
Connector NVARCHAR2(80),
UserId NVARCHAR2(80),
UserInfo NCLOB);

CREATE INDEX IDX_MessageId ON MessageLog (MessageId, SendTime);

CREATE SEQUENCE MessageLogSequence START WITH 1 INCREMENT BY 1 NOMAXVALUE;

CREATE TRIGGER MessageLogTrigger
BEFORE INSERT ON MessageLog
FOR EACH ROW
BEGIN
SELECT MessageLogSequence.NEXTVAL INTO :NEW.Id FROM DUAL;
END;

Add the SQL Connector

Now you can add the new database to Diafaan SMS Server. First create a new SQL Connector with the connector wizard and select OLE DB databse as the database type. On the next page you can provide the connection string, the connection string below is an example of a connection string for the OLE DB provider and can be adapted to suit your database installation:

Connection string with TNS:
Provider=OraOLEDB.Oracle;Data Source=myOracleDB;User Id=myUsername;Password=myPassword;

Connection string without TNS:
Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myServerAddress)(PORT=1521)))(CONNECT_DATA=(SID=myOracleSID)(SERVER=DEDICATED)));User Id=myUsername;Password=myPassword;

Advanced Database Options

Select the database fields

On the next wizard pages you can select the database tables and fields you want Diafaan SMS Server to use. You can only select the most used fields here. Additional database fields can be selected later on the advanced tab page in the connector properties dialog.

It is a good idea to change the database flavor option on the same advanced tab page to Oracle. This will instruct Diafaan SMS Server to use a specific SQL syntax for Oracle enhancing performance somewhat.