17.10 - Dynamic Result Sets Allowed - Call-Level Interface Version 2

Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems

Product
Call-Level Interface Version 2
Release Number
17.10
Release Date
October 2021
Content Type
Programming Reference
Publication ID
B035-2418-061K
Language
English (United States)

Usage Notes

Even if the procedure requests that the results of its SQL be reflected to the CALLer or the application through the use of either the SQL PROCEDURE's WITH RETURN clause or the DBCAREA Return-result-to option, a CLIv2 routine so targeted must allow these results using the DBCAREA Result-sets-OK option. If this option was not specified, the results will not be returned there.

When results are propagated, they appear in the response with an incremented statement number after the statement that contained the SQL CALL. The statement number is contained in both the first parcel for the implicit statement, either Success, OK, ResultSummary; and the last parcel for the implicit statement: EndStatement. Such a Success, OK, or ResultSummary parcel will also contain an ActivityType of 176. The next parcel will be ResultSet, which identifies the procedure and provides the row number to which the procedure positioned the results. Any response parcels follow the ResultSet.

The options for the request making the CALL and the request issued by the stored procedure could differ: the response provided to the procedure, the caller, or the application would reflect the request options that each established. So a response honors that request's DBCAREA Return-objects-as, Continued-characters-state, APH-response-OK, Return-statement-info, Max-decimal-returned, Return-identity-data options. If the procedure specified the DBCAREA MultipartIndicator Response-mode and selects a Large Object (LOB) but the CALLer or application specified a Response-mode that does not support LOBs, the procedure will receive an error and no response will be propagated.

Similarly, the response provided to the procedure, the caller, or the application honors the character set that each established. However, their collation is not. Instead, the collation used for all responses is the collation that was used when the stored procedure was compiled.

The procedure may use the DBCAREA Keep-response option to provide CLIv2 the same information that would be provided by SCROLL or NO SCROLL on the SQL DECLARE CURSOR statement for an SQL Stored Procedure. Specifically, a setting of 'Y' corresponds to NO SCROLL, indicating the propagated results are positioned to the next unfetched row, with fetched rows not propagated; a setting of 'P' corresponds to SCROLL, indicating the propagated results are positioned to the last row fetched, with earlier fetched rows propagated and available by explicit positioning using either the CLIv2 Fetch function specifying the DBCAREA Positioning-action, or the CLIv2 Rewind function. The Success, OK, or ResultSummary parcel ActivityCount field will indicate all available rows available, without regard to initial positioning.

Unlike positioning of the returned row, the procedure cannot position the offset of a Large Object (LOB) selected by locator. Any positioning by the procedure within the LOB is not reflected.

Language Variable Name
COBOL: DYNAMIC-RESULT-SETS-ALLOWED
C: dynamic_result_sets_allowed
Routine Action Taken
DBCHINI: writes
DBCHCL: read(RSUP;IRQ)
Used by Action Taken
application program writes

Possible value can be set in this DBCAREA field is 'Y' and 'N'.