Ozeki 10 SMS Gateway

HTTP API | MSSQL Express

SMS from/to Database

This is a helpful guide, so you can easily connect Ozeki SMS Gateway and a selected database server. Follow the steps in this guide to learn how to send or receive SMS messages from a database application. A widely used method can help you to achieve this by inserting or reading data from the tables. You can also find easy configuration steps in this tutorial.

Basic concept

Ozeki SMS Gateway can be used to send or receive SMS messages with the help of SQL queries, since the database servers allow the sharing of database tables between two applications.

Database servers supported by Ozeki SMS Gateway:

Microsoft SQL Express You can use an MSSQL Express database for SMS messaging. Follow these pages to send or receive SMS messages through Ozeki SMS Gateway's Database User. All you have to do is insert or read data rows from the appropriate SQL table by setting INSERT INTO and SELECT statements. See how to send SMS from an MSSQL Express database

Oracle The connection between Oracle Database and Ozeki SMS Gateway is a perfect solution for SMS messaging. Ozeki SMS Gateway's Database User makes sure that incoming SMS messages can be inserted while outgoing SMS messages can be selected from database tables. See how to send SMS from an Oracle SQL database

MySQL You can combine Ozeki SMS Gateway with a MySQL database to send, receive and store SMS messages. On these pages you can see how to install and configure the connection with the proper connection string and how to create the recommended database structure. See how to send SMS from a MySQL database

PostgreSQL This chapter gives you great opportunity to see how to send and receive SMS messages through pre-created PostgreSQL database tables. You just need to connect to them with a Database User of Ozeki SMS Gateway. Do not forget to provide the ODBC driver connection string for the user. See how to send SMS from a PostgreSQL database

SAP SQL Anywhere See how to send and receive SMS messages through an SAP SQL Anywhere server with the Database User of Ozeki SMS Gateway. Here you can find a connection string and short CREATE TABLE statements to get started. You can send messages by inserting new message records. See how to send SMS from a SAP SQL Anywhere database

Microsoft Access You can simply start to send and receive SMS messages through Ozeki SMS Gateway's Database User by using Microsoft Access database tables. All you have to do is insert or read data rows from the appropriate SQL table by configuring INSERT INTO and SELECT statements for the user. See how to send SMS from a Microsoft Access database

Other information related to databases

You can easily send SMS messages from any database by creating a table for the messages that needs to be sent. You can give this table a name, for example 'ozekimessageout'. Please insert the messages you wish to send by using SQL INSERT statement. Ozeki SMS Gateway frequently checks the table with a simple SQL SELECT statement. If a new record is found, it will be sent out as an SMS message.

SMS messages can be received in a similar way. First please create a table which can be called 'ozekimessagein'. This table will be used in situations when a message from a mobile phone is received (Figure 1). With an SQL SELECT statement the message record can be read.

Figure 1 - SMS messaging using a database server

Please install a database server to get this configuration working. You can use MSSQL Express, MSSQL Server, Oracle, MySQL, Access, PostgreSQL, Sybase, SQL Anywhere, Informix etc. After installing the database server you should create two tables for sending or receiving messages. You can name these tables 'ozekimessageout' and 'ozekimessagein'. To create these tables, please scroll up and select the database server you have installed.

