Run SQL Non-Query Activity

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

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.