Description:
This step executes a SQL statement against the selected database, if an select statement, returns the result set as HTML table.
Inputs
- connectionString – connection string to the database
- sqlStatement – SQL statement to execute
- variableName – variable/global to store the value from the SQL statement
- Connect to a different database - Connect to different database on the same connection string path
- SQLParameters - SQL parameter and values
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 definition called “getDataTableHtmlDef”
- Select the definition and click the “design” button
- Drag an “getDataTableHtml” step to the canvas
- Connect the dots between the start and “getDataTableHtml” step
- Define a variable/global to store the HTML result after execution
- Click on the "getDataTableHtml" step to configure its "Settings" properties. Provide a name to the step. Select the connection string from the list. Provide the select SQL SELECT statement. Provide the variable/global to store the HTML result.
- The “Logging” setting configuration is necessary for documentation and also measure the workflow progress and the percent complete. This is acheived 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 to view the properties. The step should get the data table result set as HTML table.
- To execute by parameters, Click on the "getDataTableHtml" step to configure its "Settings" properties. Provide a name to the step. Select the connection string from the list. Provide the select SQL SELECT statement as shown below - where the condition values are replaced with question mark. Provide the variable/global to store the HTML result.
- Click on the "getDataTableHtml" step to configure its "Advanced" properties.To connect to different database, mention the DB name which is accessible on the same connection string. Click on the SQL parameters button to configure.
- Define SQL parameter and values. Provide parameter name and values to be used in run time.
- Save the process definition, create a new process instance and execute. Render the process instance. Click on the process step to view the properties. The step should get the data table result set as HTML table from the other database as configured.
- Save the process definition, create a new process instance and execute. Render the process instance. Click on the process step. The step should should execute the SQL query in selected database server and returns result in JSON or XML based on the result format.
- To execute a stored procedure, Click on the "getDataTableHtml" 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. Provide the variable/global to store the HTML result.
- Click on the "getDataTableHtml" step to configure its "Advanced" properties.To connect to different database, mention the DB name which is accessible on the same connection string. Click on the SQL parameters button to configure. Define SQL parameter and values. Provide parameter name and values to be used in run time.
- Save the process definition, create a new process instance and execute. Render the process instance. Click on the process step to view the properties. The step should execute the stored procedure and get the result set as HTML table.
