Run SQL Query

Audience: Citizen Developer

Skill Prerequisites: SQL

This action executes a SQL statement, optionally capturing the output for use in subsequent actions. By default, it targets the SQL Server instance of the application, however, it is not restricted to SQL Server and can run against any database which supports ODBC connections.

A recent enhancement introduces optional transaction support to the SQL execution process, ensuring a safer and more consistent outcome by automatically rolling back any changes if an error occurs within a script. Previously, executing multiple SQL statements sequentially would commit changes until an error was encountered, potentially leaving partial updates in the database. This change enhances data integrity and reliability, though it may affect systems that expected the previous behavior. Users should be aware that unsuccessful scripts no longer leave intermediate database alterations intact. Use Transactions defaults to ON.

Typical Use Cases

• Query the database for a specific single row of data
• Insert data into tables
• Pre-load data into a form
• Pull data from external databases

Don't use it to

• Load multiple sets of data - use Create List from SQL instead
• Query entities - use entity specific actions instead
• Update system tables - it might have unwanted effects on functionality, use specific actions instead

Transaction Behavior

The Run SQL action can automatically wrap SQL execution inside a database transaction.

When transactions are enabled:

• All SQL statements execute as a single unit of work
• If any statement fails, all previous changes are automatically rolled back
• This helps prevent partial updates and improves data consistency

When transactions are disabled:

• SQL statements execute without transaction protection
• Earlier statements may remain committed even if a later statement fails
• Certain SQL commands that are not allowed inside transactions (such as SHRINK DATABASE) can be executed

Input Parameter Reference

Parameter	Description	Supports Tokens	Default	Required
Database	Select a database for connection. If left unselected, the main application database will be used.	No	Application database	No
Override Connection String	The connection string to the database. Use this field to override the default connection string.	Yes	Application database	No
Query Timeout	This parameter determines how long in seconds it takes until a timeout happens if the database doesn't respond. It doesn't accept values under 10 seconds. Defaults to 600 seconds.	Yes	600	No
Use Transactions	When enabled, the SQL script is executed within a database transaction to ensure data integrity. If the script fails, all changes are rolled back. Turn this OFF to run commands not allowed in transactions (e.g., SHRINK DATABASE), risk of partial updates and deadlocks.	No	ON	No
SQL Query	The SQL query you want to execute on the database. It's recommended to use the Bind Tokens parameter to avoid potential SQL injection.	Yes	Unset	Yes
Bind Tokens	Allows using tokens as variables in the SQL Query to avoid SQL injection. References the variable in the SQL Query as @VariableName.	Yes	Unset	No
Show Errors	Enables showing the original SQL error if something goes wrong.	No	Unset	No

Output Parameters Reference

Parameter	Description
Store Result	Token name in which the data returned by the query will be stored. Generates a syntax like [VariableName:SQLColumn].
Extract Columns	Maps certain columns directly to tokens instead of using the returned column names for token generation.

Events Reference

Event Name	Description
On Error	Executes a list of actions if an error occurs during the execution. The exception details can be accessed via the [Exception], [ExceptionType], [ExceptionMessage], and [ExceptionStack] tokens.

Examples

tip

To understand how to use the below examples, please see Running Examples.

1. Selecting the first user and storing its data in the token "selecteduser"

This action selects all the details of the first user and stores them in a token named user for further use.

{
    "Title": "Run SQL Query",
    "ActionType": "RunSql",
    "Parameters": {
        "ConnectionString": "",
        "QueryTimeout": "",
        "SqlQuery": "SELECT * FROM Users\nWHERE UserId=1",
        "BindTokens": "",
        "OutputTokenName": "selecteduser",
        "ExtractColumns": "",
        "OnError": [],
        "ShowErrors": false
    }
}

2. Update the First Name of the first user to "Aaron"

This action will update the First Name of the first user. Note that no OutputTokenName is set, as this action has no output.

{
    "Title": "Run SQL Query",
    "ActionType": "RunSql",
    "Parameters": {
        "ConnectionString": "",
        "QueryTimeout": "",
        "SqlQuery": "UPDATE Users\nSET FirstName='Aaron'\nWHERE UserID=1",
        "BindTokens": "",
        "OutputTokenName": "",
        "ExtractColumns": "",
        "OnError": [],
        "ShowErrors": false
    }
}

Revised 11/04/2025