ODBC Connection

ODBC connection allows you to use a database as an SME.

This type of connection uses ODBC interface to communicate with a database.

ODBC connection enables external entities (e.g. application, web service, etc.) to exchange messages with SMS Studio.

The contents of this topic:

The structure of the database
Using the database
Sending messages
Receiving messages
ODBC Connection Dialog

The structure of the database

The database design is symmetrical to allow bidirectional communication between SMS Studio and external applications. The database should contain the following tables:

Incoming table is used by SMS Studio as a source of the incoming messages. This table should have the following fields:
ID - the auto-incremented integer value* that is used as the unique ID of the record in the table.
RefID - the unique integer value, assigned by external application.
MsgText - the text of the message.
MsgType - the type of the message - 0 for text, 1 for binary.
Sender - the address of the sender of the message.
Recipient - the address of the recipient of the message.
IncomingStatus table can be used by external application to read the statuses of incoming messages. This table should have the following fields:
ID - the auto-incremented integer value* that is used as the unique ID of the record in the table.
RefID - the unique integer value, taken from the Incoming table, identifying the incoming message.
Status - the integer value representing the status of the incoming message.
Outgoing table is used by SMS Studio for writing outgoing messages. This table should have the following fields:
ID - the auto-incremented integer value* that is used as the unique ID of the record in the table.
RefID - the unique integer value, assigned by SMS Studio.
MsgText - the text of the message.
MsgType - the type of the message - 0 for text, 1 for binary.
Sender - the address of the sender of the message.
Recipient - the address of the recipient of the message.
OutgoingStatus table is used by SMS Studio to read the statuses of outgoing messages. This table should have the following fields:
ID - the auto-incremented integer value* that is used as the unique ID of the record in the table.
RefID - the unique integer value, taken from the Outgoing table, identifying the outgoing message.
Status - the integer value representing the status of the outgoing message.
DatabaseState table is used as a state table of the whole database. This table has one record for each one of the previously described tables and should have the following fields:
TableName - the name of the table from the database.
LastReadID - the ID value of the last record read from the table.

The Status field of the IncomingStatus and OutgoingStatus tables may contain the following values:

0 - the message has been rejected (or not sent).
1 - the message has been accepted (or sent).
2 - the message has not been delivered.
3 - the message has been successfully delivered.

* The syntax for specifying the "auto-incremented integer value" field type varies across different database management systems. You should consult your DBMS documentation to find the proper syntax, for example:

In Microsoft Access use CREATE TABLE Incoming (ID COUNTER PRIMARY KEY, ...
In MySQL use CREATE TABLE Incoming (ID INTEGER AUTO_INCREMENT PRIMARY KEY, ...
In Microsoft SQL Server use CREATE TABLE Incoming (ID INT IDENTITY PRIMARY KEY, ...

Using the database

Depending on the Delete after read setting, SMS Studio chooses one of the following algorithms to read Incoming and OutogingStatus tables:

1. The Delete after read option is checked.

In this case, the DatabaseState table is not used and the following steps are performed in each read cycle:

- Get the number of records and the minimum ID value in the table.

- Read only the records with the ID value greater than the minimum ID value (up to allowed maximum number of records).

- Delete the records read in previous step.

2. The Delete after read options is not checked.

In this case, the DatabaseState table is mandatory, and it should be properly initialized prior to the first usage of the connection. The following steps are performed in each read cycle:

- Read the DatabaseState table to obtain the LastReadID value for the table.

- Read only the records with the ID value greater than the LastReadID value (up to allowed maximum number of records).

- Update the DatabaseState table to remember the greatest ID value read in the step 2.

It is recommended that an external entity, which reads the Outgoing and IncomingStatus tables, also uses one of the above algorithms.

Sending messages (from SMS Studio Outbox to external entity)

SMS Studio writes outgoing messages from the Outbox to the Outgoing table. Optionally, the OutgoingStatus table can be used to let SMS Studio keep the track of the status of messages (sending status and delivery reports).

If the Use outgoing status table option is not checked, the message will have status Sent when SMS Studio writes it to the database.

If the OutgoingStatus table is in use, the message will have status Submitted when it is written to the database. Along with each outgoing message in the Outgoing table, SMS Studio will supply a unique RefID value. The external entity is then responsible to return the status of the outgoing message by inserting a record into the OutgoingStatus table which should contain the RefID value and the Status of the outgoing message.

Example

1. When a message is stored in the Outbox to be sent by the ODBC Connection, SMS Studio will store this message into the Outgoing table as a new record:
RefID: 321654, MsgText: Hello, MsgType: 0, Sender: 5555, Recipient: 1234
The status of this message in the Outbox will be set to Submitted.

2. An external application is responsible to provide the sending status of this message, by inserting a new record into the OutgoingStatus table:
RefID: 321654, Status: 0 or 1
When SMS Studio collects this record, the status of this message in the Outbox will be set to Not sent or Sent.

3. An external application may also supply delivery status of this message, by inserting a new record into the OutgoingStatus table:
RefID: 321654, Status: 2 or 3
When SMS Studio collects this record, the status of this message in the Outbox will be set to Not delivered or Delivered.

Receiving messages (from external entity to SMS Studio Inbox)

SMS Studio collects incoming messages from the Incoming table and stores them in the Inbox. Optionally, the IncomingStatus table can be used to let the external entity keep the track of the status of messages (sending status and delivery reports).

If the Use incoming status table option is not checked, the external entity will not be able to learn the status of messages, and should consider that a message is sent once it has been stored in the Incoming table.

If the IncomingStatus table is in use, along with each incoming message in the Incoming table an external application should also write a unique RefID value. If the external application can not supply the RefID value, it should set it to 0 and SMS Studio will then use the ID value of the record instead. When a message is stored in the Inbox, or a delivery report is routed back, SMS Studio will insert a new record in the IncomingStatus table which will contain the RefID value and the new Status of the incoming message.

Example

1. An external application stores the following record into the Incoming table:
RefID: 123456, MsgText: Hello, MsgType: 0, Sender: 1234, Recipient: 5555

2. When SMS Studio collects this message, it will be stored in the Inbox. The status of this operation will be written as a new record in the IncomingStatus table:
RefID: 123456, Status: 1

3. If this message was routed and a delivery report is sent back to SMS Studio, then a new record will be inserted in the IncomingStatus table:
RefID: 123456, Status: 2 or 3

ODBC Connection Dialog

See Connection Dialog for description of the Connection, Account and Options tabs.

Database tab

Data Source lets you specify the ODBC Data Source Name (DNS) which is connected to the database you wish to use.
User name lets you enter the user name for connecting to the database.
Password lets you enter the password for connecting to the database.
Remember password allows you to specify if the application should remember your user name and password. If this option is not checked, the application will ask you for the user name and password each time you initiate the connection.
Collect every lets you specify the time interval at which the application will collect new records from the database.
Collect at most N records at once specifies the maximum number of records that connection is allowed to read with a single query.
Delete after read specifies whether the records from the Incoming and OugoingStatus tables should be deleted after they have been read.
Use outgoing status table specifies whether the application should read the OutgoingStatus table.
Use incoming status table specifies whether the application should write to the IncomingStatus table.
Use URL encoding for message text specifies whether the MsgText field in both Incoming and Outgoing tables should contain URL encoded text.
Use URL encoding for addresses specifies whether the Sender and Recipeint fields in both Incoming and Outgoing tables should contain URL encoded text.