Sending SMS messages from Linux using MySQL

SCENARIO

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.

SOLUTION

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 - Linux SMS-SQL Gateway

INSTALLATION

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:

https://ozeki.hu/p_422-sql-scripts.html

[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

More information