Autoreply by selecting messages from SQL Database
Ozeki SMS Gateway's Autoreply Database User can connect to your Microsoft SQL, Oracle, MySQL, Sybase, etc... database with a connection string. It runs any SELECT query you provide, after receiving an SMS message. The result set of the query will be sent as outgoing messages. The queries and tables can also be modified from your own application.
You can create the following service by using this solution (Figure 1):
Step 1 - Setup Autoreply Database User in Ozeki SMS Gateway
First you need to install an Autoreply Database User to create the explained service. On the 'Management' console please click 'Add' as seen on Figure 2.
Find the Autoreply Database User and click the blue 'install' link next to it. As you can see it on Figure 3.
Step 2 - Create database table structure
The Autoreply Database User will SELECT the reply SMS messages from a database table. Create the following database structure to store the SMS messages (Figure 4). This example uses a MySQL database server, but you can use other databases like Microsoft SQL, Oracle, Sybase, etc... You use other tables and table layouts as well.
Your tables can be viewed with SELECT * FROM statements (Figure 5). Although this example contains only one table.
In case there is no database driver installed with the database server, you should install a driver, because you will need the connection string in the next step. The database driver connects Ozeki SMS Gateway and the database server. In most cases there is a database driver installed with the database server.
Step 3 - Configure Autoreply Database User to use your database
You will need to enter the database connection type and connection string in the Autoreply Database User's 'Database setup' panel (Figure 6). The type can be selected from the combobox, while the string must be placed into the textbox.
On Figure 7 you can see a sample string for a MySQL database connection. Keep in mind that you can use your own connection string type (OleDB, ODBC, SQLServer, Oracle) for your own database server which can be Microsoft SQL, Oracle, Sybase etc... In case you are using an ODBC driver for MySQL, you just need to modify the MySQL server IP, database name, username and password to your own values in the following string:
To find the connection string to other database servers, you are suggested to read the Database Connection Strings guide.
Step 4 - Write script to autoreply from SQL database
Now it is time to write the script file which is capable to SELECT a row from your database table if the table was successfully created in STEP 2. The message attribute of the selected row will be replied to the SMS message. Here you can find the path of the example script: C:\Program Files\Ozeki\Ozeki10\Data\NG\config\OzekiUsername\sqlscript.txt
A simple text editor can edit these files or you can create your own txt file and edit it with your own application, which is useful if you would like to modify the queries. Please provide the path to the file using the Autoreply Database User by clicking 'Database setup' and selecting the 'Autoreply script' tabpage (Figure 8)
The structure of SQL query scripts
The Autoreply Database User's SQL script contains filter-statement pairs, which works exactly like the if-then structure in programming languages. The received SMS message goes through the filter and if it is ok, an SQL SELECT statement will run. The message content of the selected rows will be forwarded to a phone number. This number is the 1st parameter of the SELECT statement's result set, while the message text is the 2nd. The filter is capable to identify phone numbers, text fragments and keywords. The keyword of each message is the first word of the text. Forwarding happens if the address of the recipient is included in the database (Figure 11).
A simple keyword example
This example will run on the following table which you can see in Figure 9.
You can run the following script in Figure 10 on the 'autoreplymessage' table by adding it to 'sqlscript.txt'. As a reminder the keyword, which is symbolized by the 'k' character, is the first word in the SMS message. This word will be tested by the filter. If a filter check is successful, the SELECT statement runs and the script will stop. As you can see, you can place constant values in the SELECT statement. For example '$sender', which is the sender of the incoming message.
The filter part of the SQL query script works similarly as the filter part of the SMS messaging script, but keep in mind only to search filter examples, since the second part is always an SQL SELECT statement.
Pre-defined keywords in Ozeki SMS Gateway
These type of keywords can be placed in the script. For example '$sender' :
Autoreply Database User can recognize the following keywords:
The autoreply script's structure is explained on these webpages:
Step 7 - Test autoreply from database solution
After doing Step 1 to 6, you can try the solution. Ozeki SMS Gateway will forward incoming SMS messages to your Autoreply Database User. The SQL query script filters the SMS message to create the proper SELECT result sets. Each row of the result set will represent a message to be replied or forwarded.
You can track the messaging log of your Autoreply Database User at the following path: