---

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 stucture
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 color."), 
		("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 color.
 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 combobox, 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' tabpage (Figure 8)

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

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 color.
 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 color.' 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.

Pre-defined keywords in Ozeki SMS Gateway

These type 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 7 - 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

More information

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

 

---