- Product Manual
- Introduction
- SMS technology
- Installation Guide
- Prerequisites
- Download Information
- Installation Steps
- GSM Modem Setup
- Service Provider Connections
- Users and applications
- Standard user
- SMPP user
- SMS from or to database
- MSSQL
- MySQL
- MyODBC v8
- Database layout
- MyODBC driver installation
- Oracle
- Sybase SQL Anywhere
- PostgreSQL
- Pervasive SQL
- Date Format Strings
- Scheduled SMS
- SQL templates
- Informix
- Access
- Video
- Connection string builder
- Keywords
- MS SQL high speed
- Oracle High Speed
- SQL statements
- Polling technology
- Dynamic Keywords
- Dynamic SMPP parameters
- Autoreply SMS from database
- Autoreply SMS using a script
- Simple autoreply SMS
- SMS from/to file
- SMS through E-mail
- SMS via HTTP Request
- SMS via colour protocol
- Application starter SMS
- SMS Voting
- SMS from MS Outlook
- SMS from/to FTP server
- PDF Request in SMS
- SMS to WCF service
- SMS Forwarder
- RSS to SMS
- Linux
- User Guide
- Developers Guide
- Examples and Solutions
- Appendix
- SMS FAQ
- Feature list
- Commercial Information
- Search
SQL to SMS Gateway configuration - MySQL overview
Video tutorial:How to send and receive SMS from MySQLIn 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 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 you 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.
Figure 1 - Specifying the connection string for the MySQL database
After the database user has been configure in the SQL SMS gatway, the only thing left to do is to create the database tables ozekimessageout and ozekimessagin in your MySQL database. We recommend you to 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.
More information