Description
The Run SQL Query activity fetches the results of a database query. To prevent SQL injection attacks, this activity prevents the command text input from being an expression, which could contain user input. Inputs should be supplied to the query with the Parameters property. If it is absolutely required to manually construct a query string using user input, the Run SQL Query Unsafe can be used.
This activity is intended to execute queries that return results. INSERT, UPDATE, and other statements that alter the database should be executed with the Run SQL Non-Query activity.
Major data providers, such as Oracle and Microsoft SQL Server, are supported. The full list of supported data providers is documented here. Certain providers, such as Oracle, must be configured before use. Connection strings defined in the database configuration file can be stored securely and reused in any workflow.
Usage
A workflow could use the Prompt activity to gather the name of a watershed. The Run Workflow activity could be used to pass this input to a server workflow.
Next, the Run SQL Query activity could be used to run the following query against an enterprise database:
▪SQL Server: "SELECT * FROM endangered_species WHERE watershed = @watershedInput"
▪Oracle: "SELECT * FROM endangered_species WHERE watershed = :watershedInput"
The user's input could be given to the Parameters input as the object { 'watershedInput': $userInput }.
Finally, the DataTable result could be parsed, formatted, and displayed to the end user with the Set Workflow Output and Alert activities.
For details on query syntax, see the documentation for your data provider.
Inputs
Provider Name |
Required Type: "Microsoft SQL Server" | "MySQL" | "ODBC" | "Oracle" | "SQLite" | String The data provider to use. Using an invalid provider name will cause an error. Certain providers, such as Oracle, must be configured before use. |
Connection String |
Optional Type: String The connection string to use to connect to the database. The connection string must match the format supported by the data provider. Examples for different database types can be found on https://www.connectionstrings.com/. If both the ConnectionString and ConnectionConfigName are set, the ConnectionString parameter will be preferred. |
Connection Config Name |
Optional Type: String The name of the key in the database settings configuration file. The connection string value corresponding to this key will be used to connect to the database. |
Command Text |
Required Type: String The SQL command to execute. Parameters should be used to provide input to the command. For query syntax, see the documentation of the appropiate data provider. This input must be literal text, as this prevents malicious user input from executing a SQL injection attack. If it is absolutely required to manually construct a query string using user input, the Run SQL Query Unsafe can be used. |
Parameters |
Optional Type: Object The parameters object to pass to the query. A JSON object composed of key/value pairs that match named parameters in the Command Text input. For example, the following SQL query would have a parameter object of the shape "{ 'difficultyParam' : '5.11a' }": ▪SQL Server: "SELECT * FROM climbs WHERE difficulty = @difficultyParam" ▪Oracle: "SELECT * FROM climbs WHERE difficulty = :difficultyParam" Queries made with the ODBC provider can only take positional parameters, signified by question marks. The order the parameters are defined in the JSON object will be used as the positional order provided to the ODBC query command. For more information on this limitation, see here. For details on query syntax, see the documentation for your data provider. |
Command Timeout |
Optional Type: Number The number of milliseconds to wait before cancelling the query. If not specified, the default timeout for the given data provider is used. |
Outputs
dataTable |
Type: DataTable A data table representing the results returned from the query. The data table's 'Rows' property can be indexed by both row number and column. For example, the expression =$sqlQuery1.datatable.Rows[2]['GRADE'] will retrieve the 'GRADE' column of the third row of the results. |
Properties
For information about the ID, Display Name, and Description properties, see Properties Common to all Activities.
Connectivity Requirements
This activity does not work when the device has intermittent connectivity to the network.
See also...
Database Connections for Server Workflows
