How to save all SMS messages into an SQL Text file

For high performance SMS systems, where message speeds go above 100 MPS (Messages Per Second), the database servers often cannot keep up with the load put onto them by the standard database reporting feature of Ozeki 10. They simply cannot execute several hundreds of thousands of SQL queries per second. To overcome this difficulty, Ozeki SMS Gateway provides SQL Text file reporting. This feature records each SQL command used for database reporting into a TXT file. You can use these txt files to do batch inserts into your database, to have a log of all messages. This guide explains how to setup this feature.

The reporting system can save messages going through the system into different sources, such as databases, txt files, etc. One of the most popular choice is using SQL text files to save a log of each message. You are able to open reporting system from the Edit menu Reporting option as you can see it on the Figure 1.

Figure 1 - Open reporting menu

In the Reporting menu click on the Create new SMS Reporter button and than select SQL Txt reporting type (Figure 2).

Figure 2 - Add SQL Txt reporter

The details page of the SQL Txt reporter allows you to configure the file location and when will be created an independent .sql file for the selected period by the SMS Gateway (Figure 3).

Figure 3 - Configure SQL Txt reporter

From the Admin user you can send SMS message. Provide the recipient address, the message and click on the OK button (Figure 4).

Figure 4 - Send Messages

Now in the folder what you set in the SQL Txt reporter details page you can see the .sql text file is created. The default folder is C:\Ozeki (Figure 5).

Figure 5 - Sql file in folder

Finally if you are open the .sql text file you can see that all the sent and received sms messages are stored as the Figure 6 shows. You can use these .sql files to do batch inserts into your database, to have a log of all messages.

Figure 6 - Sql file content

More information