|
Recommended reading:
If you wish to protect your service against hardware failure and reach higher
availability of your Ozeki NG SMS Gateway software, we recommend you to try
our Ozeki Cluster software product.
Ozeki Cluster automatically moves any
service to another computer in case of a hardware failure. You can
download it from:
Ozeki Cluster Site
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.
Figure 1 - Physical scheme of an Ozeki NG SMS Gateway cluster
What you need:
- Installing the two Ozeki NG SMS Gateway software products on different PCs
- Connecting the gateway to a service provider
- Creating a stored procedure in a MySQL server to avoid the sending of the same messages from the two gateways
- Adding new Database user and modifying its database queries
- 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 ;
Figure 3 - 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.
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.
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.
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.
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.
Dig deeper!
People who read this also read...
More information
Next page:
High volume SMS messaging
| 
