How to setup an SQL to Chat Server

In this guide provides you information about how you can send and receive chat messages from an SQL database server with the help of Ozeki Chat Server. This document divided into 3 main segments: First the supported database servers are listed. Second, the procedure of sending chat message from a database server is demonstrated using MySQL. Third, an autoresponding chat message service is created with the help of a database trigger. To complete this guide successfully, you need to set up a database server as a prerequisite and you must have some basic understanding of SQL. The completion of the steps in this guide takes about 10 to 15 minutes.

how to use a database server to build chat message system
Figure 1 - Using a database server to build chat message system

Database servers supported by Ozeki Chat Server

microsoft sql express
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
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
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
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
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
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

Step 1 - Setup a database connection

The first thing you need to do is is to setup a database connection in Ozeki SMS Gateway. To do this, first, open the SMS Gateway, and select the Apps icon from the toolbar. Next, from the list of applications (Figure 2), you have to select SQL messaging by clicking on the Install button.

how to create a sql messaging application
Figure 2 - Create a SQL messaging application

After you selected SQL messaging, you can see all available options that you can create in SMS Gateway. By following this guide, you will be able to see how you can create an SQL connection with a MySQL database, so to follow that guide, just click on the Install button of the MySQL option as you can see it in Figure 3.

how to install mysql database connection
Figure 3 - Install MySQL database connection

The last step of the creation of the database application is to connect it to your database server by filling the fields of the Connection Settings (Figure 4). Here you have to give all details about the database that you want to connect to. The first two fields are about to identify the database server. If you left them to default when setting up the database server, you can leave the values here default as well. Then you have to type the name of the database that you want to connect to and your user ID with a password that you use within the database server. If you filled all fields, you can just click OK to create the database application.

how to configure database a application
Figure 4 - Configure the database application

Step 2 - Setup an SMPP connection

At this point, you have got a database application, but you also need a service provider connection to handle the SMS messages. In this guide, you will be able to create an SMPP client connection. For that, you just have to click on Connect on the toolbar, and select SMPP client (Figure 5) from the list of all available service provider connections.

how to install a smpp client connection
Figure 5 - Install a SMPP client connection

Before establishing the connection, you need to provide some details regarding the connection. First, you have to type a name for the connection, then you have to define the server that you want to connect to as Figure 6 demonstrates it. To finish the creation of the service provider connection, just click on OK.

how to configure the smpp client
Figure 6 - Configure the SMPP client

Step 3 - Send SMS from a database

how ozeki sends sms messages using a database
Figure 7 - Sending a SMS using the database server

With all connections set up properly in Ozeki SMS Gateway, now you can start managing SMS messages from a database. First, you will be able to see, how you can send an SMS message by using only one SQL command. By default, SMS Gateway uses ozekimessageout table to send messages. You do not have to worry if you have not got this table in your database, you can create this table easily. Just open your SQL application in SMS Gateway and as you can see it on Figure 8, you can select a SQL command to create the ozekimessageout table. So, just select it, and click on Execute to create the table.

how to create a database table in ozeki sms gateway
Figure 8 - Execute the SQL command to create a table

Sending a message can be done by simply just inserting the message into the ozekimessageout table. The SMS Gateway periodically checks this table and sends the newly added messages. To insert a message into this table, just select the corresponding INSERT command as you can see it in Figure 9. In the textbox, you can change the values which represent the receiver and the message itself. When you finished with writing the message, just click on Execute and your message is about to be sent.

how to insert a new outgoing message
Figure 9 - Insert the message into the database

To modify the setting of sending SMS messages, you only have to select the Configure tab in the menu of the database application, and here, select the Send tab. Here, you will be able to see the settings that define, how the outgoing messages are handled. As Figure 10 shows, here you can modify the SQL statement that queries and processes the outgoing messages. You can also set the maximum number of outgoing messages per query and the interval of polling.

sql connection configure menu
Figure 10 - Configure the settings of outgoing messages

Step 4 - Receive SMS from a database

how ozeki receives sms messages using a database
Figure 11 - Receiving messages using the database server

When you created the database application, not only the application was created, SMS Gateway created a routing rule as well, which defines that all incoming SMS messages will be copied into the database. This rule can be found in the list of routing rules as you can see it in Figure 12.

how ozeki routes messages to the database
Figure 12 - Routing rule created for the database application

The incoming messages from any SMS connection will be routed into the ozekimessagein table by default. You can create this table easily by opening your database application and in the SQL menu, select the SQL command from the list (Figure 13) which will create the ozekimessagein table in your database after executing that command.

how to create table for incoming messages in ozeki
Figure 13 - Create table for the incoming messages

After you created the ozekimessagein table, all received messages will be inserted into that table. To check these messages, you can start a query in the database application. So, open the application, and in the SQL tab, select the command that will query the incoming messages (Figure 14) and click on Execute. If the execution was successful, you will be able to see the incoming messages.

how to query all incoming messages in ozeki sms gateway
Figure 14 - Query all received messages

To modify the settings of receiving messages, you can just select Configure tab and here, click on Receive as Figure 15 demonstrates it. In this menu, you can set a SQL command, that inserts the incoming messages into a table. In the command, you can also set the values which will be inserted into the table.

how to modify sql receiving options in ozeki
Figure 15 - Modify the SQL command for the incoming messages

More information