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.
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 Load Entities (SQL) instead
- Query entities - use entity specific actions instead
- Update system tables - it might have unwanted effects on functionality, use specific actions instead
|Server Request||A low-level action that allows for any kind of HTTP request to be performed.|
Input Parameter Reference
|Other Connection String||The connection string to the database. If left empty, the query will be executed on the main application database. You can connect to any database which supports the ODBC standard. For example, if you want to connect to a Oracle DB, you would specify ||Yes||No|
|Query Timeout||This parameter determines how long in seconds it takes until a timeout happens if database doesn't respond. It doesn't accept values under 10 seconds. If left empty, it will default to 600 seconds. You should change this when the query duration might exceed the default of 10 minutes. Be careful when leveraging this options since your application will wait for the longer running queries before continuing execution. A balance must be found with the timeout duration since waiting for a query result in a scheduled job might be acceptable but might pose serious wait times in the case of form.||Yes||No|
|SQL Query||The SQL query you want to execute on the database. While the query does accept tokens, we recommend using the ||Yes||Yes|
|Bind Tokens||This parameter allows you do use tokens as variables in the SQL Query field in order to avoid SQL injection. For example, if you want to use the token ||Yes||No|
|Show Errors||If this option is used, the error shown when something goes wrong will be the original SQL error.||No||No|
Output Parameters Reference
|Extract Columns||This option is useful when you map certain columns to certain tokens instead of using the column names brought back by the results. If you just want to inject the result data into fields/tokens, this is the most efficient way.|
|Store Result||The token name in which the data returned by the query will be stored. You should leverage this method of storing the result you don't need to map the columns to any fields or tokens and just want to manipulate the results for various uses. This will generate a syntax like |
|On Error||When an error is thrown during the execution of this action, it will trigger the execution of the list of actions specified for this event. An error event won't stop the execution of further actions. If the |
1. Selecting the first user and storing it's data in the token "user"
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",
"SqlQuery": "SELECT * FROM Users\nWHERE UserId=1",
2. Update the First Name of the first user to "Aaron"
This action will update the First Name of the firs user. Please note that no
OutputTokenName is set, as this action has no output.
"Title": "Run SQL Query",
"SqlQuery": "UPDATE Users\nSET FirstName='Aaron'\nWHERE UserID=1",