Description:
Executes an SQL statement against the selected database, if an select statement, returns an XML record set.
Usage:
Example:
Let’s build and execute the executeSQLDef example.
- Create a new definition called “executeSQLDef”
- Select the definition and click the “design” button
- Drag an executeSQL step to the canvas
- Connect the dots between the start and executeSQL step
- Click on the “executeSQL” step to configure its properties
- Configure the following values for the properties as shown on the below graphic
- Save the workflow definition, create a workflow instance and execute. Step should execute the SQL query in selected database server and returns result in JSON or XML based on the result format.
- Configure the following values to utilize the advanced features as shown on the below graphic
- Configure the SQL Parameters as shown on the below graphic
- The SQL Statement input can also accept a valid string with parameters (?) as shown above. The parameters are mentioned separately in a different UI. Take note to represent the parameters name and order of appearance as per the SQL string.
- Connect to a different database - mention the name of the other database if the SQL string requires to connect to (on the same connection string path)
- Variable/Global to store the value - mention the name of the variable/global to store the result
- Return a single result - Enable "Yes" option if the result expected is 1 row & 1 colum and this value is directly stored (as is) in above variable/global. By default "No" option, stores the result (i.e. multiple rows/columns) in JSON / XML format as configured above.
- Command timeout value - Default 30 secs. Gets or sets the wait time (in seconds) before terminating the attempt to execute a command and generating an error.
- Save the workflow definition, create a workflow instance and execute. Step should execute the SQL query in selected database server and returns result in JSON or XML based on the result format.