15.10 - Using Stored Procedure Dynamic Result Sets - Preprocessor2 for Embedded SQL

Teradata Preprocessor2 for Embedded SQL Programmer Guide

prodname
Preprocessor2 for Embedded SQL
vrm_release
15.10
category
Programming Reference
featnum
B035-2446-035K

Before stored procedure dynamic result sets, it was not possible to call a stored procedure and have the procedure produce a response spool. Instead, it was necessary to create a temporary table for the results, then gather the results by using a SELECT statement after the stored procedure CALL statement. Now, ANSI SQL allows stored procedures to return what are called dynamic result sets. The number of result sets are specified in CREATE PROCEDURE. Each result set returns a result similar to the output of one multi-statement request. The result sets are returned to PP2 using PP2’s client character set and response mode.

When using stored procedure dynamic result sets, precompile the application with SQLCHECK(FULL) and TRANSACT(BTET) options. This is required so that the variables receiving the OUTPUT or INOUT parameters can be passed from the precompiler to the runtime.

When a stored procedure without dynamic result sets is called, SQLCHECK must be specified as FULL:

  • -sc FULL for network environments
  • SQLCHECK(FULL) if Mainframe
  • For a stored procedure with dynamic result sets, a cursor is defined, and the transaction mode must be specified as BTET:

  • -tr BTET for network environments
  • TRANSACT(BTET) for mainframe environments
  • If BTET is not specified, a 5497 SQLCODE occurs (CALL cannot be submitted in a multi-statement request.)

    To declare a cursor to access a stored procedure, use the next example:

    EXEC SQL BEGIN DECLARE SECTION;
    	... 
    	long H1;
    	long H2;
     
    	long M1;
    	long M2;
    	long M3;
     
    	... 
    EXEC SQL END DECLARE SECTION;
    	...
    EXEC SQL                                     
    		DECLARE TESTCUR CURSOR 
    			FOR 'CALL TESTSP1(:H1, :H2)'; 
    EXEC SQL 
    		OPEN TESTCUR;

    If the cursor is created properly and the stored procedure returns one or more rows, the SQLCODE is set to 3212, indicating that the stored procedure returned a result set.

    If the cursor is created properly but the stored procedure returns no rows, the SQLCODE is set to 0.

    	if   (SQLCODE == 3212) 
    		fetch_rows();
    	else if (SQLCODE != 0)
    		error_check();

    Fetching rows from a cursor that is declared for a stored procedure is similar to fetching rows from a cursor defined as an SQL statement.

    void fetch_rows()
    	do 	{
    			EXEC SQL
    				FETCH TESTCUR INTO :M1, :M2, :M3;
    			...
    		} while (SQLCODE == 0);