Products
SMS
Ozeki NG SMS Gateway
Message Server 6
On-line manual
Introduction
SMS technology
MMS technology
About
Product features
Installation guide
User guide
Developers guide
General information
Languages
C++ SMS
SQL SMS
Installation
SQL scripts
SQL templates
State updates
Service owner
Incoming
Monitoring
Scheduling SMS
HTTP SMS
Files SMS
Java SMS
PHP SMS
ASP SMS
Delphi SMS
C# SMS
Access SMS
SMS FAQ
Case studies
Appendix
Download
Home
Support
How to buy
SMS Wall
IP SMS Gateway
Android SMS
Excel SMS
Ozeki Dialer
Ozeki Cluster
Developer tools
Robots
Hardware
Ozeki 10
Technology
Company


On-line manual:
  Explore all the features in this On-line HTML documentation of Ozeki Message Server 6.

Product guide:
  Download Ozeki Message Server 6 Manual in PDF format. This PDF guide explains all the features of Ozeki Message Server 6.
Ozeki Message Server 6
C++ SMS C++ SMS | Installation Installation

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).




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


Copyright © 2000- - Ozeki Informatics Ltd. | info@ozeki.hu | Tel: +36 1 371 0150
Home > Products > SMS > Message Server 6 > On-line manual > Developers guide > Languages > SQL SMS
Page: 425