How to send and receive SMS messages from a database application
Recommended reading:
If you wish to protect your service against hardware failure and reach higher
availability of your Ozeki NG SMS Gateway software, we recommend you to try
our Ozeki Cluster software product.
Ozeki Cluster automatically moves any
service to another computer in case of a hardware failure. You can
download it from:
Ozeki Cluster Site
This guide explains how you can setup Ozeki NG SMS Gateway
to communicate with your database server. If the steps in this guide are followed,
you will be able to send (or receive) SMS messages from a database application.
All you have to do is insert data (or read data) from the appropriate database
tables.
This guide gives you the simple steps for the configuration.
Introduction
Ozeki NG - SMS Gateway can be used to send and receive SMS
messages using a database server with the help of SQL queries. This is possible
because database
servers allow the sharing of database tables between two applications.
Database table layouts:
Microsoft SQL Express
( Video tutorial)
Microsoft SQL Server
Oracle
( Video tutorial)
MySQL
( Video tutorial)
Access
PostgreSQL
Sybase/SQL Anywhere
Informix
Other database related information:
Automatic information
retrieval from a database using SMS messages
To be able to send SMS messages from a database application you
need to create a database table called
ozekimessageout. You can put an
outgoing message into this database table using an SQL INSERT command. Ozeki
NG - SMS Gateway will periodical check (poll) the ozekimessagout table with
an SQL SELECT command, and if it finds a new entry, it will send the SMS
message.
You can receive SMS messages in a similar way. You need to setup a database
table called ozekimessagein. If
an SMS message is received from a mobile phone, it is inserted by Ozeki NG
SMS gateway into the ozekimessagein database table (Figure 1). You can use
an SQL SELECT to read this message.
Figure 1 - SMS messaging using a database server
In order to get this configuration working, you need to have
a database server
(such as Oracle, Access, MySql, MS SQL, Postgres, Sybase, etc.) installed,
and you need to create two database tables: ozekimessageout and ozekimessagein.
The ozekimessageout table is used for sending, while ozekimessagein is used for
receiving SMS messages. To find out how to create the ozekimessagein
and the ozekimessageout table for different database servers,
read the respective pages related to the different database users.
Check out the links at the end of this introduction.
Once you have created the database structure, you need to
connect Ozeki NG SMS Gateway to your database. To establish a connection
you can use an ODBC connection or an OLEDb connection. (To setup the connection
you might have to install and configure an OleDB or an ODBC driver. For example
MySQL requires the MyODBC Database
driver to be installed.
For Microsoft SQL Express, Microsoft SQL Server and Oracle, Microsoft Windows
has a built in OleDb drier, so you do not have to install a driver separately).
The connection can be configured with a database connection string. This
connection string includes the database driver name, the IP address, the
username and the password
and the name of the database. More information about the database connection
string can be found in our "Database
connection strings" manual.
If you have figured out the connection string for your
database server,
you need to install and configure a database user in Ozeki NG - SMS Gateway.
This is a virtual user that will communicate with a specified database server
and will provide you the SQL SMS Gateway functionality.
For more information about different databases,
check out any of the following links:
How to install a database user
You can install and configure a database user with the
graphical
user interface of Ozeki NG - SMS Gateway. Follow the instructions below.
To start installing and configuring a database user, click
the Add button
in the top right-hand corner of the Users and Applications panel on the
right of the Management Console. (To open the Management Console
interface, click the Management Console tab. For more information,
check out the User
Interface page.) You can also add a user by clicking the Add user
item in the Users and applications menu. (Figure 2).
Figure 2 - Adding a user
This will bring up an interface consisting of two panels.
- The panel on the left contains a list of users that have been installed.
In the bar at the bottom of the page you can read how many users have been
installed.
- The panel on the right contains a list of users that can be installed.
- Each user in the left-hand panel is listed with a unique name
(and the user type stated in brackets).
- Each user in the right-hand panel is listed with the name of
its type and a brief description of what it can be used for.
To add a database user, click the Install link
in the respective entry in the list of installable users and applications
(Figure 3).
Figure 3 - Installing a database user
This will open a panel asking you to give the database
user a unique name (Figure 4).
Figure 4 - Naming the database user
Enter a unique name in the User name edit box,
and click OK. Then the installation is finished, and the name of
the database user will show up in the list of users on the left in the panel.
How to configure a database user
Once you have installed a database user,
you can start to configure it. After you have provided a unique name for
the database user and clicked OK (see Figure 4 above), the
Configuration panel will open.
The first tab that will show up is the Database
connection tab (Figure 5).
- In the Connection section, select the connection string type in the
Connection string type dropdown menu.
Make sure that the connection you specify is accessible
for the system user account.
- For Open Database Connectivity, select Odbc.
- For Object Linking and Embedding Database, select OleDb.
Figure 5 - The Database connection tab
In the Connection string template, modify any variables
if necessary. For instance, as you have probably changed the username and
password, you will need to modify them in the template.
If Ozeki NG - SMS Gateway and the database reside on the same server,
"localhost" can be used to describe the database location. If they do not
reside on the same server, you need to specify the database location by
replacing "localhost" with the IP address.
Different database servers require different connection strings.
By default, this template contains the connection string of the MySQL database
server. For information about connection strings of other database servers,
check out Appendix B - Connection
Strings.
The connection string for the Oracle SQL server is:
Provider=MSDAORA.1;Password=YourPassword;User ID=YourUserID;Persist
Security Info=True
The connection string for the MS SQL server is:
Provider=SQLNCLI;Server=YourServer;Database=YourDatabase;UID=YourUsername;
PWD=YourPassword;
The connection string for the Sybase / SQL Anywhere Server is:
Provider=ASAProv.90;UID=MyUsername;PWD=MyPassword;DatabaseFile=DatabaseFileLocationOnYourComputer.db;
EngineName=Yourdbenginename;Start=YourDatabaseBinaryLocation.exe -c 32M;AutoStop=Yes
The connection string for the Postgre SQL server is:
DRIVER={PostgreSQL};SERVER=YourServerIP;port=5432;DATABASE=
YourDatabaseName;UID=YourUsername;PWD=YourPassword;
Additional connection strings can be found in
Appendix B - Connection Strings.
In the Date format section, specify - in the Date
format string edit
box - the date format string
used to create the date value for the SQL statements.
The default date format string is yyyy-MM-dd HH:mm:ss.
In the date format string, yyyy stands for the year as a
four-digit number. If the year has more than four digits, only the four
low-order digits appear in the result. If the year has fewer than four
digits, the number is padded with leading zeroes to achieve four digits.
(Note that for the Thai Buddhist calendar, which can have
five-digit years, this format specifier renders all five digits.)
MM stands for the month as a number from 01 through 12.
A single-digit month is formatted with a leading zero.
The dd string stands for the day of the month as a number
from 01 through 31. A single-digit day is formatted with a leading zero.
HH stands for the hour as a number from 00 through 23, that is,
the hour as represented by a zero-based 24-hour clock that counts the hours
since midnight. A single-digit hour is formatted with a leading zero.
The mm string stands represents the minute
as a number from 00 through 59. The minute represents whole minutes
passed since the last hour. A single-digit minute is formatted with
a leading zero.
The ss string stands for the seconds as a number from 00 through 59.
The second represents whole seconds passed since the last minute.
A single-digit second is formatted with a leading zero.
If you are content with this date format, leave the default date format
string unchanged. If you wish to change it, rearrange the format specifiers.
For example, you can put dd-MM-yyyy HH:mm:ss instead of the default
arrangement. For information, about the different date format strings you
can use, check out the Date Format
Strings page.
When you have finished configuring the database connection, click OK.
To send SMS messages using this connection, you need to make
settings in the SQL for sending tab. Click the SQL for sending tab (Figure 6).
Figure 6 - The SQL for sending tab
In the upper section of the panel you have opened, you can
see a checkbox. By default, it is checked, and it should be checked if you
use this database connection for sending outgoing messages. (To stop using
this connection for sending outgoing messages, uncheck the checkbox.)
Below this checkbox you can specify how often Ozeki NG - SMS
Gateway
should check the ozekimessageout table for outgoing messages. Specify the
frequency in seconds by entering a positive whole number in numerical form.
If you are content with the default value (10), leave this edit box
unchanged.
In the lower section of this panel you can specify the maximum number of messages
to be sent out with one poll. By default it is 10. If you are content with this
default value, leave the edit box unchanged (Figure 7).
If Maximum number of messages to send with one poll is 10, it means that
10 messages
will be sent out with 1 poll. If there are more than 10 messages to be sent, then
all the messages will be polled. If there is no messages left, the system
will stop for 10 seconds (since it is the default value for checking outgoing
messages).
For example, if there are 100 messages, there will be 11 polls.
(11 polls: 10 messages are sent with 1 poll. Then 100 messages will be
sent with 10 polls. Plus there will be 1 last poll for checking for outgoing
messages.) In this way, if there are 99 messages there will be 10 polls, etc.
In other words it is ensured that messages will be sent out even if the default
number of messages to be sent with one poll is set to 10 but there are more than
10 messages waiting for sending.
Figure 7 - Specifying the frequency of queries and the SQL statements
In the lower section, you can specify SQL statements
related to the polling and the status of outgoing messages.
Modify any variables in the templates, or if you are content with them,
leave them unchanged.
The statement in the Polling template describes the
way of selecting outgoing messages.
- The statement in the Sending template is used if the polling has
been successful.
- The statement in the Sent template is used if the message has been
accepted by the service provider.
- The statement in the Not sent template is used if the message has
not been sent.
- The statement in the Delivered template is used if the message has
been delivered to the recipient phone number.
- The statement in the Undelivered template is used if the message
has not been delivered to the recipient phone number.
For detailed description of SQL statements please go to
Detailed description of SQL statements page.
To receive SMS messages using this connection, you need to
make settings in theSQL for receiving tab.Click the SQL for receiving tab (Figure 8).
Figure 8 - The SQL for receiving tab
In the upper section of the panel you have opened, you
can see a checkbox. By default, it is checked, and it should be checked
if you use this database connection for receiving incoming messages. (To
stop using this connection for receiving incoming messages,
uncheck the checkbox.)
In the lower section of the panel, you can see a template
containing an SQL INSERT statement used for inserting incoming messages
into the database.If necessary, modify any valuables in the statement. If not, leave it
unchanged.
Below the template you can find three pairs of edit boxes
that allow you to make the program prepare the message prior to insert. This
may be useful to handle messages containing special characters.
Each pair consists of one edit box (on the left) for the character to
be replaced, and one (on the right) for the characters that will replace
the character on the left.
By default, the boxes contain the most problematic special characters
to be replaced and those to be used instead. Modify these settings if you
wish to specify different characters.
You can also configure the logging related to the database user.
Click the Logging tab to open the panel for logging settings.
In the Logging section of the panel, you can find three checkboxes.
You can choose to check any or all (or none) of them. Your choice depends on
whether
you want the program to log sent and received messages in human readable format,
to log SQL SELECT statements and/or to log SQL UPDATE statements (Figure 9).
Figure 9 - The Logging tab
In the Log file settings section of the tab, you can
make specifications for the size and the number of available logfiles.
Log rotation saves disk space, as it ensures that older (and probably no
longer needed) logfiles will be automatically deleted from the log directory,
which is specified in the
Log directory text box. By default, the access path to the logfiles is:
C:\Program Files\Ozeki\OzekiNG - SMS Gateway\Logs
In the Maximum log file size text box you can specify
the maximum size of a logfile. Once this size is reached, a new logfile will
be created. Specify the number of kilobytes for the size of a logfile by
entering a positive whole number. By default, the maximum
logfile size is 8000 kB.
In the Number of rotated files text box, specify
the maximum number of rotated logfiles that are saved. Specify this number
by entering a positive whole number. By default, the number of logfiles
that are saved is 4.
If you are content with the default specifications, leave
the text boxes unchanged (Figure 10).
Figure 10 - Log file settings
To modify the configuration or to view server events related to
a database user, open the panel of the user's account. To do this, click the name
of the user in the Users and Applications panel of the Management
Console. In the panel of the user's account, click the link of
the task you wish to perform (Figure 11).
Figure 11 - Clicking a user-related task link
To configure or modify the configuration of a user, click Configure.
To view the latest server events related to a user, click Events.
To uninstall the user, click the Uninstall link.
More information
Next page:
MSSQL
| 
