Automation Action: Update A Database Using Custom SQL

Insert or Update a row in a database using custom SQL.

This Action allows you to insert or update a row in a database based on the results of a select statement.

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.

The Insert tab is used to enter any valid SQL statement. You can also optionally enter statements in the Update and Select tabs.

If a select statement is entered in the Select tab, then the SQL entered in the Update tab is executed if the select returns one or more rows. If no rows are returned then the SQL entered in the Insert tab is executed.

The select, insert & update statements can contain parameters (using @parametername).

You must specify the Name, Type & Value of each parameter used. Parameter values can be assigned to %variable% replacements. See: SQL Parameters.

It is not recommended that you directly specify %variables% in your SQL statements. You should use parameters instead and set the parameter values to each %variable%. This will ensure the database value is set correctly. It is also more secure. If you do use %variables% directly in your SQL statement you must ensure the value is correctly escaped (any single quotes must be replaced with two single ) and string values are enclosed in single quotes.

For the Insert & Update statements you can assign the rows affected to a variable.

Blob Data (Saving File Contents)

For parameters with type Blob - if the Value assigned is a file path, then the file contents are read and the binary data is assigned to the Value.

Saving Attachments

If you want to store message attachments to a database you can use a For..Each action to loop on Attachment. Inside the loop set variables for the Filename and Temporary Location values. You can then assign these variables to the relevant database parameter values. See: Example.