Description:
This step executes an SQL statement against the selected database. If it is a select statement, it returns the result set as an HTML table.
Inputs
- connectionString – connection string to the database
- changeDatabase - Connect to a different database on the same connection string path
- sqlStatement – SQL statement to execute
- params - SQL parameters and values
- varGlobalHTML – variable/global to store the HTML table
Returns
- True – SQL statement executed successfully
- False – SQL statement failed to execute
Usage:
Example:
Let’s build and execute the “getDataTableHtmlDef” example.
- Create a new process definition called “getDataTableHtmlDef” and open the definition in designer mode.
- Drag a “getDataTableHtml” step to the canvas.
- Connect the dots between the “Start” step and “getDataTableHtml” steps, as shown above.
- Define a variable or a global to store the HTML result.
- Click the "getDataTableHtml" step to configure its "Required" properties. Provide a name for the step. Select the connection string from the list. Provide the SQL SELECT statement. Provide a variable or global reference to store the HTML result. Click the Save button. Note: Click the "AI Predict" button for the Copilot to add new process steps that match your process description.
- Click on the "getDataTableHtml" step to configure its "Optional" properties. A pop-up window is displayed for configuration. Click on the SQL parameters button to configure. A pop-up window is displayed for configuration. Click the Add Row (+) button to insert an empty row. Provide the SQL parameter name and value. Click the Save button. You may insert multiple SQL parameters by using the Add Row button.
- 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 below. Configure the “Logging” using the following properties.
- Save the process definition, create a new instance, and then execute it. Render the process instance. Click the process step to view its properties. The step should get the data table result set as an HTML table.
- To execute by parameters: Click the "getDataTableHtml" step to configure its "Required" properties. Provide a name for the step. Select the connection string from the list. Provide the SQL SELECT statement as shown below, where the condition values are replaced with question marks. Provide a variable or global reference to store the HTML result.
- Click on the "getDataTableHtml" step to configure its "Optional" properties. To connect to a different database, specify the database name accessible via the exact connection string. Click the SQL parameters button to configure. A pop-up window is displayed for configuration. Click the Add Row (+) button to insert an empty row. Provide the SQL parameter name and value. Click the Save button. You may insert multiple SQL parameters by using the Add Row button.
- Save the process definition, create a new instance, and then execute it. Render the process instance. Click the process step to view its properties. As configured, the step should get the data table result set as an HTML table from the other database.
- Save the process definition, create a new instance, and then 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, depending on the specified result format.
- To execute a stored procedure: Click the "getDataTableHtml" step to configure its "Required" properties. Provide a name for the step. Select the connection string from the list. Provide the stored procedure info as shown below, with question marks identified as parameters. Provide a variable or global reference to store the HTML result.
- Click on the "getDataTableHtml" step to configure its "Optional" properties. To connect to a different database, specify the database name accessible via the exact connection string. Click the SQL parameters button to configure. A pop-up window is displayed for configuration. Click the Add Row (+) button to insert an empty row. Provide the SQL parameter name and value. Click the Save button. You may insert multiple SQL parameters by using the Add Row button.
- Save the process definition, create a new instance, and then execute it. Render the process instance. Click the process step to view its properties. The step should execute the stored procedure and get the result set as an HTML table.
Definition Sample:
You may download the sample definition(s) from the link here and later import them (drag-and-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.