- Product Manual
- Introduction
- Installation Guide
- Prerequisites
- SMS technology
- Download Information
- Installation Steps
- GSM Modem Setup
- Mobile Network Connections
- Users and applications
- Standard user
- SMPP user
- SMS from or to database
- Autoreply SMS from database
- Installation guide
- Create database layout
- Sample script file
- Script file structure
- Autoreply SMS using a script
- Simple autoreply SMS
- SMS from or to file
- SMS through E-mail
- SMS via HTTP Request
- SMS via colour protocol
- Application starter SMS
- SMS Voting
- SMS from MS Outlook
- SMS from/to FTP server
- PDF Request in SMS
- SMS to WCF service
- SMS Forwarder
- RSS to SMS
- Linux
- User Guide
- Developers Guide
- Examples and Solutions
- Appendix
- SMS FAQ
- Feature list
- Commercial Information
- Search
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 Autoreply from Database
This guide gives you information on how to create an autoresponding application that uses information stored in your database. The solution presented on this webpage can be used with any database server (Microsoft SQL, Oracle, MySQL, Sybase, etc...). This solution is ideal to create the following service (Figure 1):
- A mobile user sends in a keyword in an SMS message to the system.
- The system looks up the response SMS message from the database based on this keyword.
- The system sends the response SMS message back to the mobile user.
I assume you already have a working service provider connection configured, that is capable of sending and receiving messages (e.g. you have a GSM modem attached to your server computer and your GSM modem is configured in Ozeki NG).
Step 1 - Setup the Autoreply Database User
In order to create the explained service first you need to install an Autoreply Database User (Figure 2). Detailed information about how this user can be installed is available in the "Autoreply Database User installation guide".
Step 2 - Create the database layout
The next step is to create your database table, that stores the response SMS messages (Figure 3). In our example we use Microsoft SQL Express as the database server. The following guide gives you information on how you can create a database layout using SQL Express: Create database layout for SMS autoresponses. In our example we use the following table. (Of course you can change the table layout according to your needs and you can use multiple database tables.)
> 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) |
Step 3 - Configure the Autoreply Database User to use your database
When your database is ready, you need to enter the database connection information (connection type and connection string) into the database settings configuration form of the Autoreply Database user. This configuration form can be opened by clicking on the "Database setup" menu item (Figure 4).
On this form you can select the database connection type and you can enter the connection string. In our example (for SQL Express) the connection information that should be used is:
Connection type: OleDb Connection string: Provider=SQLNCLI;Server=.\SQLEXPRESS;User ID=ozekiuser;password=ozekipass; Database=ozeki;Persist Security Info=True |
For other database servers, such as Oracle, MySQL, etc..., you can find information about how to format the connection string in the Database Connection Strings guide.
Step 4 - Write the autoresponding command script
The final step to make SMS information query possible is to write the script file. The script file is a plain text file that controls which SQL command should be executed when an incoming SMS arrives. You can edit the scriptfile with notepad or any other text editor. In our example the location of the script file is:
C:\Program Files\Ozeki\OzekiNG - SMS Gateway\config\TestApp\sqlscript.txt
The contents of the script file in our example has three sections (Figure 5). The first two sections are triggered for a response SMS if the keyword "RED" or "BLUE" is received in an SMS message. The final section creates the default response message if something else was received.
C:\Program Files\Ozeki\OzekiNG - SMS Gateway\config\TestApp\sqlscript.txt |
k^RED SELECT '$sender',msg from autoreplymessage where keyword='red' k^BLUE SELECT '$sender',msg from autoreplymessage where keyword='blue' k.* SELECT '$sender',msg from autoreplymessage where keyword='default' |
Autoreply Database user can recognize the following keywords:
Keyword | Value | $originator | It is replaced to the sender telephone number of the messages |
---|---|
$sender | It is replaced to the sender telephone number of the messages (an alias for originator) |
$recipient | It is replaced to the telephone number that received the message |
$receiver | It is replaced to the telephone number that received the messages (an alias for recipient) |
$messagedata | It is replaced to the message text |
$keyword | It is replaced to the keyword in the message |
$after | It is replaced to the message the message text after the keyword in the message |
$senttime | It is replaced to the timestamp, that represents when the message was sent |
$receivedtime | It is replaced to the timestamp, that represents when the message was received |
$messagetype | It is replaced to the message type (in most cases this will be SMS:TEXT) |
$id | It is replaced to the unique string identifier of the message |
$operator | It is replaced to the name of the service provider connection that received the message |
$1 | The first word in the message. You can use $2 to refer to the second word, $3 to refer to the third word, etc... |
The structure of the autoreply script is explained in the following documents:
Script file structure for SMS information query from database
Sample script file for SMS information query from database.
Step 5 - Testing the solution
After this is done, you can test the solution by sending an SMS message to your SMS gateway. The SMS gateway will forward the message to the Autoreply Database User, where the command script will be executed for the response. The response will be returned by an SQL SELECT query defined in the script file. After the appropriate SQL SELECT is executed the response messages will be sent back to you in SMS. To track what is going on, read the logs in the following directory:
C:\Program Files\Ozeki\OzekiNG - SMS Gateway\LogsMore information