Database Triggers and Actions - Database Stored Procedure Action
The Database Stored Procedure Action calls a stored procedure, then returns the result of the procedure (if any) one row at a time as its “RESULT” in the flow context.
Properties
Registered IN-OUT Parameters
This property is a Map that specifies both the position and type of the stored procedure’s registered IN-OUT parameters.
The key of each entry in the map should represent the 1-based index number of an IN-OUT parameter. So, for example, the first IN-OUT parameter would have a key of 1 in this map, the second a key of 2, and so on. The index of a parameter is determined by the location of the ‘?’ character in the stored procedure call.
The value of each entry is a numerical value representing that parameter’s SQL type (the type of the parameter at the index given in the map key). The table below lists each SQL type and its corresponding numerical value:
| SQL Type | Numeric Value |
|---|---|
| ARRAY | 2003 |
| BIGINT | -5 |
| BINARY | -2 |
| BIT | -7 |
| BLOB | 2004 |
| BOOLEAN | 16 |
| CHAR | 1 |
| CLOB | 2005 |
| DATALINK | 70 |
| DATE | 91 |
| DECIMAL | 3 |
| DISTINCT | 2001 |
| DOUBLE | 8 |
| FLOAT | 6 |
| INTEGER | 4 |
| JAVA_OBJECT | 2000 |
| LONGVARBINARY | -4 |
| LONGVARCHAR | -1 |
| NULL | 0 |
| NUMERIC | 2 |
| OTHER | 1111 |
| REAL | 7 |
| REF | 2006 |
| SMALLINT | 5 |
| STRUCT | 2002 |
| TIME | 92 |
| TIMESTAMP | 93 |
| TINYINT | -6 |
| VARBINARY | -3 |
| VARCHAR | 12 |
This means, for example, that if the stored procedure has IN-OUT parameters at index 3 and 5, with SQL types of VARCHAR and INTEGER respectively, your IN-OUT parameters setting would be a map that looked like:
3 12
5 4
Registered OUT Parameters
This property is a Map that specifies both the position and type of the stored procedure’s registered OUT parameters.
The key of each entry in the map should represent the 1-based index number of an OUT parameter. So, for example, the first OUT parameter would have a key of 1 in this map, the second a key of 2, and so on. The index of a parameter is determined by the location of the ‘?’ character in the stored procedure call.
The value of each entry is a numerical value representing that parameter’s SQL type (the type of the parameter at the index given in the map key). The SQL Types table above (under the heading “Registered IN-OUT Parameters”) lists each SQL type and its corresponding numerical value.
For example, if the stored procedure has OUT parameters at index 3 and 5, with SQL types of VARCHAR and INTEGER respectively, your OUT parameters setting would be a map that looked like:
3 12
5 4
Registered OUT Parameter SQL Type
This property sets the numerical value of the registered OUT parameter’s SQL type (The SQL Types table above, under the heading “Registered IN-OUT Parameters”, lists each SQL type and its corresponding numerical value) in the common case where there is only one registered OUT parameter and it is the last parameter in the stored procedure signature. If you use this property, you do not need to set the registered OUT parameters property.
In other words, setting this property is equivalent to setting the registered OUT parameters property with a key of “1” and the value entered for this property.
Stored Function Result SQL Type
If this is a stored function (rather than procedure) call, this property sets the numerical value of the registered OUT parameter’s SQL type (The SQL Types table above, under the heading “Registered IN-OUT Parameters”, lists each SQL type and its corresponding numerical value) in the common case where there is only one registered OUT parameter and it is the last parameter in the stored procedure signature. If you use this property, you do not need to set the registered OUT parameters property.
In other words, setting this property is equivalent to setting the registered OUT parameters property with a key of “1” and the value entered for this property.
Parameters
A list of parameters that are supplied to a stored procedure call. The first element in the list corresponds to the first value in the SQL parameter list, the second value to the second expected parameter, and so on.
Stored Procedure Call
The signature of the stored procedure call is used to invoke a stored procedure on a database.
For a stored procedure call that returns no result and takes no parameters, this might look like:
{call MYPROCEDURE}
Depending on the database you are accessing, result types, IN parameters and OUT parameters may need to be marked with the ‘?’ character in the stored procedure call. This may look like:
{? = call MYPROCEDURE(?, ?)}
Where the first ‘?’ character represents the result, the second represents an IN parameter, and the third an OUT parameter.
Because the procedure call may look different depending on your database, we strongly suggest consulting the documentation for your database while configuring the stored procedure call setting here.
Result Properties
When this action runs, there are potentially two kinds of output: OUT parameters and a JDBC result set.
OUT parameters return data from the stored procedure after it is called. They must be registered before the stored procedure is called, and they are made available in the result object. If there are no OUT parameters registered, no outputs are made available. Not all stored procedures return data through OUT parameters.
Additionally, the stored procedure may return a JDBC result set. If it does, it is returned as a facade over a JDBC result set. The facade exposes the first row of data returned by the SQL SELECT statement. Each time the current execution flow context reaches this action again, the next row in the JDBC result set is fetched.
Finally, when the JDBC result set is exhausted, the result set is closed. The result set is also closed when the current execution flow context reaches a transaction break since JDBC result sets must be closed before the open transaction commits.
If this action runs in a loop (that is, if the action will invoke its stored procedure more than once during the workflow), it must completely loop through the entire result before the current execution flow context reaches a transaction break. If a transaction break occurs before the result set has completely exhausted and flow has returned to the action to close the set, you will encounter the error:
SQLServerException: The result set is closed
Once the JDBC result set is properly closed, the next time the current execution flow context executes this action, the SQL SELECT statement is issued again, and the entire process repeats.
The following table contains more information on the Database Stored Procedure Action result fields.
| Flow Context Variable | Field | 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 | outParameters | List<Object> | The OUT parameters from the stored procedure call. | List outParameters = flowContext.get("RESULT").outParameters; for (Object outParameter : outParameters) { System.out.println("OUT Parameter: " + outParameter); } |
| 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 OUT parameters or 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:
