SQL to SMS Gateway configuration - MySQL overview

In this chapter you can learn how to configure the SMS gateway to be able to send and receive SMS messages using a MySQL database server. You will be provided with the installation steps, the configuration details such as the database connection string, database connection type, and with the recommended database table layout.

To be able to send and receive SMS from a MySQL database you will need to install the MyODBC driver to your computer. This driver can be downloaded from the MySQL website. We recommended to use MyODBC v5.1, because it supports unicode characters.

After the MyODBC driver has been installed on the computer, you need to configure a database user in the SMS Gateway.

During the configuration the database connection type, you should choose is:

Odbc

The database connection string you should use is:

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

For newer versions, you will need to update the driver's name. For example, if you install MySQL ODBC 8.0 you could use the following connection string:

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

Please note that the database connection string should be modified to match your database. You should replace the "127.0.0.1" ip address to the hostname or ip address of your database server. Replace "ozekisms" to the name of your database. Replace "ozeki" and "abc123" 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 Database user configuration form.

specifying the connection string for the mysql database
Figure 1 - Specifying the connection string for the MySQL database

After the database user has been configure in the SQL SMS gateway, the only thing left to do is to create the database tables ozekimessageout and ozekimessagin in your MySQL database. We recommend that you use the following table layout:

MySQL create table script:

CREATE TABLE ozekimessagein (
  	id int(11) NOT NULL auto_increment,
  	sender varchar(255) default NULL,
  	receiver varchar(255) default NULL,
  	msg text default NULL,
  	senttime varchar(100) default NULL,
  	receivedtime varchar(100) default NULL,
  	operator varchar(100) default NULL,
  	msgtype varchar(160) default NULL,
  	reference varchar(100) default NULL,
  	PRIMARY KEY (id)
  	) charset=utf8;
ALTER TABLE ozekimessagein ADD INDEX (id); 
CREATE TABLE ozekimessageout (
  	id int(11) NOT NULL auto_increment,
  	sender varchar(255) default NULL,
  	receiver varchar(255) default NULL,
  	msg text 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,
  	PRIMARY KEY (id)
  	) charset=utf8;
ALTER TABLE ozekimessageout ADD INDEX (id);

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 wish you can add additional columns or you can modify these database tables. If you do please review the SQL templates used by the SMS gateway to make sure they will be compatible with the customized database table layout.

FAQs

I would like to use one database for many "database users". Is this possible?

Yes, you can use one database for multiple users in Ozeki NG, but each user needs separate tables.

  • First User: Use the existing tables ozekimessagein and ozekimessageout for receiving and sending messages.
  • Additional Users: Create new tables with unique names (e.g., ozekimessagein2 and ozekimessageout2) with the same structure as the originals. Then, modify the SQL templates in Ozeki NG for each user to point to their specific tables.

When I insert Data into Ozekimessageout table I didn't get message to my cell phone. What would be cause that I didn't get the text message that it is inserted into out table using my SQL console?

Is it Ozeki NG or the mobile network?

  1. Try sending a test SMS directly from the Ozeki NG software interface. If it arrives, the mobile network connection is likely working properly.
Did the message get from the database to Ozeki NG?
  1. Insert some test messages into the ozekimessageout table.
  2. In the Ozeki NG software GUI, check the outbox for the specific database user you're using. Messages there confirm successful transfer from the database.
Finding the culprit:
  1. Within Ozeki NG, locate the event list for the module you're using (e.g., Database User).
  2. Look for any error messages. These messages often provide hints to help you resolve the problem.

My Ozeki NG SMS Gateway is always disconnecting from my MySQL server with the following error message: MySQL Server has gone away. What should I do?

1. Adjust MySQL Timeout:

  • You need to set the interactive_timeout value in your MySQL configuration file (often named my.ini). This value controls how long MySQL waits for inactive connections before closing them.
2. Modify Ozeki NG Connection String:
  • After setting the timeout, restart the MySQL service.
  • In Ozeki NG, update the connection string to use the interactive_timeout parameter instead of wait_timeout.
While I can't provide the full connection string due to security reasons (username and password), here's a general format: 

Driver={MySQL ODBC 5.2 ANSI Driver};Server=127.0.0.1;Database=ozeki;User=test;Password=test;interactive=;Option=8;

Replace username and password with your actual credentials.

Important Note:

For specific connection string formatting and configuration steps, consult your Ozeki NG documentation. This will ensure the changes are made correctly for your particular setup.

More information