ODBC Account

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

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

ODBC account 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.

Using the database

SMS Studio uses the following algorithm to read Incoming and OutogingStatus tables:

1. Read the DatabaseState table to obtain the LastReadID value for the table of interest.

2. Read only the records from the table with the ID value greater than the LastReadID value.

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

4. Optionally, delete the records read in step 2.

It is recommended that an external entity, which reads the Outgoing and IncomingStatus tables, uses the same algorithm.

Sending messages

SMS Studio writes outgoing messages to the Outgoing table.

If the OutgoingStatus table is not in use, the message will be considered as sent when it is written to the database.

If the OutgoingStatus table is in use, the message will be considered as 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

SMS Studio collects incoming messages from 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 Statistics 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.
• 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.