SQL script for SMS information query from Database

This guide displays a sample script file for SMS information query from database. It shows you how you can request information from multiple database tables based on the keywords in received SMS messages.

C:\Program Files\Ozeki\OzekiNG - SMS Gateway\config\TestApp\sqlscript.txt
###########################################################################
# Default script file for Autoreply configuraiton. This script
# is executed every time a new message is sent to the system 
# from a mobile phone
# 
# Entry:
#     One entry contains a single condition line that starts
#     with a condition option. The condition line is followed
#     by one or more action lines. Action lines contain SQL
#     commands. The entry is closed by an empty line  
#
# Condition options:
#     n - if the sender telephone number matches the pattern
#     m - it the message text matches the pattern
#     k - if the keyword matches the pattern. (The keyword is first word 
#         of the messages. The keyword is converted to uppercase)
#
# Action:
#     Each line contains an SQL command. Before the SQL commands: 
#     are executed on the database, the following words are replaced:
#     $originator - is replaced to the sender telephone number of the messages
#     $recipient - is replaced to the telephone number that received the message 
#     $messagedata - is replaced to the message text
#     $keyword - is replaced to the keyword in the message
#     $senttime - is replaced to the timestamp, that represents when the message was sent
#     $receivedtime - is replaced to the timestamp, that represents when the message was received
#     $messagetype - is replaced to the message type (in most cases this will be SMS:TEXT)
#     $id - is replaced to the unique string identifier of the message
#     $operator - is replaced to the name of the service provider connection that received the message
#     
# Response:
#     The SQL commands in the Action section can return one or more response messages. 
#     A response message is a row in the result set of the SQL select. A row in the result
#     set should contain the following columns:
#     recipient - The 1st column in the result set (Mandatory)
#     messageData - The 2nd column in the result set (Mandatory)
#     messageType - The 3rd column in the result set (Optional)
#     sender - The 4th column in the result set (Optional)
#     operatorNames - The 5th column in the result set (Optional)
#
# Comments:
#     The hashmark sign (#) marks a single line comment
###########################################################################

###########################################################################
#Condition: 
#    The sender number starts with +44
#Action: 
#    One SQL statement is specified. Before the statement
#    is executed the word '$sender' is resplaced to the
#    phone number of the person who sent the message to the
#    system.  
#

n^+44.*
SELECT '$sender',messagetext FROM defaultresponse 

###########################################################################
#Condition: 
#    The message startes with the keyword: RED
#Action: 
#    Two SQL statements are executed, the first one
#    updates a database table, the second one returns a
#    response message

k^RED
UPDATE stats SET incoming = incoming+1 WHERE keyword='RED'; 
SELECT '$sender',messagetext from defaultresponse where keyword='RED'

###########################################################################
#Condition: 
#    Message text contains the word ALERT
#Action: 
#    Many messages will be sent to the recipients stored in 
#    the alertrecipients database table

m.*ALERT.*
SELECT recipients,'Alert, somebody broke in!' FROM alertrecipients

###########################################################################
#Condition: 
#    All incoming messages (coming from any phone number)
#Action: 
#    Select a response based on keyword

n.*
SELECT '$sender',messagetext from keywordresponse where keyword='$keyword'


Total number of lines: 89

More information