Description
The Run SQL Non-Query activity executes a database command and returns the number of rows affected. 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.
This activity is intended to execute queries that alter the database, such as INSERT or UPDATE. Queries that return results should be executed with the Run SQL 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 client workflow could use the Display Form activity to gather information about a construction project from a user. 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 query 'INSERT INTO construction_projects VALUES (@id, @address)' against an enterprise database. The user's input could be given to the Parameters input to be substituted for the named parameters in the VALUES object. Finally, the rowsAffected output could be checked to validate the row was inserted, and a success message could be returned to the user with the Set Workflow Output and Alert activities.
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. |
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
rowsAffected |
Type: Number The number of rows affected by an UPDATE, INSERT, or DELETE statement. For example, an UPDATE query that matched and altered five rows would return 5. For other types of statements, a value of -1 is returned. |
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
