Dynamic result sets are supported for external procedures written in C or C++ using the CLIv2 API and for Java external procedures using the JDBC API.
A procedure can return from 0 to 15, inclusive, result sets.
- Submit the request to the database using the same session, which is the default connection, as the session for the external procedure.
- Submit a single statement containing a SELECT request.
- Specify a SPReturnResult of 1, 2, 3, or 4 in the Options parcel.
- Specify keep response or positioning.
TO keep the result set until EndRequest is called so that parcels can then be fetched … Set keep_resp to this value … either in random order by row or sequentially within a row using the DBCAREA settings Positioning-action, Positioning-statement-number, and Positioning-value P in sequential order, the Rewind function called, and then the parcels can again be fetched Y
- The external procedure did not send a Cancel parcel to the Dispatcher.
- The caller or client has indicated its willingness to receive the result sets by setting DynamicResultSetsAllowed to Y in the Options parcel for the request that called the procedure.
- The number of result sets to return is less than or equal to the number_of_sets value in the DYNAMIC RESULT SETS clause of the CREATE PROCEDURE or REPLACE PROCEDURE request.
If the external procedure does send a Cancel parcel to the Dispatcher, then the Dispatcher does not return the result set to the caller or client.
To match the functionality of the SCROLL and NO SCROLL cursors for SQL procedures, the set of rows returned to the caller or client depends on whether the SetPosition parcel, flavor 157, is sent with the SELECT request.
| IF the SetPosition parcel is … | THEN the set of rows returned follows the rules for … |
|---|---|
| sent | SCROLL cursors. |
| not sent | NO SCROLL cursors. |
For CLIv2 applications, the SetPosition parcel is sent when keep_resp = P.
The external procedure sends the ResultSetCurrentPosition parcel to the Dispatcher Continue mailbox to indicate the last row read prior to the external procedure completing. If the procedure never sends the ResultSetCurrentPosition parcel, then the platform assumes that while the entire response might have been sent, none of the rows have been read. This assumption is made even if the external procedure sends a Continue request to the database.
An external procedure can read a dynamic result set by submitting the CALL with the DynamicResultSetsAllowed field in the Options parcel set to the same session that invoked the external procedure.