After the structure of the database have been created, please connect Ozeki SMS Gateway to your database. An ODBC or OLE DB connection driver works in most cases. (If the driver is not integrated in the server's installer than you might have to manually install and configure the driver. As an example the MyODBC Database driver is needed for MySQL to work, but MSSQL Express, MSSQL Server and Oracle have an integrated OLE DB driver, so these servers do not require manual driver installation.)

Please use a database string to configure the connection. The string contains the name of the database driver, the name and IP address of the database server and the username, password pair. You can find out more from the 'Database connection strings' manual.

If you provided the required connection string, please install and configure a Database User in SMS Gateway, which is a virtual user for communication. It covers the functions of Ozeki SMS Gateway as well.

You can find more information on different Database Users if you check out these links:

Oracle User
MSSQL User
MySQL User
Sybase_SQLAnywhere User
PostgreSQL User

How to install a Database User

Ozeki SMS Gatway has a browser GUI, so you can easily install and configure a Database User.
This detailed guide should help you:

Please start installing and configuring a Database User by clicking the 'Add' button which you can find on the top right corner of the 'Users/Applications' panel which is on the right side panel. Exactly at the top right-hand corner of the browser GUI. The 2nd option is by clicking 'User and applications/Add user or application' from the top menu bar (Figure 2).

Figure 2 - Adding a user

You will find yourself on a new page which consists of two panels.
The left side panel contains a list of already installed users.
The users on this list must have unique names and the bracket after the name contains the type of the user.
On the bottom of this panel you can see how many users are installed.
The right side panel is where you can install new users or applications.
Here you can find every user and application type supported by Ozeki SMS Gateway
and a small description explaining each.

Add a 'Database' user from the right side panel ('Add user or application').
Click on the 'Install' button next to it (Figure 3).

Figure 3 - Installing a Database User

A page will open up asking you to provide an unique username for your Database User (Figure 4).

Figure 4 - Giving name to Database User

Please type a unique User name in the text box and press 'OK'.

Congratulation! You have finished installing the Database User. You can find the name of the database in the userlist, which you can see on the left side panel.

How to configure a Database User

Your Database User is created, now you can configure it. At the moment you provided the name of the user and clicked 'OK', as seen on figure 4 above, the configuration panel opens.

The 'Database connection' tab should be the first active tab (Figure 5).
Here you can select the connection string type from the dropdown menu. Please provide the connection string as well. You should make sure that this connection can be accessed through the system user's account.
If Open Database Connectivity is used, please choose ODBC.
If Object Linking and Embedding Database is used, please choose OLE DB.

Figure 5 - The Database connection tab

Later on any variable can be modified on the connection string template.
For example you can change the username or password.
If Ozeki SMS Gateway runs on the same machine as the database server, than the 'Server' parameter should be 'localhost'. But if Ozeki and the database server is running on different machines than you should replace 'localhost' with the database server's IP address.

Each database server type requires an individual connection string format. As default the text box contains the connection string of a MySQL database server. If you have a different server, please search it on Appendix B - Connection Strings, but you can find a few connection strings below.

The Oracle SQL Server has the following connections string:
Provider=MSDAORA.1;Password=YourPassword;User ID=YourUserID;Persist Security Info=True

The MSSQL Server has the following connections string:
Provider=SQLNCLI;Server=YourServer;Database=YourDatabase;UID=YourUsername; PWD=YourPassword;

The Sybase / SQL Anywhere Server has the following connections string:
Provider=ASAProv.90;UID=MyUsername;PWD=MyPassword;
DatabaseFile=DatabaseFileLocationOnYourComputer.db; EngineName=Yourdbenginename;
Start=YourDatabaseBinaryLocation.exe -c 32M;AutoStop=Yes

The Postgre SQL Server has the following connections string:
DRIVER={PostgreSQL};SERVER=YourServerIP;port=5432;DATABASE= YourDatabaseName;UID=YourUsername;PWD=YourPassword;

Here you can find additional connection strings: Appendix B - Connection Strings.

Below the Connection string you can find the 'Date format string' text box. Here you can set how the date and time should be represented on your database server.
The time is defaultly represented as yyyy-MM-dd HH:mm:ss.
yyyy is a 4 digit number and stands for the year. If it has more digits then 4, the low-order digits will count. If it has less then 4 digits than a few zeros will added to the left so it will finally have 4 digits (Keep in mind that the Thai Buddhist calendar has 5 digits, so the format specifier should render all 5 digits.)
MM shows the value of the month from 01 to 12. If you provide a single digit month, a leading zero will be added to it.
dd shows the day and works similarly as the month, although it can go from 01 to 31.
HH shows where the hour should be placed and it can go from 00 to 23. It works similarly to a 24-hour clock. For single digit value a leading 0 will be placed before it. For example 05.
mm represents the location of the minutes which can go from 00 to 59 and if it contains one character, than it will get a leading 0 before it. For example 07.
ss shows the place of the seconds which can go from 00 to 59. A single character value gets a leading 0.
Since the default date string format is yyyy-MM-dd HH:mm:ss, but if you have a different date format you can rearrange it. For example you can try dd-MM-yyyy HH:mm:ss If you need more information please check the Date Format Strings page.
If you have finished configuring your database, please click 'OK'.

To send SMS messages you should click the 'SQL for sending' tabpage on the database configuration page (Figure 6). This is where you can set SQL statements, which will search and update the outgoing message records.

Figure 6 - The SQL for sending tab

You can find a checkbox defaultly checked in the upper section of this tabpage. If you use this database connection for sending messages, you should leave this box checked.

In the next row you can select the delay between two message table checks. You should know that SMS Gateway checks the outgoing message table for available records. Please enter a positive number which represents the delay in seconds. You can leave it unchanged as well. The default value of this checkbox is 10 (Figure 7).

In the bottom of the 'SQL for sending' tabpage you can set the maximum number of messages to be sent out after a single polling. The default value is 10, which you can find in this text box (Figure 7). You can leave it unchanged if you wish. With the default settings a maximum of 10 messages should be polled for sending in every 10 seconds.

Figure 7 - Specifying the frequency of queries and the SQL statements

There is a large text box in the middle for SQL statements that polls the outgoing messages. You can find default statements in them. You can modify the parameters in them if it is necessary.

In the 'Polling' template you can find the basic statement that selects outgoing message.
The 'Sending' template is used in case the polling was successful.
The 'Sent' template is used at the moment the service provider accepts the message.
The 'Not sent' template is used in case the message has not been sent.
The 'Delivered' template is used in case the message has been delivered to the recepient's phone number.
The 'Undelivered' template is used in case the message has not been delivered to the recepient's phone number.

Please read 'Detailed description of SQL statements' to find out more.

To receive SMS messages you should click the 'SQL for receiving' tabpage (Figure 8).
This is where you can set the SQL statement that inserts new message records.

Figure 8 - The SQL for receiving tab

You can find a checkbox defaultly checked in the upper section of this tabpage. If you use this database connection to receive messages, you should leave this box checked.

There is a large text box in the middle. Here you can see a template that defaulty contains an SQL INSERT statement which is for inserting messages in the form of new SQL records into the database. If it is necessary you can modify the statement.

Lower you can add 3 pairs of characters to replace a message character with another. This helps you prepare messages before the main statement is processed. With these text boxes you can handle special characters in the messages.

Now the basic SQL messaging functions have been set.
In the 'Logging' tabpage you configure how logs are created. Here you can find some checkboxes to select the logged activites. You can check any of them (Figure 9).

Figure 9 - The Logging tab

The 'Log file settings' groupbox let's you select the directory, file size and the maximum number of rotated files. This rotation saves you disk space by deleting the oldest file after reaching the maximum number of files. Everytime a file grows bigger then the maximum file size, a new file will be created. The default maximum file size is 8000 kB while the default number of rotated files are 4. The files should be created to the following default path which you can change:
C:\Program Files\Ozeki\Ozeki10\Data\NG\Logs

If you like the default logging configurations, leave the text boxes unchanged (Figure 10).

Figure 10 - Log file settings

Later on you can always come back and modify the configurations of the Database User by selecting the user from the 'Users/Applications' right side panel on the 'Management' page of Ozeki SMS Gateway (Figure 11).

Figure 11 - Clicking a user-related task link

Click 'Configure' to modify the configuration of the user.
Click 'Events' to check the newest user related server events.
Click 'Uninstall' to delete the Database User.
Click 'SQL prompt' to try your own SQL statetements on the database.