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.
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:
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.
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:
How to install a Database User
Ozeki SMS Gatway has a
so you can easily install and configure a Database User.
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).
You will find yourself on a new page which consists of two panels.
Add a 'Database' user from the right side panel
('Add user or application').
A page will open up asking you to provide an unique username for your Database User (Figure 4).
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).
Later on any variable can be modified on the connection string template.
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:
The MSSQL Server has the following connections string:
The Sybase / SQL Anywhere Server has the following connections string:
The Postgre SQL Server has the following connections string:
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.
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.
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).
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.
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).
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.
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:
If you like the default logging configurations, leave the text boxes unchanged (Figure 10).
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).
Click 'Configure' to modify the configuration of the user.