Sending SMS messages from Linux using MySQL
A Linux Server is located at an Internet Service provider. It is runing a
MySQL database and some applications, such as PHP scripts and Java programs.
These applications use the MySQL database to store information.
The task is to provide SMS capability to this system. The applications would
like to send and receive SMS messages.
The best way to achive our goal is to use Ozeki Message Server 6 as an
SQL SMS gateway. This solution suggests the following system architecture: (Figure 1)
An Ozeki Message Server 6 - SMS Gatway software is installed on a windows server.
It uses a GSM modem to send and receive the SMS messages. This SMS Gateway software
connects to a MySQL database over the network using MyODBC. The software
uses two database tables in this database, one for outgoing and one for incoming
messages. The SMS Gateway queries the outgoing database table periodically using
an SQL select statement for outgoing messages. If it finds messages it downloads them and
sends them. During and after sending it updates a status field in the database
to notify the application on the Linux box about what happened to the message.
If an incoming message is received, it is saved into the incoming database table.
Figure 1 - SMS-SQL Gateway
I. The Linux system
Step 1.) Create the database layout
First you have to create a database table definition file called createdb.sql
based on the table definitions available on the following URL:
|[root@linux root]# echo "create database ozekidb" | mysql -u root|
[root@linux root]# cat createdbmysql.sql | mysql ozekidb -u root
Step 2.) Create a user for the Windows system
|[root@linux root]# mysql mysql -u root|
mysql> insert into user values ('%','winduser',passsword('winpass'),'Y',...
[root@linux root]# mysqladmin flush-privileges -u root
Step 3.) Set up the firewall
You must allow connection on port 3306 to your MySQL server. You can do this
by issuing the following command:
|[root@linux root]# iptables -I INPUT -p tcp --dport 3306 -j ACCEPT |
II. The Windows system
Step 1.) Install Ozeki Message Server 6 on the Windows Server
Step 2.) Configure the GSM Modem
Step 3.) Install MyODBC
Step 4.) Install the Database plugin
Step 5.) Configure the Database link
Step 6.) Modify the service user