Use MySQL to send and receive SMS messages

MySQL

MySQL is one of the most popular database programs in the world. Diafaan SMS Server can use an ODBC driver to connect to MySQL. For this article MySQL version 5.0.88 was used with the MyODBC 5.1 driver.

Install the MyODBC driver

Before Diafaan SMS Server can use the database you have to make sure the MyODBC 5.1 driver is installed on the computer running Diafaan SMS Server. You can find the MyODBC driver here.

Create the SMSServer database

You can use the MySQL command line client 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 necessary to create the database tables to send and receive SMS messages and to keep the send log.

CREATE DATABASE SMSServer DEFAULT CHARACTER SET utf8mb4;

CREATE TABLE MessageOut (
Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
MessageTo VARCHAR(80),
MessageFrom VARCHAR(80),
MessageText TEXT,
MessageType VARCHAR(80),
Gateway VARCHAR(80),
UserId VARCHAR(80),
UserInfo TEXT,
Priority INT,
Scheduled DATETIME,
ValidityPeriod INT,
IsSent TINYINT(1) NOT NULL DEFAULT 0,
IsRead TINYINT(1) NOT NULL DEFAULT 0) CHARACTER SET utf8mb4;

CREATE INDEX IDX_IsRead ON MessageOut (IsRead);

CREATE TABLE MessageIn (
Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
SendTime DATETIME,
ReceiveTime DATETIME,
MessageFrom VARCHAR(80),
MessageTo VARCHAR(80),
SMSC VARCHAR(80),
MessageText TEXT,
MessageType VARCHAR(80),
MessageParts INT,
MessagePDU TEXT,
Gateway VARCHAR(80),
UserId VARCHAR(80)) CHARACTER SET utf8mb4;

CREATE TABLE MessageLog (
Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
SendTime DATETIME,
ReceiveTime DATETIME,
StatusCode INT,
StatusText VARCHAR(80),
MessageTo VARCHAR(80),
MessageFrom VARCHAR(80),
MessageText TEXT,
MessageType VARCHAR(80),
MessageId VARCHAR(80),
ErrorCode VARCHAR(80),
ErrorText VARCHAR(80),
Gateway VARCHAR(80),
MessageParts INT,
MessagePDU TEXT,
Connector VARCHAR(80),
UserId VARCHAR(80),
UserInfo TEXT) CHARACTER SET utf8mb4;

CREATE INDEX IDX_MessageId ON MessageLog (MessageId, SendTime);

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 ODBC as the database type. On the next page you can provide the ODBC connection string, the following connection string should be adapted for your database installation:

Driver={MySQL ODBC 5.1 Driver};Server=myServerAddress;charset=UTF8MB4;Database=myDataBase;User=myUsername; Password=myPassword;Option=3;
Send SMS table

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 MySQL. This will instruct Diafaan SMS Server to use a specific SQL syntax for MySQL enhancing performance somewhat.