Examples > My SQL Inbox / Outbox

This example will show :

1. How to install MySql JDBC driver.
2. How to create a MySQL Datasource
3. How to save all incoming messages to table Inbox in MySql
4. How to setup a Timer to send the messages in table Outbox.

 

Note:

A new Inbox/Outbox scenario is available within the release, you can download the new release and import the configuration file under the samples directory

---

How to install MySql JDBC driver.

• Download MySql JDBC driver if you don't already have it: Connector/J
• Copy the driver jar file to the lib directory under GnGSmS
• Restart GnGSmS to recognize the added jar.

---

How to create a MySQL Datasource

• Goto the Datasource tab and click the add button:
• Enter the datasource parameters as follows, you will need to replace the server address, port, username and password with your MySql equivalents
    :

 

• Start the new MySql Datasource from the control tab, green status means the connection is established successfuly
   

---

How to save all incoming messages to table Inbox in MySql

We will use a  SQL action to save incoming messages to the database

• First create the Inbox table in schema gngsms:
  

  			DROP TABLE IF EXISTS `gngsms`.`inbox`;
  			CREATE TABLE `gngsms`.`inbox` (
  			`id` int(10) unsigned NOT NULL auto_increment,
  			`SMS_FROM` varchar(45) NOT NULL,
  			`SMS_TO` varchar(45) NOT NULL,
  			`SMS_TEXT` varchar(45) NOT NULL,
  			PRIMARY KEY (`id`),
  			KEY `Inbox_from` (`SMS_FROM`),
  			KEY `Inbox_to` (`SMS_TO`)
  			);
  			

• Open Actions tab, right click and select SQL action

• Select Data source: MySql

• Select Statement type: update

• Statement: INSERT into gngsms.inbox (SMS_From, SMS_TO, SMS_TEXT) values('${FROM}' , '${TO}' , '${TEXT}')
   

• To test you can use the "inject" button to simulate the receiving of a message. you should see a new row in the Inbox table
  

---

How to setup a Timer to send the messages in table Outbox.

The simulation of an outbox is bit more work than the inbox.

• Create the Outbox table in gngsms schema:

  DROP TABLE IF EXISTS `gngsms`.`outbox`;
  CREATE TABLE  `gngsms`.`outbox` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `SMS_FROM` varchar(45) NOT NULL,
    `SMS_TO` varchar(45) NOT NULL,
    `SMS_TEXT` varchar(45) NOT NULL,
    `SMS_UDH` varchar(45) NOT NULL,
    `SMS_SENT` char(1) NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `Outbox_from` (`SMS_FROM`),
    KEY `Outbox_to` (`SMS_TO`)
  ) ;

 

• We will create a repeatable timer that runs every 10 seconds.
  

 

• A group action to gather all actions to be executed by the timer.
  Notice the expression used ${SERVICE} = TIMER, this means that the group of actions will only execute for the messages originated from the timer
  

• A SQL action to read unsent messages in the outbox table:

  select ID,  SMS_FROM , SMS_TO, SMS_TEXT , SMS_UDH from gngsms.outbox where SMS_SENT != 'Y'

  

• A Loop action to loop over all unsent message, the ${SQLROW} is a sql action auto generated variable that stores the number of rows retrieved
  

• A Sender action inside the loop to send messages, this action will use a G1 //TODO add demo for gateways.
  note that i is the variable used in the loop as above,  ${i} will be evaluated 0,1,2 .. n where n is the loop size (the number of unsent messages in this case)
  

• A SQL action to mark the message sent
  

  update gngsms.outbox set SMS_SENT= 'Y' where ID = '${ID${i}}'

  
• To test you can just insert a new row in the Outbox table and specify your number as the SMS_TO, wait few seconds then check the logs or wait for the message to arrive on the mobile.