Description:
This step executes a SQL statement against the selected database; if a select statement returns an XML record set.
Inputs
- singleResult – Returns a single result
- connectionString – connection string to the database
- changeDatabase - Connect to a different Database
- sqlStatement – SQL statement to execute
- variableName – variable/global to store the value from the SQL statement
- resultFormat – Result Format (XML/JSON)
- Connect to a different database - Connect to different database on the same connection string path
- Return a single result - Select YES for 1 row & 1 column expected result
- SQLParameters - SQL parameter and values
- CommandTimeOut – Command timeout value (default 30 secs)
Returns
- True – SQL statement executed successfully
- False – SQL statement failed to execute
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
- Define a variable/global to store the result after execution
- Click the "executeSQL" step to configure its "Settings" properties. Provide a name to the step. Select the connection string from the list. Provide the SQL SELECT statement. The statement in this example is configured with a parameter (?), and the value is supplied through a variable reference. Select the result format as XML or JSON.
- Click the "executeSQL" step to configure its "Advanced" properties. Provide a variable/global to store the result. Select the result to return a single row or column (if necessary). Select the SQL command timeout value in seconds. Define SQL parameters and values.
- Define SQL parameters and values. Provide parameter names and values to be used in run time.
- The “Logging” setting configuration is necessary for documentation and also measures the workflow progress and the percent complete. This is achieved by configuring the step state and percent fields individually, as shown in the images below. Configure the “Logging” using the following properties.
- Save the process definition, create a new process instance, and execute.
- Render the process instance. Click on the process step. The step should execute the SQL query on the selected database server and return the result in JSON or XML based on the result format.
- To execute a stored procedure, Click on the "executeSQL" step to configure its "Settings" properties. Provide a name to the step. Select the connection string from the list. Provide the stored procedure info as shown below - with question marks identified as parameters. The statement in this example is configured with a parameter (?), and the value is supplied through a variable reference. Select the result format as XML or JSON.
- Define SQL parameters and values. Provide parameter names and values to be used in run time.
- To connect to a different database, mention the DB name, which is accessible on the exact connection string.
Definition Sample:
You may download the sample definition(s) from the link here and later import it (drag-drop) to your FlowWright Process Definition (XML file) or Form Definition (HTML file) page.
NOTE: Please verify and complete the process steps for any missing configurations, such as file path references and database connections after import. Then, save the definition to confirm the changes.