Database Triggers and Actions - Database Query Action
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:
- The database query action executes the query.
- A conditional flow (with a 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.
- A flow back to the database query action from the second action, to pick up the next row of the result.
- 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 |
|---|---|---|---|---|
| RESULT | result | boolean | Indicates whether there are more results available in the underlying JDBC result set. | boolean result = flowContext.get("RESULT").result; System.out.println("More results? " + result); |
| RESULT | row | List<Object> | The current row in the result set. Each entry in the list is one column from the row. | List row = flowContext.get("RESULT").row; for (Object column : row) { System.out.println("Column: " + column); } |
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 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. |
| >= | Greater 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.