SMS FORWARDER

In this example I am going to give the SQL definitions, that can be used to set up Ozeki Message Server to operate simultaneously in two different locations and to forward messages from one location to another using GSM modems. Figure 1 presents the scenario.

sms forwarder software
Figure 1 - SMS Forwarder

Here is the mysql script that needs to be used to do the message forwarding:

CREATE TABLE ozekimessagein1 (
id int(11) NOT NULL auto_increment,
sender varchar(30) default NULL,
receiver varchar(30) default NULL,
msg varchar(160) default NULL,
senttime varchar(100) default NULL,
receivedtime varchar(100) default NULL,
operator varchar(100),
msgtype varchar(160) default NULL,
reference varchar(100) default NULL,
PRIMARY KEY (id)
);

CREATE TABLE ozekimessageout1 (
id int(11) NOT NULL auto_increment,
sender varchar(30) default NULL,
receiver varchar(30) default NULL,
msg varchar(160) default NULL,
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),
PRIMARY KEY (id)
);


CREATE TABLE ozekimessagein2 (
id int(11) NOT NULL auto_increment,
sender varchar(30) default NULL,
receiver varchar(30) default NULL,
msg varchar(160) default NULL,
senttime varchar(100) default NULL,
receivedtime varchar(100) default NULL,
operator varchar(100),
msgtype varchar(160) default NULL,
reference varchar(100) default NULL,
PRIMARY KEY (id)
);

CREATE TABLE ozekimessageout2 (
id int(11) NOT NULL auto_increment,
sender varchar(30) default NULL,
receiver varchar(30) default NULL,
msg varchar(160) default NULL,
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),
PRIMARY KEY (id)
);

DELIMITER |


drop function getPhoneNum|
CREATE FUNCTION getPhoneNum (p1 VARCHAR(160))
RETURNS VARCHAR(160)
DETERMINISTIC
BEGIN
DECLARE S1 VARCHAR(160);
SELECT SUBSTRING_INDEX(p1,' ',1) INTO S1;
RETURN S1;
END |


drop function getText|
CREATE FUNCTION getText (p1 VARCHAR(160))
RETURNS VARCHAR(160)
DETERMINISTIC
BEGIN
DECLARE S1 VARCHAR(160);
Select mid(p1,locate(' ',p1),length(p1)-locate(' ',p1)+1) INTO S1;
RETURN S1;
END |

drop trigger smscopy2 |
CREATE TRIGGER smscopy2 AFTER INSERT ON ozekimessagein1
FOR EACH ROW BEGIN
INSERT INTO ozekimessageout2 (receiver,msg,status) values (getPhoneNum(New.msg),concat(concat(New.sender,' '),getText(NEW.msg)),'send');
END;
|

drop trigger smscopy1 |
CREATE TRIGGER smscopy1 AFTER INSERT ON ozekimessagein2
FOR EACH ROW BEGIN
INSERT INTO ozekimessageout1 (receiver,msg,status) values (getPhoneNum(New.msg),concat(concat(New.sender,' '),getText(NEW.msg)),'send');
END;
|

DELIMITER;

More information