Learn how to send and receive SMS messages using an existing SQL database with Diafaan SMS Server. Get instructions about the installation of the ODBC or OLE DB driver, how to connect to the database with a connection string and use the database tables and fields of your database.
Connect to an existing database
Install the driver
Diafaan SMS Server supports three driver models to connect to your database, Microsoft SQL Server, ODBC and OLE DB. Database drivers for some databases like Microsoft SQL Server or dBase are already installed by default in Windows but for most databases you will have to install the ODBC or OLE DB driver for your specific database. Usually you can find the right drivers on the website of the database manufacturer.
Create the connection string
All information necessary to connect to the database is stored in the connection string. The format of the connection string is different for each ODBC/OLE DB driver and database. Examples of connection strings to connect to a large number of databases can be found on the website http://www.connectionstrings.com.
In the example below you can see that the connection string contains information about the server on which the database runs, the name of the database and the name and password needed to connect to the database. You can use the Test button to test if the connection string is set up right.
Use the database tables and fields
Database tables
The SQL connector supports three database tables, one for messages to send (MessageOut), one to store received messages (MessageIn) and a table to log sent or failed calls (MessageLog). For each database table you can provide the table name of your database and each database field name you want to use.
Send SMS table
Diafaan SMS Server polls the Send SMS table for new records and uses the information to send SMS messages. After the records are read in Diafaan SMS Server either deletes the record or updates one of the fields to a fixed value to prevent that messages are sent more than once. The only required field for the Send SMS table is Index which should have a unique value, without this field Diafaan SMS Server does not know which records should be deleted or updated.
example 1
If you have a database table ProcessErrors with three fields, a unique Id field, a Mobile field that contains a GSM number and an ErrorMessage field that contains the text that should be sent to the mobile number you can enter these table and field names as in the screenshot below:
example 2
If you have a database with the same database table but without the Mobile field it is also possible to use a fixed value instead of a database field name by enclosing the text in double quote characters. In this case all messages will be sent to GSM number +44xxxxxxxx. Quoted strings can be used in all values for the Send SMS table except the Index and Change field to values.
Receive SMS table
In the Receive SMS tab page the database table name and field names where received SMS messages are stored can be added. All fields are optional, you can choose to add only the information about the received SMS message you are interested in to the database.
SMS Log table
Messages that are sent or failed to send can be stored in the SMS Log database table. All fields are optional but when the Index and Message Id fields are not used, the status of the message cannot be updated. Status updates occur when a the status of a previously sent message changes. This happens for example when the message is received on the GSM phone or if the message could not be delivered on the GSM phone because it was switched off.
The Message Status Code field stores the message result. Status codes between 200 and 299 indicate that the message is sent successfully. Status codes between 300 and 399 means that the message failed.
status codes
200 |
Message sent and accepted by the gateway. |
201 |
Message received on the GSM phone. |
300 |
Message failed or rejected by the gateway. |
301 |
Status error received after the message was accepted by the gateway |
Advanced database fields
In the advanced tab page you can specify additional database fields that you want to use. The purpose of most fields should be clear, but some might need explaining.
Send SMS database fields
SMSOutGateway
Use this field if you want to send the message through one or more specific gateways. See Route messages for more information about routing options.
SMSOutUserId and SMSOutUserInfo
These two fields are not used by Diafaan SMS Server, but are transparent and written without change in the SMSLogUserId and SMSLogUserInfo fields of the MessageLog database table. The fields can be used to track the messages you send or to add your own information to the log database table.