sqlconnector SQL connector

<< Click to Display Table of Contents >>

Navigation:  Reference > Connector properties >

sqlconnector SQL connector

Database properties

Select the database driver type and connection string to the database you want to use. Diafaan SMS Server supports three database driver types, Microsoft SQL Server (Express), ODBC and OLE DB.





Send SMS properties

In the Send SMS tab page you can enter the table name and the basic database field names of the database table that is polled for new messages to send. After the database records are read in, the records are either removed from the database or one of the database fields is set to a fixed value. Records where this value is set are not read anymore in successive database pollings.

If large numbers of messages will be sent it is better to remove the records from the database, leaving a large number of records in the database might have an impact on the database performance.




In the Send SMS tab page you can also use double quotes to indicate fixed values. In the example below the To part of the SMS message is not retrieved from the database but all messages are sent to number +44xxxxxxxxxx. This enables you to use database tables where no GSM number is available. The only required database field is the Index, all other fields are optional.




Scheduled messages

The Advanced settings offers support for an SMSOutScheduled date/time database field. If this database field is used individual messages are not read from the database before the specified date and time.



Receive SMS properties

Use the values in the Receive SMS tab page to specify the database table name and basic database field names where received messages are stored. More fields from the received message can be specified in the Advanced properties.



SMS Log properties

The SMS Log database table is used to store the result of message send attempts. Specify the database table name and basic database field names where the message logs are stored. More fields can be specified in the Advanced properties.


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


Message sent and accepted by the gateway.


Message received on the GSM phone.


Message failed or rejected by the gateway.


Status error received after the message was accepted by the gateway


The message status can be updated, this means that a message with status code 200 (accepted by the gateway) can change to status code 301 if the GSM network cannot deliver the message or to status code 201 when the message is delivered on the phone.



Advanced properties


Log all SQL transactions from this gateway to a file.



The maximum number of database records this connector will process in one batch. If this value is higher than one and a number of messages are sent at the same time, the connector loads multiple messages concurrently. This increases the speed in which the messages are processed considerably.



Default message send priority, set to 0 to allow a custom priority for individual messages. A higher number ensures that messages are placed higher in the message send queue and are sent before messages with a lower priority.



The number template can be used to translate the number from one format to another.


For more information see Use number templates.



List of allowed destination numbers (to address) for outgoing SMS messages, messages to other numbers are blocked. When the list does not have any items, messages to all numbers are allowed.


For more information about the white list and black list format see: Use white lists and black lists.



Enable or disable HTTP callbacks for this gateway. This option is only available when the HTTP callback option is enabled in the general options.



Specifies the most extensive character set the database supports, the best character set for most modern databases is 'Unicode'. The UCS2 character set can solve a compatibility problem with some Unicode characters (like smileys) on some versions of the MySQL database. ASCII can be used for older databases that do not support Unicode at all. Characters in received messages that are not in the selected character set are converted to question marks (?).



The time interval in seconds between two queries of the Send SMS database table.



Minimum interval time for updates of the MessageIn and MessageLog database tables. Increase this value to increase the number of messages that are written or updated to the database per transaction, this can reduce strain on the database in high-traffic systems.



Lock the MessageOut table for reading when the polling transaction is in progress. This option can be set when multiple SQL Connectors or multiple installations of Diafaan SMS Server use the same MessageOut table to import messages. With some database configuration this setting prevents that messages are loaded multiple times by different SQL Connectors.



If set to true, messages that cannot be written to the MessageIn table because of a database error are kept in memory until a follow up database transaction succeeds.



If set to true, messages that cannot be written to, or updated in, the MessageLog table because of a database error are kept in memory until a follow up database transaction succeeds.



Pause loading new messages from the MessageOut table when the send queue exceeds this threshold. Set to 0 to disable the threshold.



The type of database that is used, changing this value causes small changes in the SQL query strings to optimize performance.


database types


Standard ANSI SQL


Implements the TOP keyword


Implements the ROWNUM keyword


Implements the LIMIT keyword


Database field names

Additional database field names can be entered to query or store more parts of the sent or received message fields.