SQL templates for high-capacity SMS sending

This section explains how to customize the SQL commands used to access your SMS database tables. Customizing the SQL commands to access the database tables is a key part of configuring the SQL-SMS gateway. SQL templates provide a practical solution, enabling database configurators to save time and maintain consistency in their database operations. These pre-written SQL queries can be easily customized and reused for various purposes, ensuring the efficiency and reliability of the SQL to SMS system.

What are SQL templates

SQL templates are pre-written SQL queries that can be customized and reused for different purposes. They can be used to save time and ensure consistency in database operations. Some useful examples of SQL SMS templates include:
  1. INSERT INTO ozekimessagein - when you insert incoming messages into the ozekimessagein table
  2. INSERT INTO ozekimessageout- when you insert outgoing messages into the ozekimessageout table
  3. SELECT - you use it when you send out messages
  4. UPDATE - when the SMS program automatically changes the status of the SMS

5 reasons why it's worth to use database for high-capacity SMS sending

  • Scalable solution to handle high volumes of SMS messages
  • Efficient data management and retrieval for SMS campaigns
  • Ensure SMS data integrity, reducing errors in message transmission
  • Advanced querying for targeted SMS recipient segmentation and reporting
  • Seamless integration with SMS platforms for cost-effective SMS sending

The default operation of the SMS gateway

By default, the SMS gateway uses two database tables (see in: Ozeki SMS Gateway database table layout). One of the tables (ozekimessagein) is used for receiving SMS messages. The other (ozekimessagout) is used to send SMS messages. If an SMS message arrives to your gateway, it will be inserted into the ozekimessagein database table using the following SQL command:

INSERT INTO ozekimessagein (sender, receiver, msg, senttime, receivedtime, msgtype, operator) VALUES ('$originator','$recipient','$messagedata','$senttime','$receivedtime','$messagetype','$operatornames')

Before this statement is executed by the SMS gateway the keywords (e.g.: $originator, $recipient,...) will be replaced with the corresponding values of the incoming SMS message.

If you want to send an SMS message, you have to create a record in the ozekimessagout table. The status of the message should be set to "send". The SMS gateway will read the ozekimessagout table using the following SQL query to find your message:

SELECT id,sender,receiver,msg,msgtype,operator FROM ozekimessageout WHERE status='send'

After your outgoing SMS messages have been successfully fetched, the record will be updated using the following SQL command:

UPDATE ozekimessageout SET status='sending' WHERE id='$id'

This indicates that your SMS messages have been accepted for delivery by the SMS gateway and are saved into the outbox of the SMS gateway. Sometime after this, the following events can happen to your message:

  1. delivered to network
  2. delivery to the network is not possible
  3. delivered to the handset
  4. delivery to the handset is not possible

Depending on which one of these events occurs one of the following SQL commands will be executed on your database record:

If the "delivered to network" event occurs:

UPDATE ozekimessageout SET status='sent', senttime='$senttime' WHERE id='$id'

If the "delivery to the network is not possible" event occurs: UPDATE ozekimessageout SET status='notsent' WHERE id='$id'

If the "delivered to handset" event occurs: UPDATE ozekimessageout SET status='delivered' WHERE id='$id'

If the "delivery to the handset is not possible" event occurs: UPDATE ozekimessageout SET status='undelivered' WHERE id='$id'

If the status of the message stays in "sending", it indicates, that the Ozeki software has read the message from the database. The message was accepted for delivery and was stored in the outbox folder, but it was not yet accepted by the mobile network operator. It means that it is "waiting for delivery". In this case, if you look at the Graphical User Interface (GUI) of Ozeki SMS Gateway, you should see the message in your outbox.

If the status of the message stays in "sending" for a long time, it is highly probable that the messages are not delivered because the mobile network connection is not functioning in your system. To resolve this issue, please check if the service provider connection's link (SMPP, UCP, CIMD2) is working.

Customized operation of the SMS Gateway

To change this behaviour, you should modify the SQL templates used by the SMS gateway. This can be done by opening the "Send" or the "Receive" tab of the "Database configuration form" (Figure 1).

how to update the sql templates
Figure 1 - How to update the SQL templates

On this form, you can specify an SQL command that is valid in your database server. You can put stored procedures and complex SQL statements. As long as the SQL command can be executed, the SMS gateway can use it.

Conclusion

SQL templates provide a convenient and efficient way to customize the SQL commands used for accessing and manipulating database tables in the context of an SQL to SMS gateway configuration. By utilizing pre-written SQL queries that can be easily customized and reused, users can save time and ensure consistency in their database operations.

The default operation of the SMS gateway involves two tables, namely "ozekimessagein" for receiving SMS messages and "ozekimessageout" for sending SMS messages. Through the use of INSERT, SELECT, and UPDATE SQL commands, incoming and outgoing messages are appropriately managed within these tables. However, the SMS gateway's behaviour can be further customized by modifying the SQL templates, allowing users to tailor the system's operation to their specific requirements.

By updating the SQL templates through the provided database configuration form, users can specify their desired SQL commands, including stored procedures and complex statements, to be executed by the SMS gateway. This flexibility empowers users to adapt the system's behaviour according to their database server's capabilities and their unique needs.

More information