- Quick start guide
- System requirements
- Features
- Technology
- Installation guide
- Mobile networks
- SMS from Apps and Office
- SMS routing
- SMS Messenger
- Standard user
- Autoreply SMS
- Autoreply Easy
- Autoreply by Script
- Autoreply from Database
- Installation guide
- Create database layout
- Sample script file
- Script structure
- SMS from Excel
- SMS from Txt
- SMS from FTP
- Application starter
- Email to SMS
- SMS to Email
- SMS modification
- SMS priorities
- High volume sms
- HLR lookups
- Developers guide
- Service providers
- Administrators guide
- Appendix
- Training
Ozeki brings you outstanding
SMS Gateway technology. Use our SMS Server products on Windows,Linux, or Android
C# SMS API
Developers can use our C# SMS API to send SMS from C#.Net. The C# SMS API comes with full source code
PHP SMS API
The ozeki PHP SMS gateway software can be used to send SMS from PHP and to receive SMS usig PHP on your website
SMPP SMS Gateway
SMS service providers use our SMPP gateway solution, that offers a high performance SMPP server and SMPP client gateway with amazing routing capabilities
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:
- Launch Ozeki SMS Gateway
- Add new Autoreply database user
- Create SQL database table stucture
- Setup Autoreply database user
- Provide the autoreply text script
- Use identifiable numbers and keywords
- Send test SMS to get reply from SQL
- 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;
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.txtSELECT '$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:
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