Description:
This step executes a SQL statement against the selected database; the 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 process definition named “executeSQLDef” and open it in designer mode.
- Drag a few “executeSQL” steps to the canvas.
- Connect the dots between the “Start” and the “executeSQL” steps, as shown above.
- Define a variable or a global to store the result.
- Click the first "executeSQL" step to configure its "Required" properties. Provide a name for the step. Select the connection string from the list. Enter the SQL SELECT statement. In this example, the statement includes a parameter (?), and the value is supplied via a variable reference. Select the result format as XML or JSON. Click the Save button. Note: Click the "AI Predict" button for the Copilot to add new process steps that match your process description.
- Click the first "executeSQL" step to configure its "Optional" properties. Provide a variable or a global to store the result. Select the result to return a single row or column (if necessary). Set the SQL command timeout value in seconds. Click the button to configure the SQL parameters and values. A pop-up window appears for configuration. Click the Add Row button to insert an empty row. Provide the SQL parameter and value. Click the Save button. Using the Add Row button, you can include multiple SQL parameters and values.
- Click the second "executeSQL" step to configure its "Required" properties. Enter a name for the step. Select the connection string from the list. Enter the SQL SELECT statement. Select the result format as XML or JSON. Click the Save button. Note: Click the "AI Predict" button to have the Copilot add new process steps that match your process description.
- Click the second "executeSQL" step to configure its "Optional" properties. Provide the variable or global reference to store the result. Select the result to return a single row or column (if necessary). Set the SQL command timeout value in seconds. Click the button to configure the SQL parameters and values. Click the Save button.
- Click the last "executeSQL" step to configure its "Required" properties. Provide a name for the step. Select the connection string from the list. Enter the SQL SELECT statement. In this example, the statement executes a stored procedure, and the parameter values (?) are supplied via a variable reference. Select the result format as XML or JSON. Click the Save button. Note: Click the "AI Predict" button for the Copilot to add new process steps that match your process description.
- Click the last "executeSQL" step to configure its "Optional" properties. Provide a variable or global reference to store the result. Select the result to return a single row or column (if necessary). Set the SQL command timeout value in seconds. Click the button to configure SQL parameters and values. A pop-up window appears for configuration. Click the Add Row button to insert an empty row. Provide the SQL parameter and value. Click the Save button. Use the Add Row button to include multiple SQL parameters and values.
- The “Logging” configuration is necessary for documentation and also measures workflow progress and percent complete. This is achieved by configuring the step state and percent fields individually, as shown below. Configure the “Logging” using the following properties.
- Save the process definition, create a new process instance, and execute it. Render the process instance. Click the process step. The step should execute the SQL query on the selected database server and return the result in JSON or XML format, based on the specified result configuration.
- To connect to a different database, specify the database name using the exact connection string.
Definition Sample:
You may download the sample definition(s) from the link provided and later import them (drag-and-drop) into 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 the import. Then, save the definition to confirm the changes.