Data Logger Suite: Logging and Monitoring. Saving SMS to a database with an acknowledgement
Download and install our software
There are two ways to solve this problem:
- Method 1 "Synchronous". The SQL Database Pro module will be used to write data to the database. A procedure stored on the server that will prepare the confirmation text will be called instead of the SQL script of the INSERT type. The disadvantage of this method is that text messages in the queue will not be written to the database until the stored procedure is executed and returns a respond. This method is available only when you write to databases where it is possible to create stored procedures (MySQL 5+, MSSQL, Oracle).
- Method 2 "Asynchronous". Data will be written to the database with the help of any method, for example, the one described in the "Writing text messages to the database" section. Third-party software will keep checking the table in the database for new text messages, generate responds and place them to a separate table. In its turn, our program will keep checking the contents of that table and send text messages via the GSM modem. The additional feature of this method is that it is possible to use different modems to receive and send text messages.
Sending the confirmation SMS message synchronously
- You should create a stored procedure that will receive a set of parameters, perform the necessary operations and return the respond text. You can see a sample stored procedure for SQL Express 2008 below. This simple procedure writes data to the table and returns "OK". You can modify this example according to your needs. Note that you should give permissions to execute this procedure on the server. By default, administrator permissions are required to execute this procedure.
DECLARE @result nvarchar(32);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- insert data to table
INSERT INTO SMSIN (DATE_TIME_STAMP, NUMBER, SMS)
VALUES (@datetimestamp, @number, @sms);
-- return acknowledgement
SET @result = 'OK';
-- return result as dataset
- Select and configure a connection in the SQL Database Pro module as described in the "Writing text messages to the database" section. You should specify the query calling the stored procedure instead of an SQL query of the INSERT type. The MS SQL query for calling the stored procedure will look like this: EXECUTE smsack :P1, :P2, :P3. You should link items and parameters similarly to how it is described in the "Writing text messages to the database" section. Additionally, you should specify two parameters as it is shown in fig. 1.
Fig. 1. Configuring calling the stored procedure
- Now when a text message is written to the database, a reply will be sent to the number the text message was received from. You can see it in the program window (fig. 2).
Fig. 2. Reply to a text message