Automation Action: Lookup From A Database

Reads records from a database and assigns returned column values to multiple ThinkAutomation variables.

Select a Database Type to connect to from the list. You must specify a Connection String that ThinkAutomation will use to open the database. Click the ... button to build the connection string. Click the Test button to verify that ThinkAutomation can connect to the database.

Specify the Max Rows to read.

Enter the SQL Statement to use to query records from the database. The SQL Statement can contain Parameters. Eg:

SELECT * FROM Person WHERE Id = @Id              

For any Parameters you must complete the Parameters grid. Specify the Name, Type & Value for each parameter used. Parameter values can be set to %variable% replacements. See: SQL Parameters. Click the Test button to verify the query.

Assignments

Column Assignments

You can assign individual column values to ThinkAutomation Variables (optional).

In the Column Assignments grid you can map database columns returned from the query to ThinkAutomation variables.

In the Column Name/Index column specify a database field name or position number from the SELECT statement.

In the Assign Value To column select a ThinkAutomation Variable that you want the database column value assigned to.

If the database query returned multiple rows, then the first row returned will be used for variable assignment. If no rows are returned then assign-to variables will not be assigned.

Optional Assignments

You can assign the row count to a ThinkAutomation variable. Select a variable from the Assign Row Count To list (optional).

Assign All Rows/Columns To A Variable In JSON Format

You can assign all rows/columns returned by the query as JSON text to a ThinkAutomation variable. Select a variable from the Assign Json To list (optional).

Each row returned by the query will be a JSON value. For example:

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

If the query returned multiple rows then the JSON will be set to an array.

You can then perform other actions on this value - or pass it to another Automation using the Call Automation action. You can use the Convert JSON To Html action to convert the JSON to a HTML table if the data needs to be sent or viewed in human readable format.

Assign All Rows/Columns To A Variable In CSV Format

You can assign all rows/columns returned by the query as CSV text to a ThinkAutomation variable. Select a variable from the Assign CSV To list (optional).

For the JSON/CSV content you can use the Read/Write Text File action to save the content to a file for use on subsequent actions (Convert Document, Add Attachment to outgoing email etc.).

Converting The CSV To Displayable Format

If you want to use the Database Lookup to lookup multiple rows that you can then return in your Automation for a user to view, you can use the Set Variable action with the Convert CSV To Markdown Table option. Eg:

CSV = Lookup From A Database MySQL on world SELECT * FROM world.country
Markdown = Convert CSV To Markdown Table(%CSV%)
Return %Markdown%              

When run in the Studio this will display the table when used via the Send Message option. When used via a Web Form or API Message Source the markdown will be automatically converted to HTML.

Reading Blob Data

If any columns return binary data (data types: blob, binary, varbinary etc) the data will be returned to the variable in Base64 format. If you want to write the base64 data to a file you can use the File Operation action with the Write Binary File From Base64 String operation.

Lookups From The Embedded Document DB

This action can also be used to perform lookups using the Embedded Document DB. Select Embedded Database when selecting the Database Type. See: Embedded Data Store action