SMS API for Database developers (SQL)

If you use the OZEKI Message Server you can send and receive SMS messages using a database server with the help of SQL queries.

In order to use this option, you need to have a database server (such as Oracle, Access, MySql, MS SQL, Postgres, Sybes, etc) installed, and there should be two database tables created: ozekimessageout and ozekimessagein. One of these will be used for sending and the other for receiving SMS messages.

Ozeki Message Server will connect to the database through a standard ADO or ODBC connection (Figure 1). Using this connection it will periodically query the database table ozekimessageout for outgoing messages using a SELECT statement and will INSERT incoming messages into the database table used for incoming messages (ozekimessagein).

sms message server connecting to a database server
Figure 1 - Ozeki Message Server connecting to a database server

If you want to send a message you can issue the following SQL statement:

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


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. This ID is used by Ozeki Message Server to update the status attribute of the message. For example if the message is accepted by the GSM service provider for delivery it's status field is updated to 'transmitted' to notify the database programmer what happened to the message.

The incoming messages are placed into the ozekimessagein 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 ozekimessagein; 

+--------------+--------------+-------------------+
| sender       | msg          | receivedtime      |
+--------------+--------------+-------------------+
| +36203105366 | test message | 01/11/03 08:43:32 |
+--------------+--------------+-------------------+
1 row in set (0.00 sec)


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 or stored procedures. Here is an example:

Autoreply function with a Microsoft SQL Server:

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

										
					
More information