Skip to end of metadata
Go to start of metadata

This action executes an SQL query against a database and makes the result set available to the running workflow.

Result

The results of the database query that you execute are returned through the flow context. Results are returned one row at a time, along with a boolean (true/false) value indicating whether there are still more results available.

The first time the database query action executes, it will return the first row of the result (if any) as the result property "row". To access the next row in the result, you must add a flow back to the database query action. When this flow re-enters the action, it will simply pick up the next row of the result (rather than re-executing the action). This will continue until there are no more rows left in the result, at which point the result property "result" will be set to false, and you can take appropriate measures to continue on in the workflow.

The underlying database connection is closed when a flow returns the query action, and there are no more results to process. Because of this, even if the query only returns a single row, there must eventually be a loopback flow that instructs Flux to close the connection.

Because of this behavior, the query result must be processed one row at a time. A typical use of the database query action will look something like this:

  1. The database query action which executes the query.
  2. A conditional flow (with condition "RESULT.result = true" or similar) that flows into a new action (such as a Java Action or Process Action) that processes the current row of the result.
  3. A flow back to the database query action from the second action, to pick up the next row of the result.
  4. Finally, a conditional flow with either the condition "RESULT.result = false", or the Else condition, which is followed once all rows in the result have been processed.

If you need to maintain every row of the result for later use in the workflow, this action should record each row using a flow chart variable that can be accessed later (for example — you might use a flow chart variable that is a Java List, which each entry in the List containing one row of the result). This is up to the user to implement as Flux does not automatically store the values of all rows in the result, only the current row.

The following table contains more information on the Database Query Action result fields.

Flow Context Variable
Field
Java Type
Description
Prescript / Postscript Example
RESULTresultbooleanIndicates whether there are more results available in the underlying JDBC result set.
RESULTrowList<Object>The current row in the result set. Each entry in the list is one column from the row.

Passing Results with a Runtime Data Map

You can use a Runtime Data Map to copy the current row of the result into a variable (for future reference or to reuse the data later in the workflow).

To copy the row you can use a data map like:

Conditional Flow Syntax

As mentioned above in #Result Properties, the database query action returns its result one row at a a time, and you can use the result property "result" to determine whether there are more rows available. Therefore, you can use the flow condition:

RESULT.result = true

A flow with this condition is only followed if there are one or more rows remaining in the result. Likewise, a flow with the condition:

RESULT.result = false

Is only followed once the result is exhausted and there are no more rows available.

You can also create conditional flows that depend on the values of one or more columns in the current row of the result. For example, to access the first column in the current row of the result, you would use the syntax:

COLUMN(1)

Likewise, COLUMN(2) would represent the value of the second column, and so on. In general, you can access the value of column N in the current row of the result using COLUMN(N), where N is a number greater than or equal to one.

For conditional flows that evaluate column values in the current row of the result, you can use the following SQL relational and boolean operators: <, <=, =, <>, >=, >, AND, OR, NOT. See the table below for examples demonstrating how each of these operators is used.

Examples

Operator

Description

Example

Result

<

Less Than

COLUMN(1) < 500

The flow is followed if the first column of the current row in the result is less than 500.

<=

Less Than or Equal To

COLUMN(1) <= 500

The flow is followed if the first column of the current row in the result is less than or equal to 500.

=

Equals

COLUMN(1) = 500

The flow is followed if the first column of the current row in the result equals 500.

<>

Not Equal To

COLUMN(1) <> 500

The flow is followed if the first column of the current row in the result is any value other than 500.

>=

Greather Than or Equal To

COLUMN(1) >= 500

The flow is followed if the first column of the current row in the result is greater than or equal to 500.

>

Greater Than

COLUMN(1) > 500

The flow is followed if the first column of the current row in the result is greater than 500.

AND

TRUE if all conditional expressions evaluate to TRUE

COLUMN(1) < 500 AND COLUMN(2) > 500

The flow is followed if the first column of the current row in the result is less than 500 AND the second column of the current row in the result is greater than 500.

OR

TRUE if any conditional expression evaluates to TRUE

COLUMN(1) < 500 OR COLUMN(2) > 500

The flow is followed if the first column of the current row in the result is less than 1000 OR the second column of the current row in the result is greater than 5000.

NOT

TRUE if the conditional expression evaluates to FALSE

NOT COLUMN(1) > 500

The flow is followed if the first column of the current row in the result is not greater than 500 (the value is less than 500).

Operators are evaluated from right to left, and relational operators are always applied before boolean operators. For example, consider the following condition:

NOT COLUMN(1) = 50 OR COLUMN(1) = 100

This condition would be evaluated in the order:

  1. COLUMN(1) = 100
  2. COLUMN(1) = 50
  3. COLUMN(1) = 50 OR COLUMN(1) = 100
  4. NOT COLUMN(1) = 50 OR COLUMN(1) = 100

So if the first column of the current row in the result contained the value 50, the condition would be evaluated like:

  1. NOT COLUMN(1) = 50 OR false
  2. NOT true OR false
  3. NOT true
  4. false

In this case the flow condition would ultimately evaluate to "false" and the flow would not be followed.

  • No labels