Description:

The Update Variables from SQL workflow step executes a SQL statement against a configured database connection and maps the SQL column values returned by the statement to FlowWright variables or globals.

Unlike the Update Record Value step, which updates a specific database field, this step retrieves SQL results and assigns them to workflow variables or globals.

The step supports:

• Executing a SQL statement
• Mapping SQL column values to FlowWright variables or globals
• Selecting a database connection
• Optionally connecting to a different database
• Supplying SQL parameters
• Using a multiline SQL statement
• True and False return paths

Usage:

The Update Variables from SQL step is typically used when a workflow needs to retrieve one or more values from a database and make them available to subsequent workflow steps.

The SQL statement determines which data is returned, while the variable/global mapping determines where the returned values are stored within the workflow.

For example, a workflow can:

• Retrieve customer information from a database.
• Retrieve an order status and amount.
• Obtain configuration values from a database.
• Retrieve calculated values for use in subsequent workflow processing.
• Populate workflow variables from an existing database record.
• Retrieve multiple SQL columns and map them to multiple workflow values.
• Use SQL parameters when the query depends on workflow data.

The step therefore provides a bridge between database data and FlowWright workflow variables or globals.

Typical Workflow Suggestions

Retrieve Customer Information

Use the step to retrieve customer information needed by subsequent workflow activities.

Example:

Receive Customer ID → Update Variables from SQL → Generate Customer Document

The SQL statement can retrieve the required customer fields and map them to workflow variables.

Retrieve Order Information

Use Update Variables from SQL to obtain order information before continuing with order processing.

Example:

Receive Order ID → Update Variables from SQL → Process Order

The returned database values can then be used by downstream workflow steps.

Retrieve Record Status

Use the step to retrieve the current status of a database record.

Example:

Receive Record ID → Update Variables from SQL → Evaluate Status

The SQL result can be mapped to a workflow variable for subsequent processing.

Populate Workflow Variables

Use the step when values required by a workflow already exist in a database.

Example:

Start → Update Variables from SQL → Business Processing

This avoids the need to enter the same information manually into the workflow.

Retrieve Multiple Database Values

Use the variable/global mapping when a SQL statement returns several columns needed by the workflow.

Example:

Get Customer ID → Update Variables from SQL → Process Customer

The SQL result can provide multiple pieces of information for subsequent activities.

Parameterized Database Queries

Use SQL parameters when the SQL statement needs values supplied by the workflow.

Example:

Capture Order ID → Update Variables from SQL → Retrieve Order → Continue

The workflow can supply the relevant value through the configured SQL parameter mapping.

Retrieve Configuration Data

Use the step when workflow behavior depends on values maintained in a database.

Example:

Start → Update Variables from SQL → Apply Configuration → Continue

This can allow workflow processing to use database-maintained configuration values.

Example:

Let’s build and execute the “updateVariableWithSQLDef” example.          

• Create a new process definition called “updateVariableWithSQLDef” and open the definition in designer mode. 
• Drag an “updateVariableWithSQL” step to the canvas.
• Connect the dots between the “Start” and “updateVariableWithSQL” steps, as shown above.
• Select the line between the steps to configure the “Connection Properties”. The default property values are “None, True, False, Error, and Evaluate”. Depending on the step’s purpose, additional values are available for configuration. 
• Define a variable or a global to store the SQL column values. 
• Click the “updateVariableWithSQL” step to configure its “Settings” properties. Provide a name for the step. Click the button to map SQL fields to variables or globals. Select the connection string from the drop-down list. Enter the SQL SELECT statement. 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 button to assign SQL fields to variables or globals. A pop-up window appears for configuration. Click the Add Row button to insert an empty row. Enter the variable name and value. Click the Save button. You can add multiple variables and values by using the Add Row button. 

• Click the “updateVariableWithSQL” step to configure its “Optional” properties. Provide a different DB name (other than FlowWright) that is available in the connection string. By default, the step connects to the FlowWright database. Click the Save button. 

• 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 in the images below. Configure the “Logging” using the following properties.

• Save the process definition, create a new instance, and execute it. Render the process instance. Click the process step to view its properties. The step should update the variable or globals with the values from the DB.

Tips:

• Make sure the SQL statement returns the columns required by the variable/global mapping.
• Configure Assign SQL column values to variables/Globals carefully so that each SQL result is mapped to the intended workflow value.
• Select the correct Connection string for the database containing the required data.
• Use SQL parameters when the SQL statement depends on values supplied by the workflow.
• Verify that the database connection has permission to execute the configured SQL statement.
• Test the SQL statement independently before incorporating it into a production workflow.
• Use meaningful step names such as “Retrieve Customer Details”, “Get Order Status”, or “Load Database Configuration” to make the workflow easier to understand.
• If a different database must be accessed, configure Connect to the different database appropriately.
• Ensure that the SQL result structure remains compatible with the configured variable/global mappings when the workflow is changed, or the database schema evolves.

Notes:

The UpdateVariablesWithSQL step is defined in the Database category with the internal name updatevariableswithsql and display name Update variables from SQL.

The workflow step is implemented by the FlowWright.Workflow.UpdateVariablesWithSQL namespace in FlowWright.Workflow.dll and is defined as a Process step. It supports 2 incoming connections and 2 outgoing connections.

The step contains five configurable properties. Assign SQL column values to variables/Globals, Connection string, and Enter SQL Statement are required. Connecting to a different database and SQL parameters are optional.

The Assign SQL column values to variables/Globals property uses the enterVariables data type, while Connection string uses selectConnectString.

The Enter SQL Statement property uses a multiline text box, and SQL parameters use the clsSQLParams data type.

The XML does not expose separate properties for table name, field name, WHERE field, or WHERE value. Instead, the database operation is defined through the SQL statement and its optional SQL parameter mapping.

Definition Sample:

You may download a sample definition when provided and import it into your FlowWright Process Definition.

Note: Verify and complete any missing configuration after importing a sample, including:

• Variable/global mappings
• Connection string
• SQL statement
• SQL parameters
• Outgoing workflow connections
• Environment-specific database settings

After verifying the configuration, save the Process Definition before execution.

Click here to download the sample file.