Products
Ozeki 10
SMS
Ozeki NG SMS Gateway
SMS Gateway
Quick Start Guide
SMS with Client Software
HTTP API
SMS from/to Database
PHP SMS API
Solutions
Autoreply SMS
Autoreply Easy
Autoreply by Script
Autoreply from Database
Installation guide
Create database layout
Sample script file
Script structure
Built-in SMTP server
SMS from C# API
SMS through Email
SMS from/to File
SMS from/to FTP server
Application starter SMS
C# SMS API
VB.NET SMS API
SQL Logging
System Database
SMPP
Message Server 6
SMS Wall
IP SMS Gateway
Android SMS
Excel SMS
Bulk Messenger
Cluster
Developer tools
Robots
Hardware
Technology
Company


Ozeki 10 SMS Gateway
 Autoreply by Script Autoreply by Script | Installation guide Installation guide

Autoreply by selecting messages from 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.

Start the video

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.

SMS Database query
Figure 1 - How does Ozeki SMS Gateway's Autoreply Database User work

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.

Adding user or application in Ozeki SMS Gateway
Figure 2 - Click 'Add' user or application on the 'Management' console

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

Autreply Database User for SMS information queries
Figure 3 - Install Autoreply Database User from the 'Management' console

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.


Figure 6 - Setup Autoreply Database User for SMS information query

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


Figure 8 - Provide txt script path to Autoreply Database User

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
k^RED
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' :

k^RED
SELECT '$sender',msg FROM autoreplymessage WHERE keyword='red'
Figure 11 - Sends msg to '$sender'

Autoreply Database User can recognize the following keywords:
KeywordValue
$originatorReplaced by the telephone number of the sender.
$sender=$originator (The $sender is another name for the $originator.)
$recipientReplaced by the telephone number that received the message.
$receiver=$recipient (The $receiver is another name for the $recipient.)
$messagedataReplaced by the text of the message.
$keywordReplaced by the 1st word in the message. This word is called keyword.
$afterReplaced by the remaining text after the keyword.
$senttimeReplaced by a timestamp which shows when the message was sent.
$receivedtimeReplaced by a timestamp which shows when the message was received.
$messagetypeReplaced by the type of the message, which is 'SMS:TEXT' in most cases.
$idReplaced by a unique string,
which identifies the message in Ozeki SMS Gateway.
$operatorReplaced the name of the service provider,
which received the message in Ozeki SMS Gateway.
$1Replaced by the 1st word in the message.
$2Replaced by the 2nd word in the message.
$3Replaced by the 3rd word in the message, etc...

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

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


Copyright © 2000- - Ozeki Ltd | info@ozeki.hu
Home > Products > SMS > SMS Gateway > Solutions > Autoreply SMS > Autoreply from Database
Legal | Privacy policy | Terms of use
Page: 5750 | Login | 3.229.118.253