SQL Reporting in the SMS Gateway - Database layout (MySQL)

This page gives you the database connection information and the database layout if you want to setup SQL reporting in a MySQL database server. SQL reporting makes it possible to keep track of all incoming and outgoing messages in the system regardless of who sent it.

To setup SQL reporting with MySQL, you need to install the MySQL ODBC v8 driver.

You can find information on how to install this driver in the MySQL ODBC v8 installation guide.

The database connection type, you should choose is: 

Odbc

The database connection string you should use is: 

Driver={MySQL ODBC 8.0 Unicode Driver};Server=127.0.0.1;Database=ozekisms;User=ozeki;Password=abc123;Option=4;

The recommended database table layout for SQL reporting into a MySQL database contains two database tables called outbox and inbox. Their definitions are displayed in the following section. 

CREATE TABLE  outbox (
id int not null auto_increment primary key,
username varchar(30),
msgtype varchar(160),
msgid varchar(60),
callbackid varchar(255),
sender varchar(255),
receiver varchar(255),
msgsubject varchar(1024),
msgdata text,
acceptedfordeliverytime datetime,
deliveredtonetworktime datetime,
deliveredtohandsettime datetime,
operator varchar(30),
route varchar(30),
status varchar(130),
errormessage varchar(1024),
cost varchar(10),
INDEX(msgid)
) charset=utf8;


CREATE TABLE  inbox (
id int not null auto_increment primary key,
username varchar(30),
msgtype varchar(160),
msgid varchar(60),
sender varchar(255),
receiver varchar(255),
msgsubject varchar(1024),
msgdata text,
senttime datetime,
receivedtime datetime,
operator varchar(30)
) charset=utf8;

If you want to change these table definitions to support your requirements, do not forget to review (and change if necessary) the SQL templates that are configured in the server preferences form.

More information