Database

The Database message source type can be used to trigger automations based on database changes (either new or updated rows). Rows returned from a database query will be passed to an Automation for processing. Select the Database Type (Microsoft SQL Server, MySQL, SQLite, Oracle, PostgreSQL, DB2, Firebird or OLDBC/OLEDB). Enter the Connection String or click the ... button to build and test the connection. See: Database Connection Notes for more details about supported databases.

Enter the SQL Statement to query against the database to retrieve rows. For example:

SELECT * FROM Person WHERE PersonId > @Id ORDER BY PersonId

The SQL Statement can contain Parameters. You should then complete the Parameters table to provide each parameter type and value.

The query should include at least one column that provides a unique value. Enter this column name in the Unique Id Column box. ThinkAutomation will ensure that the same record is not processed more than once based on the Unique Id Column value. If no Unique Id Column is used then the same records could be processed multiple times if they are not filtered out using the WHERE clause (or deleted).

ThinkAutomation automatically caches the last Unique Id Column value each time it requests data from the database. You can use this cached value as a Parameter value by setting a parameter value to %LastDatabaseId%.

In the above example the Unique Column Name is PersonId. We set the @Id parameter value to %LastDatabaseId%. This means that each time ThinkAutomation requests data from the database it only requests records with a higher PersonId since the last request (making the query much faster).

Test & Create Automation

You should test your query before saving the Message Source. Click the Test button. When you use the Test option on a new Message Source, ThinkAutomation will ask if you want to create a new Automation to process the records. It will then create a new Automation with Extract Field actions that match the columns returned by the query.

Creating The ThinkAutomation Message

Each row returned from the database query will be passed to the Automation for processing. You can choose to pass all columns to the Automation or a single column. Set the Assign All Columns To Message Body to send all columns returned by the query.

The database row will be passed to the Automation in the following format:

{
  "PersonId": 1,
  "PersonType": "EM",
  "NameStyle": false,
  "Title": "",
  "FirstName": "Ken",
  "MiddleName": "J",
  "LastName": "Sánchez",
  "Suffix": "",
  "EmailPromotion": 0,
  "AdditionalContactInfo": "",
  "ModifiedDate": "2009-01-07 09:00:00"
}

If you used the Test option then Extract Field actions will have been created automatically to extract each column value.

You can optionally set the message Subject, From Address and Date. The Subject will default to '%TableName% (%LastDatabaseId%)' - where %TableName% will be the table name extracted from the SQL statement used for the query. You can change this and include text and column values for the current row. To use column values, use %ColumnName% replacements. For example:

Record Id %LastDatabaseId% For %FirstName% %LastName% would set the subject to 'Record Id 1 For Ken Sánchez'.

The Message Date will default to the current date & time. You can optionally set it to a column value. For example, %ModifiedDate%.

---