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 split 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.

Condition

Each section starts with a condition consisting of a single row split 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). FONT style="font-size:15px;">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

The second part is the match pattern which is the character filter (Figure 4).

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:

Words	Value
$originator	The telephone number the message was sent from.
$sender	=$originator (The $sender is another name for the $originator.)
$recipient	The telephone number the message was sent to.
$receiver	=$recipient (The $receiver is another name for the $recipient.)
$messagedata	The text of the message.
$keyword	The 1st word in the message. This word is called keyword.
$after	The remaining text after the keyword.
$senttime	A timestamp which shows when the message was sent.
$receivedtime	A timestamp which shows when the message was received.
$messagetype	The type of the message, which is 'SMS:TEXT' in most cases.
$id	A unique string. It identifies each message in Ozeki SMS Gateway.
$operator	Name of the service provider. It receives the message in Ozeki SMS Gateway.
$1	The 1st word in the message.
$2	The 2nd word in the message.
$3	The 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. (Figure 6)

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)

Figure 6 - 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 on if the word is 'RED', 'BLUE' or other. Uppercase and lowercase characters are taken into account equally. (Figure 7)

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 colour.' 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 7 - SQL query script example

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

id          keyword                        msg
----------  ------------------------------ -------------------------------------
 1          default                        There is no data for this keyword.
 2          red                            Red is a good colour.
 3          blue                           Blue is not the best option.
 

Figure 8 - 'autoreplymessage' table

id          sender                         message
----------  ------------------------------ -------------------------------------
 1          +3620123456                    red Hello Autoreply Database User
 2          +3670654321                    red Good day to you Recipient

Figure 9 - 'log' table for incoming message

More information

• Installation guide
• Create database layout
• Sample script file
• Script structure