Automation Action: Open Database Reader

Opens a connection to a database for use with For.. Each Actions

The Open Database Reader Action opens a connection to a database using a SQL query. The connection remains open during Automation execution. You can then create a For..Each loop to read each row returned by the query.

Enter the Reader Name. This is a unique name for the data reader. A single Automation can open multiple data readers - each having a unique name.

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.

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

SELECT * FROM Person WHERE PersonType = @Type              

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.

Now create a For..Each Action. Specify the For Each option as Data Reader Row In and select the Reader Name.

You can then select a variable from the Assign Data Row Json To selector to be assigned the current row Json. The current row Json will be set for each record returned from the query. For example:

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

You can then perform other actions on this value - or pass it to another Automation using the Call Automation action.

The For..Each loop will continue until all rows from the query have been read or an Exit Loop action is used.

The Open Database Reader action is designed for queries that return a small number of rows (less than 10000). For example: To read a list of email addresses from a database and send an email to each. If your query will return many rows consider using the Database message source type instead. You can also use the Set Logging Level action before your For..Each loop. Set the logging level to Minimal so that only errors are logged during the loop. This will improve performance.

A For..Each - Data Reader Row In loop block cannot contain the following actions:

• Wait For User Response
• Wait For Webhook
• Twilio Wait For SMS Reply
• Twilio Send SMS Message (where waiting for status is enabled)
• Twilio Make A Telephone Call

The reason is that the Automation will exit during the waiting phase of the above Actions (allowing the next message to be processed). The underlying data source for the Open Database Reader action may change during this waiting period causing the loop to become invalid.