SMS replies from an SQL Database

Ozeki SMS Gateway's Autoreply Database User can connect to your Microsoft SQL, Oracle, MySQL, Sybase, etc... database with a connection string. It runs any SELECT query you provide, after receiving an SMS message. The result set of the query will be sent as outgoing messages. The queries and tables can also be modified from your own application.

How to send an SMS reply from SQL (Quick steps)

To send an SMS reply from SQL:

1. Launch Ozeki SMS Gateway
2. Add new Autoreply database user
3. Create SQL database table structure
4. Setup Autoreply database user
5. Provide the autoreply text script
6. Use identifiable numbers and keywords
7. Send test SMS to get reply from SQL
8. Check the Autoreply database user logs

You can create the following service by using this solution (Figure 1):
1.) An SMS message is received by Ozeki SMS Gateway from a mobile phone.
2.) Ozeki SMS Gateway's Autoreply Database User searches for keywords in the SMS message by using a txt file.
3.) Depending on the search, an autoreply message will be selected from your database server with the same txt file.

It is important that you have a IP SMS service provider or a GSM modem configured on your Ozeki SMS Gateway, so you can receive SMS messages and automatically reply to them using this example.

Step 1 - Setup Autoreply Database User in Ozeki SMS Gateway

First you need to install an Autoreply Database User to create the explained service. On the 'Management' console please click 'Add' as seen on Figure 2.

Find the Autoreply Database User and click the blue 'install' link next to it. As you can see it on Figure 3.

Step 2 - Create database table structure

The Autoreply Database User will SELECT the reply SMS messages from a database table. Create the following database structure to store the SMS messages (Figure 4). This example uses a MySQL database server, but you can use other databases like Microsoft SQL, Oracle, Sybase, etc... You use other tables and table layouts as well.

CREATE TABLE autoreplymessage (
		 id int(11) NOT NULL auto_increment, 
		 keyword varchar(30) default NULL, 
		 msg text default NULL, 
		 PRIMARY KEY (id)
		 );
				
INSERT INTO autoreplymessage (keyword,msg) VALUES 
		("default","There is no data for this keyword."), 
		("red","Red is a good colour."), 
		("blue","Blue is not the best option.");

Figure 4 - CREATE TABLE and INSERT rows into 'autoreplymessage' table

Your tables can be viewed with SELECT * FROM statements (Figure 5). Although this example contains only one table.

> SELECT * FROM autoreplymessage;

id          keyword                        msg
----------  ------------------------------ -------------------------------------
 1          default                        There is no data for this keyword.
 2          red                            Red is a good colour.
 3          blue                           Blue is not the best option.

(3 rows affected)

Figure 5 - Read 'autoreplymessage' table with a SELECT statement

In case there is no database driver installed with the database server, you should install a driver, because you will need the connection string in the next step. The database driver connects Ozeki SMS Gateway and the database server. In most cases there is a database driver installed with the database server.

Step 3 - Configure Autoreply Database User to use your database

You will need to enter the database connection type and connection string in the Autoreply Database User's 'Database setup' panel (Figure 6). The type can be selected from the combo box, while the string must be placed into the textbox.

On Figure 7 you can see a sample string for a MySQL database connection. Keep in mind that you can use your own connection string type (OleDB, ODBC, SQLServer, Oracle) for your own database server which can be Microsoft SQL, Oracle, Sybase etc... In case you are using an ODBC driver for MySQL, you just need to modify the MySQL server IP, database name, username and password to your own values in the following string:

Connection Type: ODBC
Connection String: Driver={MySQL ODBC 5.3 Unicode Driver}; Server=127.0.0.1;Database=ozekisms;User=ozeki;Password=abc123;Option=4;

Figure 7 - Sample connection string for an ODBC connection to a MySQL database

To find the connection string to other database servers, you are suggested to read the SMS Database Connection Strings guide.

Step 4 - Write script to autoreply from SQL database

Now it is time to write the script file which is capable to SELECT a row from your database table if the table was successfully created in STEP 2. The message attribute of the selected row will be replied to the SMS message. Here you can find the path of the example script: C:\Program Files\Ozeki\Ozeki10\Data\NG\config\OzekiUsername\sqlscript.txt

A simple text editor can edit these files or you can create your own txt file and edit it with your own application, which is useful if you would like to modify the queries. Please provide the path to the file using the Autoreply Database User by clicking 'Database setup' and selecting the 'Autoreply script' tab page (Figure 8)

Step 5 - The structure of SQL query scripts

The Autoreply Database User's SQL script contains filter-statement pairs, which works exactly like the if-then structure in programming languages. The received SMS message goes through the filter and if it is ok, an SQL SELECT statement will run. The message content of the selected rows will be forwarded to a phone number. This number is the 1st parameter of the SELECT statement's result set, while the message text is the 2nd. The filter is capable to identify phone numbers, text fragments and keywords. The keyword of each message is the first word of the text. Forwarding happens if the address of the recipient is included in the database (Figure 11).

Step 6 - A simple keyword example

This example will run on the following table which you can see in Figure 9.

id          keyword                        msg
----------  ------------------------------ -------------------------------------
 1          default                        There is no data for this keyword.
 2          red                            Red is a good colour.
 3          blue                           Blue is not the best option.

Figure 9 - The content of 'autoreplymessage' table

You can run the following script in Figure 10 on the 'autoreplymessage' table by adding it to 'sqlscript.txt'. As a reminder the keyword, which is symbolized by the 'k' character, is the first word in the SMS message. This word will be tested by the filter. If a filter check is successful, the SELECT statement runs and the script will stop. As you can see, you can place constant values in the SELECT statement. For example, '$sender', which is the sender of the incoming message.

