SQL SMS API

Ozeki SMS Gateway provides a SQL SMS API that lets your application send and receive SMS messages through an SQL database server. Instead of exchanging HTTP requests with the gateway, your application writes and reads rows in two standard database tables: outgoing messages are sent by inserting them into the ozekimessageout table, and incoming messages arrive in the ozekimessagein table. This document is a practical guide to the API. It explains how to create a SQL messaging application (database user) in the gateway, lists the supported database servers, documents the layout of the two database tables, and demonstrates the most important operations with ready-to-use SQL examples: sending SMS messages, following their delivery status, and downloading incoming messages.

Table of Contents

Overview and architecture

This section describes the big picture: the three parties in the exchange, who initiates each request type, and in which direction the SQL statements travel.

Three parties communicate in this API:

• Your application — the database client you develop.
• SQL database server — the shared storage that connects your application to the gateway.
• Ozeki SMS Gateway — the messaging server connected to the mobile network.

Your application submits messages by inserting rows into the ozekimessageout table of the database. Ozeki SMS Gateway polls this table periodically, picks up the new rows, and sends them to the mobile network. For incoming messages the direction reverses: the gateway inserts the received messages into the ozekimessagein table, and your application downloads them by running SELECT queries. Because the database interface has no callback option, every exchange is table-driven and pull-based — nothing is pushed to your application.

Enable the API by creating a SQL messaging application

Before your application can use the API, you have to create a SQL messaging application (also called a database user) in Ozeki SMS Gateway. This application represents the database connection and holds its configuration, such as the connection settings, the SQL statements used to send and receive messages, and the polling interval. To create it, open the SMS Gateway, select the Apps icon from the toolbar, and install the SQL messaging application. From the list of available options, select the database server you use and click Install next to it.

For more detailed instructions, read the following pages:

• How to setup an SQL to SMS gateway
• How to send SMS from MS SQL Server

Configure the database connection

The last step of the creation of the SQL messaging application is to connect it to your database server by filling the fields of the Connection Settings. You have to give all the details about the database you want to connect to: the address (IP address and port) of the database server, the name of the database, and the user ID with the password that your application uses within the database server. When the connection is configured, turn it on with the switch button next to it.

In the Configure tab of the SQL messaging application you can modify the behavior of the connection. The Send tab defines the SQL statement that queries the outgoing messages, the maximum number of outgoing messages per query, and the interval of polling. The Receive tab defines the SQL statement that inserts the incoming messages into the database table.

Supported database servers

Ozeki SMS Gateway can connect to the following database servers through its SQL messaging application:

Any database server with an OleDB or ODBC driver is supported.

Database table layout

Ozeki SMS Gateway uses the ozekidb database with two tables. The table of incoming messages, ozekimessagein, is filled with data by Ozeki SMS Gateway using SQL insert statements. The table of outgoing messages, ozekimessageout, is read by Ozeki SMS Gateway using SQL select statements, and SQL update statements are used to set the statuses of the sent messages. This is the table into which your application inserts a new row for every message to be sent.

ozekimessagein — incoming messages:

ozekimessageout — outgoing messages:

To create the tables in Microsoft SQL Server, run the following commands:

CREATE DATABASE ozekidb
GO

USE ozekidb
GO

CREATE TABLE ozekimessagein (
 id int IDENTITY (1,1),
 sender varchar(255),
 receiver varchar(255),
 msg nvarchar(160),
 senttime varchar(100),
 receivedtime varchar(100),
 operator varchar(30),
 msgtype varchar(30),
 reference varchar(30),
);

CREATE TABLE ozekimessageout (
 id int IDENTITY (1,1),
 sender varchar(255),
 receiver varchar(255),
 msg nvarchar(160),
 senttime varchar(100),
 receivedtime varchar(100),
 operator varchar(100),
 msgtype varchar(30),
 reference varchar(30),
 status varchar(30),
 errormsg varchar(250)
);

You can add other tables and new columns to these tables. The application can freely delete records from the incoming table.

Send an SMS message without callback (overview)

Send an SMS message

To send an SMS message, insert a new row into the ozekimessageout table. The SMS Gateway periodically checks this table and sends the newly added messages. The receiver and the message text are the most important fields; the message type defaults to SMS:TEXT and the status must be set to send so that the gateway picks the row up.

This request is initiated by "Your application" and is executed on "the SQL database" using an SQL INSERT statement.

Request:

INSERT INTO ozekimessageout (receiver, msg, msgtype, status)
VALUES ('+36201234567', 'Hello World', 'SMS:TEXT', 'send');

After the insert, the row looks like this in the database:

SELECT id, sender, receiver, msg, msgtype, operator, status FROM ozekimessageout;

id | sender | receiver      | msg         | msgtype  | operator | status
---|--------|---------------|-------------|----------|----------|-------
 1 | NULL   | +36201234567  | Hello World | SMS:TEXT | ANY      | send

Ozeki SMS Gateway polls the table, picks up the row, submits the message to the mobile network and updates the status column. The id assigned by the database identifies the message and lets you follow it from submission to delivery.

Optional fields:

• sender: can be used to specify a customer SenderID. It can be a phone number, a short code or an alphanumeric sender address. By default it is assigned by the mobile network connection.
• operator: can be used to select the service provider connection that sends out the message. The default is ANY, which means the message is sent out using the first connection with free capacity.

Send multiple SMS messages

To send several SMS messages in one go, insert multiple rows into the ozekimessageout table in a single statement or in a batch. The gateway picks up all the newly added rows and sends each message to its recipient.

