How to send out SMS messages from the same database table using two Ozeki NG SMS Gateway software products

This guide provides information about how to run two Ozeki NG SMS Gateway software products simultaneously on different computers connecting to the same database to send out SMS messages. In this way your system will provide better uptimes and better tolerance against hardware related errors. If one of your Ozeki NG SMS Gateway or the PC on which the gateway is running stops due to any error, the SMS sending will be continued by the other PC. The solution in this text will help you to setup a MySQL database server and a database user in Ozeki NG SMS Gateway.

physical scheme of an ozeki ng sms gateway cluster
Figure 1 - Physical scheme of an Ozeki NG SMS Gateway cluster

What you need:

  1. Installing the two Ozeki NG SMS Gateway software products on different PCs
  2. Connecting the gateway to a service provider
  3. Creating a stored procedure in a MySQL server to avoid the sending of the same messages from the two gateways
  4. Adding new Database user and modifying its database queries
  5. Adding a new outgoing routing rule

1. Installing the two Ozeki NG SMS Gateway software products on different PCs

In order to start using Ozeki NG SMS Gateway, first you need to install them to your two computers. If you have any problem during the installation process, just follow this guide: Installation Steps.

2. Connecting the gateway to your service provider

After you have installed the two Ozeki NG SMS Gateway software products, you need to setup at least one service provider connection to send out your SMS messages. There are two ways of sending SMS messages:


You can configure the service provider connections based on the guides that can be found here.

3. Creating a stored procedure in a MySQL server to avoid the sending of the same messages from the two gateways

If you would like to send out SMS messages from the two PCs using the same database server and table, you need to modify the default SQL queries to avoid the situation that the gateways will send out the same SMS messages from the two software products.

First of all, you need to create the tables for outgoing and incoming SMS messages: ozekimessageout and ozekimessagein.

MySQL create table script: 


 CREATE TABLE IF NOT EXISTS `ozekimessagein` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `sender` varchar(30) DEFAULT NULL,
   `receiver` varchar(30) DEFAULT NULL,
   `msg` text,
   `senttime` varchar(100) DEFAULT NULL,
   `receivedtime` varchar(100) DEFAULT NULL,
   `operator` varchar(100) DEFAULT NULL,
   `msgtype` varchar(160) DEFAULT NULL,
   `reference` varchar(100) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `id` (`id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

 CREATE TABLE IF NOT EXISTS `ozekimessageout` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `sender` varchar(30) DEFAULT NULL,
   `receiver` varchar(30) DEFAULT NULL,
   `msg` text,
   `senttime` varchar(100) DEFAULT NULL,
   `receivedtime` varchar(100) DEFAULT NULL,
   `reference` varchar(100) DEFAULT NULL,
   `status` varchar(20) DEFAULT NULL,
   `msgtype` varchar(160) DEFAULT NULL,
   `operator` varchar(100) DEFAULT NULL,
   `errormsg` varchar(250) DEFAULT NULL,
   `callbackid` varchar(500) NOT NULL,
   `reason` varchar(1000) NOT NULL,
   `rn` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `id` (`id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Now a stored procedure has to be created in your database server which will help you to prevent multiple SMS sendings. If one row will be queried, it will be locked, so the other gateway will not select the same SMS messages.

First you need to login to your database management software. You can use mysql console, phpmyadmin or any other management software. Then select the database in which you created the ozekimessageout and ozekimessagein tables and issue the following create script.

MySQL stored procedure:


DELIMITER //
CREATE PROCEDURE selectMessages(IN counter INT(11))
BEGIN
  DECLARE random INT(11);
  SET random = FLOOR(RAND()*50000)+FLOOR(RAND()*50000);
  UPDATE ozekimessageout 
    SET status = 'polling',rn = random 
    WHERE status = 'send' 
    LIMIT counter;
  SELECT id,sender,receiver,msg,msgtype,operator 
    FROM ozekimessageout 
    WHERE rn = random AND status = 'polling';
END; //
DELIMITER ;

create script in phpmyadmin
Figure 2 - Create script in PHPMyAdmin

4. Adding new Database user and modifying its queries

After you have created the two tables and the stored procedure, you need to add a new Database user to your two Ozeki NG SMS Gateway software products. In order to connect the gateway to a MySQL database, first you need to install a MySQL ODBC Driver to your Windows operating system and then add a new data source to it which will be connected to your database server.

new data source
Figure 3 - New data source

If you have added the new data source, return to your Ozeki NG SMS Gateway, install a new Database user and provide a connection string (related to your database server) under the Database connection tab in the Connection String textbox and select ODBC from the Connection string type dropdown menu.

provide connection information
Figure 4 - Provide connection information

Now go to the SQL for sending tab and change the default query of polling method. You need to call the previously created stored procedure (here you need to provide the number of messages that you would like to select). In the Check the ozekimessageout table every _ sec. for outgoing messages textbox you need to provide the number of seconds in which the gateway will always check for SMS messages with send status. In the Maximum number of messages to send with one poll textbox you need to provide the same value that you used when you called the selectMessages() stored procedure.

Note: do not check the database frequently (do not use very low values in the first textbox) and do not select a lot of messages in one query (do not use very high values when you call the stored procedure and in the last textbox).

Important: "selectMessages(25)" and "Maximum number of messages to send with one poll: 25" textbox have to be the same value.

give the previously created stored procedure
Figure 5 - Give the previously created stored procedure

5. Adding a new outgoing routing rule

Finally, you need to create a new outbound routing rule which will forward the SMS messages from the database user to the the service provider connection that will send out the SMS messages.

new outgoing rule
Figure 6 - New outgoing rule

This guide shows you a solution how to send out SMS messages from the same MySQL database server, the same database and the same table using two Ozeki NG SMS Gateway software products. The stored procedure can also be created in different ways. It is also possible to use other database servers, such as MSSQL or Oracle.

If you have any other question, please send us an e-mail at info@ozekisms.com.



Appendix: If you wish to use MS SQL Server, you need to use the following create table script and stored procedure.

Create table script for MS SQL Server:


CREATE TABLE ozekimessagein ( 
    id int IDENTITY (1,1), 
    sender varchar(30), 
    receiver varchar(30), 
    msg nvarchar(160), 
    senttime varchar(100), 
    receivedtime varchar(100), 
    operator varchar(30), 
    msgtype varchar(30), 
    reference varchar(30), 
);

CREATE TABLE ozekimessageout ( 
    id int IDENTITY (1,1), 
    sender varchar(30), 
    receiver varchar(30), 
    msg nvarchar(160), 
    senttime varchar(100), 
    receivedtime varchar(100), 
    operator varchar(100), 
    msgtype varchar(30), 
    reference varchar(30), 
    status varchar(30), 
    errormsg varchar(250),
    rn int 
);

Stored Procedure for MS SQL Server:


CREATE PROCEDURE selectMessages
    @counter INT
AS
    DECLARE @random INT
    DECLARE @upper INT
    DECLARE @lower INT
    SET @lower = 1
    SET @upper = 50000
    SET @random = ROUND(((@upper - @lower - 1) * RAND() + @lower), 0)
	
    UPDATE TOP (@counter) ozekimessageout 
      SET status = 'polling', rn = @random 
      WHERE status = 'send'
    SELECT id,sender,receiver,msg,msgtype,operator 
      FROM ozekimessageout
      WHERE rn = @random AND status = 'polling'
GO

SQL for Sending \ Polling tab of the Database user:


EXEC selectMessages @counter = 25;

Important: @counter and "Maximum number of messages to send with one poll" textbox have to be the same value.

Learn more about SMS services
People who read this also read...

More information