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)

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'

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'

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)

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.

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.
 

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