How to... > Access your database

Learn how to send and receive SMS messages using an Oracle database with Diafaan SMS Server. Get instructions about the installation of the OLE DB driver, create the database with an SQL script and how to connect to the database with the connection string for Oracle.

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 send and receive SMS messages from the database you have to make sure the OLE DB provider is installed on the computer running Diafaan SMS Server. You can get the Oracle OLE DB provider software on 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, a number of 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),

MessageGuid NCLOB,

MessageInfo NCLOB,

Gateway NVARCHAR2(80),

UserId NVARCHAR2(80),

UserInfo NCLOB,

Priority NUMBER,

Scheduled DATE,

ValidityPeriod NUMBER,

TLVList NCLOB,

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

MessageGuid NCLOB,

MessageInfo NCLOB,

ErrorCode NVARCHAR2(80),

ErrorText NCLOB,

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 database 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;

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.

For more information see Access your database.

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.