Database integration channel
The database integration channel is a way to integrate with an external database using either an existing table or a set of stored procedures.
Find more information about channels in general here.
Use the database integration channel to connect through any existing database that has ADO or ODBC drivers available. For example:
- Microsoft SQL Server
- PostgreSQL
- MySQL
- MariaDB
- Oracle
- Etc…
The database integration channel works for any message type including SMS, WhatsApp and E-mail.
With the database integration channel you can send from an external data source and receive into an external data source. You can also update the message status in your external database according to the send and or delivery status.
Edit database integration channel
It’s possible to connect to multiple external databases at the same time by creating multiple database integration channels.
Settings
| Property | Description |
| Connection string | This is an ADO or ODBC connection string that connects to your target external database. |
| Escape characters | Specify a list of characters that need an escape character before storing in the external database. |
| Escape option | Specify how a character escape works. This can be either:
|
| String length | This is the maximum length for any string in the target database. If you’re receiving into the target database your message body truncated at this length. |
| Select from | Select messages to send from the external database. This can be either a ‘SELECT’ query from a table or a stored procedure call. |
| Confirm sending | (Optional) Confirm that a message is being sent by the SMS Server. This statement should update the status in your remote database indicate the message is sending.
You can use fields from the remote database ‘Select from’ query by enclosing them in ‘%’ characters. For example: If you remote database has a key field called ‘ID’ you can use ‘%ID%’ to refer to the key field. |
| Map message fields | Select which message type this channel will send and/or receive.
Map the fields from the ‘Confirm sending’ to message fields in the SMS Server. The ‘Attachments’ field is a special case. The SMS Server expects a field with semicolon (‘;’) separated paths to attachment fields. The SMS Server needs to be able to access these files. |
| Receive message | (Optional) Specify how a message moves to the external database. This can be an ‘INSERT’ statement or stored a procedure call. Refer to fields from the SMS Server message type by enclosing them in ‘%’ characters.
Specify an attachment directory that receives attachments when applicable. If a message has attachments the ‘%ATTACHMENTS%’ field contains semicolon (‘;’) separated file paths. To receive files you also need to setup a ‘Database Integrate Forward’ trigger. This trigger forwards from any channel to the database integration channel. |
| Status update | (Optional) Specify statements to update the status in the remote database according to the message status in the SMS Server.
To receive status updates you also need to setup a ‘Database Integrate Status Update’ trigger. This trigger updates the message status to the database integration channel. |
| Poll interval | This poll interval relates to sending messages from a remote database. Receiving message from the SMS Server into the external database is always immediate. |
| Connection options | Define when the remote database connection should be reset. Can be after every send or receive batch or after a number of minutes. You can specify the connection should be automatically reset if an error occurs in the remote database. On a reset the connection will close and reopen. This causes the database driver to reset. |
| On new record | Specify what should happen with new messages. This can be:
|