Send and Receive SMS messages with the help of a Database

If you use the OZEKI SMS Server you can send and receive SMS messages with a database query. In this case the SMS server is connected to the selected database through a standard ODBC connection. The database can be Oracle, Access, MSSql or any other database engine that has an ODBC driver. Through this ODBC connection the Ozeki SMS Server inserts the incoming messages into a database table and reads out the messages that should be sent from another table.

sqlex
Figure 1 - SQLex

If you want to send and receive messages this way, first you should create two database tables: ozekismsout and ozekismsin. You can find the table definitions for these two tables at the following page: SQL Create Table Script (Ozeki SMS Server)

To send a message you should issue the following SQL statement: 

sql> INSERT INTO ozekismsout (receiver,msg,id,status)
VALUES ('+36203105366','test message',23,'send');

Code 1 - Sending a message

It is important to mention, that the SMS Server identifies the messages by their ids. You must make sure that you specify a unique id for each message or you should use auto_increment columns.

The incoming messages are placed into the ozekismsin table. You can see the create definition for this table at the url above as well. To view the incoming messages you can use the select statement. 

sql> SELECT sender,msg,receivedtime FROM ozekismsin;
+--------------+--------------+-------------------+
| sender       | msg          | receivedtime      |
+--------------+--------------+-------------------+
| +36203105366 | test message | 01/11/03 08:43:32 |
+--------------+--------------+-------------------+
1 row in set (0.00 sec)

Code 2 - The result of a select statement

It is interesting that often, without any external application you can create powerful SMS enabled solutions. You can do this with the help of database triggers. Here is an example:

Autoreply function with a Microsoft SQL Server: 

CREATE TRIGGER autoreply ON ozekismsin FOR INSERT
AS
DECLARE @tel VARCHAR(30)
SELECT TOP 1 @tel=sender FROM ozekismsin ORDER BY ID DESC
INSERT INTO ozekismsout (receiver,msg,status) VALUES
            (@tel,'Thank you for the message','send')
GO

Code 3 - An autoreply function with a Microsoft SQL Server

More information