This request is initiated by "Your application" and is executed on "the SQL database" using an SQL INSERT statement.

Request:

INSERT INTO ozekimessageout (receiver, msg, msgtype, status) VALUES
('+36201234567', 'Hello World', 'SMS:TEXT', 'send'),
('+36209876543', 'This is the second message', 'SMS:TEXT', 'send'),
('+36201112233', 'This is the third message', 'SMS:TEXT', 'send');

Each inserted row receives its own id, and each id can be queried individually for its delivery status. The insert fields are the same as for sending a single message.

Check the status of sent SMS messages

To check the current delivery status of the SMS messages you have sent, run a SELECT query against the ozekimessageout table. The gateway writes the latest status of every message into the status column, so you can follow every message from submission to delivery.

This request is initiated by "Your application" and is executed on "the SQL database" using an SQL SELECT statement.

Request:

SELECT id, receiver, msg, status, errormsg FROM ozekimessageout
WHERE id IN (1, 2);

Response:

id | receiver      | msg                    | status    | errormsg
---|---------------|------------------------|-----------|--------------------------
 1 | +36201234567  | Hello World            | delivered | NULL
 2 | +36209876543  | This is the second ... | notsent   | No subscriber found by this MSISDN

The status column tells you the current state of each message, and the errormsg column stores the reason when the message could not be sent or delivered. The gateway only processes rows whose status is send, so you can re-submit a failed message by resetting its status to send.

Delete sent SMS messages from the database

After a sent SMS message has reached its final state, you can issue a DELETE request to remove it from the ozekimessageout table. Deleting is optional; the gateway leaves the rows in the table for you to archive or process.

This request is initiated by "Your application" and is executed on "the SQL database" using an SQL DELETE statement.

Request:

DELETE FROM ozekimessageout WHERE id IN (1, 2);

The affected row count returned by the database tells you how many messages were deleted, so you can verify that every message was removed.

Receive an SMS message without callback (overview)

Download incoming SMS messages

When you created the SQL messaging application, Ozeki SMS Gateway also created a routing rule which defines that all incoming SMS messages are copied into the database. Every received message is inserted into the ozekimessagein table automatically. To download the incoming messages, run a SELECT query against this table. You can control how many messages you download in one request.

This request is initiated by "Your application" and is executed on "the SQL database" using an SQL SELECT statement.

Request:

SELECT TOP 2 id, sender, receiver, msg, msgtype, receivedtime
FROM ozekimessagein
ORDER BY id;

Response:

id | sender        | receiver      | msg              | msgtype  | receivedtime
---|---------------|---------------|------------------|----------|-------------------
 1 | +36301234567  | +36201111111  | Hello World      | SMS:TEXT | 2026-10-02 08:48:24
 2 | +36301234567  | +36201111111  | Nice to meet you | SMS:TEXT | 2026-10-02 08:49:11

The SELECT query does not delete the downloaded messages from the table. After you have downloaded the messages from the ozekimessagein table, you should issue a DELETE request containing the list of ids you want to remove. For details, see the "Delete incoming SMS messages from the database" section below.

Delete incoming SMS messages from the database

After downloading the incoming messages from the ozekimessagein table, you should issue a DELETE request to remove them. Otherwise the same messages will be returned the next time you run the SELECT query. The DELETE request must contain the list of ids of the messages to be deleted. These ids are returned by the SELECT query in its result set.

This request is initiated by "Your application" and is executed on "the SQL database" using an SQL DELETE statement.

Request:

DELETE FROM ozekimessagein WHERE id IN (1, 2);

The affected row count returned by the database tells you how many messages were deleted, so you can verify that every message was removed.

SMS delivery statuses in the outbox table

Ozeki SMS Gateway writes the status of every outgoing message into the status column of the ozekimessageout table, so delivery information is reported in a uniform way, independently of the mobile network connection that was used.

Only rows with the status send are processed by the gateway. A failed message can be sent again by resetting its status column to send.

Data format conventions

These conventions apply to the database tables and keep the interface predictable and unambiguous.

• Column names are lowercase words without separators: sender, receiver, msg, msgtype, senttime, receivedtime, operator, status, errormsg.
• id is a unique integer assigned by the database (typically an identity column). It is the primary correlation key for status queries and deletes.
• receiver is mandatory and holds the phone number of the recipient in international format, e.g. +36201234567.
• status is mandatory and must be set to send when a new message is inserted, so the gateway picks the row up.
• msgtype is mandatory; the default is SMS:TEXT.
• Timestamps are strings in the format YYYY-MM-DD HH:MM:SS, e.g. 2026-10-02 08:49:11.
• Encoding is UTF-8 everywhere; use nvarchar columns to store message texts.
• Extensibility: you can add new columns and tables to the database; the gateway ignores fields it does not use.

Conclusion

To start using the SQL SMS API, create a SQL messaging application in Ozeki SMS Gateway, connect it to your database server with the connection settings, and create the ozekimessageout and ozekimessagein tables. Send SMS messages by inserting rows into the ozekimessageout table with the status set to send, and track their delivery through the status column, either by polling or by querying the table on demand. Receive SMS messages by reading the ozekimessagein table, which the gateway fills automatically through the routing rule created with the application. Because the database interface has no callback option, all communication is table-driven: the gateway polls the outgoing table, and your application polls the incoming table. With these building blocks, your application can send and receive SMS messages through Ozeki SMS Gateway using nothing but standard SQL statements.