OZEKI
NG SMS Gateway
"The World's most reliable SMS gateway software!"
info@ozekisms.com
Tel.: 00 36 52 532 731
Overview Quick start Download Manual How to buy FAQ Contact Us
OZEKI NG SMS Gateway - Product Guide

Database user Contents | SearchInstallation guide

Home > Product Manual > Installation Guide > Users and applications > AutoreplyDatabase user

SMS Gateway SMS Gateway Home

  Product Manual
  Introduction
  SMS technology
  Installation Guide
  Prerequisites
  Download Information
  Installation Steps
  Service Provider Connections
  Users and applications
  Standard user
  SMPP user
  Database user
  AutoreplyDatabase user
  Installation guide
  Create database layout
  Sample script file
  Script file structure
  Autoreply user
  AutoReplyEasy user
  File user
  E-mail user
  HTTP Client user
  Colour SMS user
  Appstarter user
  SMS Voting
  Outlook Mobile Service
  FTP user
  PDF user
  WCF Client User
  Forwarder user
  Ozeki NG Autoreply
  Linux
  User Guide
  Developers Guide
  Examples and Solutions
  Appendix
  FAQ
  Feature list
  Commercial Information
  Search
 


Automated phone calls?
Ozeki Phone System XE VoIP PBX software is an advanced PBX built for automated voice calls and 2 way SMS messaging. It has outstanding APIs for software developers. It can be used for:

Phone calls from SQL
Phone calls from HTTP
Voice and SMS applications

Callcenter developers
If you are working on telephone solutions, please check out the Ozeki VoIP SIP SDK.
It can be used to create VoIP client and server software.

Contact Us!
If you wish to get further information, do not hesitate to contact us!

E-mail: info@ozekisms.com

If you have a technical question, please submit a support request on-line.

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

1.) A mobile user sends in a keyword in an SMS message to the system.
2.) The system looks up the response SMS message from the database based on this keyword.
3.) 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).

SMS Database query
Figure 1 - System architecture


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

Autreply Database User for SMS information queries
Figure 2 - Install the Autoreply Database User for SMS information queries


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)

Figure 3 - Database table layout for SMS information query

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


Figure 4 - Database settings for SMS information query

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'

Figure 5 - example script file

Autoreply Database user can recognize the following keywords:

KeywordValue
$originatorIt 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)
$recipientIt is replaced to the telephone number that received the message
$receiverIt is replaced to the telephone number that received the messages (an alias for recipient)
$messagedataIt is replaced to the message text
$keywordIt is replaced to the keyword in the message
$afterIt is replaced to the message the message text after the keyword in the message
$senttimeIt is replaced to the timestamp, that represents when the message was sent
$receivedtimeIt is replaced to the timestamp, that represents when the message was received
$messagetypeIt is replaced to the message type (in most cases this will be SMS:TEXT)
$idIt is replaced to the unique string identifier of the message
$operatorIt is replaced to the name of the service provider connection that received the message
$1The 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\Logs

Dig deeper!
People who read this also read...






Next page: Installation guide
Copyright © 2000 - 2014 Ozeki Informatics Ltd.
All rights reserved

Software PBX for Windows | VoIP SDK   |   Legal information   |   Privacy policy   |   Terms of use
Please, address your inquiries to info@ozekisms.com