Script elements you can place into sqlscript.txt

SELECT '$sender',msg FROM autoreplymessage WHERE keyword='red'
#The first filter checks if word 'RED' was the keyword.
#If true, it SELECT-s 'Red is a good colour.' as a reply message.
#If false it goes to the next filter.

k^BLUE
SELECT '$sender',msg FROM autoreplymessage WHERE keyword='blue'
#The second filter checks if word 'BLUE' was the keyword.
#If true, it SELECT-s 'Blue is not the best option.' as a reply message.
#If false it goes to the next filter.

k.*
SELECT '$sender',msg FROM autoreplymessage WHERE keyword='default'
#The third filter will accept any other keyword.
#It SELECT-s 'There is no data for this keyword.' as a reply message.

Figure 10 - Example script which can be placed into sqlscript.txt

The filter part of the SQL query script works similarly as the filter part of the SMS messaging script, but keep in mind only to search filter examples, since the second part is always an SQL SELECT statement.

Step 7 - Pre-defined keywords in Ozeki SMS Gateway

These types of keywords can be placed in the script. For example '$sender' :

SELECT '$sender',msg FROM autoreplymessage WHERE keyword='red'

Figure 11 - Sends msg to '$sender'

Autoreply Database User can recognize the following keywords:

Keyword	Value
$originator	Replaced by the telephone number of the sender.
$sender	=$originator (The $sender is another name for the $originator.)
$recipient	Replaced by the telephone number that received the message.
$receiver	=$recipient (The $receiver is another name for the $recipient.)
$messagedata	Replaced by the text of the message.
$keyword	Replaced by the 1st word in the message. This word is called keyword.
$after	Replaced by the remaining text after the keyword.
$senttime	Replaced by a timestamp which shows when the message was sent.
$receivedtime	Replaced by a timestamp which shows when the message was received.
$messagetype	Replaced by the type of the message, which is 'SMS:TEXT' in most cases.
$id	Replaced by a unique string, which identifies the message in Ozeki SMS Gateway.
$operator	Replaced the name of the service provider, which received the message in Ozeki SMS Gateway.
$1	Replaced by the 1st word in the message.
$2	Replaced by the 2nd word in the message.
$3	Replaced by the 3rd word in the message, etc...

The autoreply script's structure is explained on these webpages:

• The simple structure of a script file is defined on this webpage.
• 'sqlscript.txt' example

Step 8 - Test autoreply from database solution

After doing Step 1 to 6, you can try the solution. Ozeki SMS Gateway will forward incoming SMS messages to your Autoreply Database User. The SQL query script filters the SMS message to create the proper SELECT result sets. Each row of the result set will represent a message to be replied or forwarded.

You can track the messaging log of your Autoreply Database User at the following path:
C:\Program Files\Ozeki\Ozeki10\Data\NG\Logs

When I insert Data into Ozekimessageout table I didn't get message to my cell phone. What would be cause that I didn't get the text message that it is inserted into out table using my SQL console?

First, let's rule out a mobile network issue. Send a test SMS directly from Ozeki's graphical user interface (GUI). If the message arrives successfully, the network connection seems to be functioning properly.

Next, we need to verify if messages are being transferred from your database to Ozeki software. Insert some test messages into the Ozekimessageout table. Then, within Ozeki's GUI, navigate to the outbox associated with the database user you're using. If these test messages are absent from the outbox, the problem lies in the data transfer process between the database and Ozeki.

I would like to use one database for many "database users". Is this possible?

Absolutely! Ozeki SMS Gateway allows you to maintain distinct message logs for different purposes by using separate database users.

Here's a step-by-step guide:

• Create New Database Tables: Set up two new tables within your database. For easy identification, you can name them ozekimessagein2 and ozekimessageout2. These tables should mirror the structure of the existing ozekimessagein and ozekimessageout tables used by your first database user.
• Configure the Second Database User: Access the configuration form for your second database user in Ozeki.
• Modify SQL Templates: Locate the section for SQL templates within the configuration form. These templates specify how messages are saved to the database tables.
• Update Table Names: Within the SQL templates for the second database user, update the existing table names (ozekimessagein and ozekimessageout) to match the newly created tables (ozekimessagein2 and ozekimessageout2).

By following these steps, you'll establish separate message logs for each database user. This allows you to categorize or filter messages based on your specific needs using the chosen table names.

My Ozeki SMS Gateway is always disconnecting from my MySQL server with the following error message: MySQL Server has gone away. What should I do?

To enhance responsiveness when using Ozeki with MySQL, adjust the timeout setting. Here's how:

Configure MySQL Timeout:

• Edit your MySQL configuration file (e.g., my.ini) and set the interactive_timeout parameter to your desired duration in seconds (e.g., interactive_timeout = 28800 for 8 hours).
• Save the changes and restart the MySQL service.

Update Ozeki Connection String:

• Modify your Ozeki connection string to use Interactive instead of wait_timeout. The correct format is Driver={MySQL ODBC 5.2 ANSI Driver};Server=127.0.0.1;Database=ozeki;User=test;Password=test;Interactive=YOUR_TIMEOUT_VALUE.
• Remember to replace YOUR_TIMEOUT_VALUE with your desired timeout in seconds.

By following these steps, you'll optimize MySQL connection timeouts for smoother operation within Ozeki, especially beneficial for long-running tasks or large datasets.

Driver={MySQL ODBC 5.2 ANSI Driver};Server=127.0.0.1;Database=ozeki;User=test;Password=test;interactive=;Option=8;

More information

• SMS reply from pc to mobile
• How to write an SMS reply script
• How to send an SMS reply from SQL