Dynamic Result Sets | CREATE PROCEDURE | Teradata Vantage - Dynamic Result Sets - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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.

To create a result set to return to the caller or client, the external procedure must do these things:
  • 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
When an external procedure executes, result sets created during its execution that match the conditions in the preceding bulleted list are returned to a calling procedure or client application of an external procedure if all of the following conditions are met:
  • 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.