The structure of the SQL query script

Scroll down to see the structure of the SQL query script. You can render a script file to each Autoreply Database User, which is capable to send SMS messages if they follow the script. The file works with every incoming message and selects outgoing messages according to the filter.

Introduction

Look at the script file. You can see that it is splitted into sections separated by empty lines (Figure 1). You can write as many sections as you would like with no maximum limit. The sections are read sequentially and the first section that matches the incoming SMS will be used to create the response SMS message.

Each section's first line is the filtered condition and the following one or more SQL statements are called action. SQL SELECT statements can return one or more response messages. If the filter condition matches the incoming message data, it calls the statements to be executed until the empty line is found. In this case it will not check other sections.

you can see the filtered conditions and the sql statements in this example
Figure 1 - You can see the filtered conditions and the SQL statements in this example

Condition

Each section starts with a condition consisting of a single row splitted into two parts (Figure 2)

The first part is the match target selector, a single character that determines which part of the incoming message should be filtered (Figure 3). The second part is the match pattern which is the character filter (Figure 4).

the first line of every section is called:condition
Figure 2 - The first line of every section is called: condition

You can select a match target selector character from the following options:
# 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)
Figure 3 - Match target selectors

These match pattern examples work on the 'm' massage target:
m.* - matches any text message
m^abc.* - matches if text message starts with 'abc' or 'ABC'
m.*abc.* - matches if text message contains 'abc' or 'ABC'
Figure 4 - Match pattern examples

Here you can find a regular expression tutorial for the available match patterns.

Action

Each condition filter is followed by an action which consists of SQL statements.

You can only send SMS messages with SQL SELECT queries, but you can run INSERT and UPDATE statements as well. Keep in mind that the 1st parameter of the SELECT result set is the recipient of the message to be sent, while the 2nd parameter is the message itself. On Figure 5 you can see a few examples.


k^RED
INSERT INTO log (sender,message) VALUES ('$sender','$messagedata')
SELECT '$sender',msg FROM autoreplymessage WHERE keyword='red'

k^BLUE
SELECT '$sender',msg FROM autoreplymessage WHERE keyword='blue'

k.*
SELECT '$sender',msg FROM autoreplymessage WHERE keyword='default'
Figure 5 - SQL statement examples where the recipient is the '$sender'

The following words are replaced in the SQL statements:
WordsValue
$originatorThe telephone number the message was sent from.
$sender=$originator (The $sender is another name for the $originator.)
$recipientThe telephone number the message was sent to.
$receiver=$recipient (The $receiver is another name for the $recipient.)
$messagedataThe text of the message.
$keywordThe 1st word in the message. This word is called keyword.
$afterThe remaining text after the keyword.
$senttimeA timestamp which shows when the message was sent.
$receivedtimeA timestamp which shows when the message was received.
$messagetypeThe type of the message, which is 'SMS:TEXT' in most cases.
$idA unique string. It identifies each message in Ozeki SMS Gateway.
$operatorName of the service provider. It receives the message in Ozeki SMS Gateway.
$1The 1st word in the message.
$2The 2nd word in the message.
$3The 3rd word in the message, etc...

Each SQL statement is executed line by line until the interpreter reaches an empty line, where it finally stops.

(Tip: You can only get reply message content by using SELECT statements.)

Response

The result set of the SELECT SQL statement contain the messages to be forwarded or responded. The number of rows you can find in the result set will determine the number of messages to be sent.

The result set can contain the following parameters:
recipient - The result set's 1st column (Mandatory)
messageData - The result set's 2nd column (Mandatory)
messageType - The result set's 3rt column (Optional)
sender - The result set's 4th column (Optional)
operatorNames - The result set's 5th column (Optional)

Ozeki 10 SMS Gateway's Autoreply Database User will send SMS messages from the results.

Example

Look at an example how to filter the 1st word in every incoming message and how to do separate actions on each. The action depends if the word is 'RED', 'BLUE' or other. Uppercase and lowercase characters are took into account equally.

Look at SQL tables found in Figure 7 and 8 to examine the following example:

k^RED
INSERT INTO log (sender,message) VALUES ('$sender','$messagedata')
SELECT '$sender',msg FROM autoreplymessage WHERE keyword='red'
#The first filter checks if word 'RED' was the keyword.
#If true, it INSERT's the message into the 'log' table
#and SELECT-s 'Red is a good color.' as a reply message.
#If false it goes to the next filter.

k^BLUE
SELECT '$sender',msg FROM autoreplymessage WHERE keyword='blue'
#The second filter checks if word 'BLUE' was the keyword.
#If true, it SELECT-s 'Blue is not the best option.' as a reply message.
#If false it goes to the next filter.

k.*
SELECT '$sender',msg FROM autoreplymessage WHERE keyword='default'
#The third filter will accept any other keyword.
#It SELECT-s 'There is no data for this keyword.' as a reply message.
Figure 6 - SQL query script example

id          keyword                        msg
----------  ------------------------------ -------------------------------------
 1          default                        There is no data for this keyword.
 2          red                            Red is a good color.
 3          blue                           Blue is not the best option.
 
Figure 7 - 'autoreplymessage' table

id          sender                         message
----------  ------------------------------ -------------------------------------
 1          +3620123456                    red Hello Autoreply Database User
 2          +3670654321                    red Good day to you Recipient
Figure 8 - 'log' table for incoming message

More information