Data Logger Suite: Logging and Monitoring. SMS to database: Writing short messages from a GSM modem into a database. Part I
Download and install our software
Configuring the format for receiving text messages
You should enable the Add date and time from the text message and Add number from the text message options. In this case, the program will add the corresponding data to the message text and pass it for further processing and inserting SMS to database.
You should enable the Query modem option if the modem cannot notify the computer about new incoming text messages. In this case, the program will query the modem for new messages every 10 seconds.
Notifications are configured with the help of the AT+CNMI modem initialization string and it is specific for each modem model. You should consult the manual for the AT commands of your modem. The "SMS GSM modem settings and configuration" section contains sample commands for some modem types and the more detailed descriptions of other options.
Fig. 1. Advanced modem options
Configuring the parser module
After receiving a text message, the incoming text message module passes it to the parser for processing. The parser splits the incoming text message into several separate values. When a text message is received, it looks in the main window of the program like this:
Fig. 2. Main window
NUMBER<HT>DATE and TIME<HT>MESSAGE TEXT<CR><LF><SUB>
<HT> - the ASCII tabulation character with the hexadecimal code of 0x09h
<CR> - the ASCII carriage return character with the hexadecimal code of 0x0Dh
<LF> - the ASCII new line character with the hexadecimal code of 0x0Ah
<SUB> - the ASCII file end character with the hexadecimal code of 0x1Ah
If you disable either the Add data and time from the text message option or the Add number from the text message option, the data packet will look like this:
NUMBER<HT> MESSAGE TEXT<CR><LF><SUB>
DATE and TIME<HT>MESSAGE TEXT<CR><LF><SUB>
You should use the "ASCII Data Parser" module included in the installation package as your parser. You should enable this parser on the Query Analysis Filtering tab (fig. 1).
Fig. 3. Selecting the parser
Then you should open the parser configuration dialog box (fig. 4) using the Setup button.
Fig. 4. Specifying the data packet properties
Split packets by the start/end signs - enabled.
Specify the #0D#0A#1A in the Packet End Sign field, which corresponds to the hexadecimal codes of the following ASCII characters: <CR>, <LF> and <SUB>.
If necessary, you can configure a filter that will filter out unneeded text messages. You can find detailed information about this feature in the help file.
You should specify the fields a text message contains on the Data Parsing tab (fig. 5).
Fig. 5. Configuring the data packet format
You should add the description of all values you want to extract from a text message to the tree with parser items. Figure 5 shows the settings for a data packet that is generated when the Add date and time from the text message and Add number from the text message options are enabled.
- Item name - this name will later be used in the filtering and data export modules. You may use only Latin letters in the name.
- Data processing type - this option defines how the parser should extract a value from a text message. All values are separated with a delimiter in this example. That is why we select the corresponding data processing type.
- Data type - specifies the data type of the extracted value. NUMBER and SMS is a string in this example, which corresponds to the String type.
- Default value - this value will be used when a text message does not have the value to be extracted or it is impossible to convert it to the selected data type. The default value must correspond to the selected data type. For example, you cannot specify the default value for integers.
- Field delimiter - you should specify the value delimiter for the previously selected data processing type. The "#09" text is specified as a delimiter in this example, which corresponds to the hexadecimal code of the ASCII tabulation character.
- Position - the position of value in a text message is specified. The first position is one. If you disable either the Add data and time from the text message option or the Add number from the text message option, you will have to change this option as well.
You should specify the format of date and time extracted from a text message in the Date and Time field on the Data Format tab (fig. 6). The program always uses the ISO format YYYY-MM-DD HH:NN:SS. The date and time string will be converted into a value of the Datetime type with the help of this format. If the format is specified incorrectly, the program will display the corresponding error message. You can find the detailed description of the characters that can be used in the date and time format in the help file.
Fig. 6. Date and time format
You should test the parser after you configure it.
1. To do it, click the OK button to save the changes.
2. Enable the DDE Server data export module (fig. 7)
Fig.7. Data export module
3. Click the OK button to save the changes.
4. Wait for a new incoming text message to appear in the main window of the program.
5. Open the configuration dialog box and double-click the DDE Server data export module. Switch to the All active items tab in the DDE Server dialog box (fig. 8). This list must contain the configured parser items and their values. Here you can check whether the parser is configured correctly and, if necessary, make changes in its configuration.
Fig. 8. Active items
The parser also adds some additional items:
DATA_SOURCE_NAME - the number of the COM port used by the modem that received the message. If the program works with several modems simultaneously, this item makes it possible to identify the modem.
DATA_TIME_STAMP - the date and time when the program processed the message. It may differ from the SMSTIME time.
Configuring how to write SMS data to the database
- SMS GSM modem settings and configuration
- SMS to database: Writing short messages from a GSM modem into a database
- Saving SMS to a database with an acknowledgement
- SMS from database: Sending short messages from a database