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

Result Properties

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 flow chart.

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.

If you need to maintain every row of the result for later use in the flow chart, 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.

1. A flow back to the database query action from the second action, to pick up the next row of the result.
2. Finally, a conditional flow with the condition "RESULT.result = false", which is followed once all rows in the result have been processed.

result

A boolean value that indicates whether more results are available from the query. If this value is true, then at least one more row is available in the result; if the value is false, then the result set has been exhausted (there are no more results available).

row

A Java List (java.util.List) representing the current row in the result. Column 1 of the result corresponds with element 0 in the list, column 2 with element 1, and so on.'

To access the second column of the current row of the result, therefore, you could call the following code on a pre- or post-script in an action after the database query action:

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:

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:

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:

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

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

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.