Example 2: Calling a stored procedure that returns a result set from a nested procedure
This example shows how a nested stored procedure can open and return a result set to the outermost procedure.
To return a result set to the outermost procedure in an environment where there are nested stored procedures, the RETURN TO CLIENT returnability attribute should be used on the DECLARE CURSOR statement or on the SET RESULT SETS statement to indicate that the cursors are to be returned to the application which called the outermost procedure. Note that this nested procedure returns two result sets to the client; the first, an array result set, and the second a cursor result set. Both an ODBC and a JDBC client application are shown below along with the stored procedures.
Defining the stored procedures
CREATE PROCEDURE prod.rtnnested () LANGUAGE CL DYNAMIC RESULT SET 2
EXTERNAL NAME prod.rtnnested GENERAL
CREATE PROCEDURE prod.rtnclient () LANGUAGE RPGLE
EXTERNAL NAME prod.rtnclient GENERAL
Note: By using the code examples, you agree to the terms
of the Code license and disclaimer information.
CL source for stored procedure prod.rtnnested
PGM
CALL PGM(PROD/RTNCLIENT)
ILE RPG source for stored procedure prod.rtnclient
DRESULT DS OCCURS(20)
D COL1 1 16A
C 1 DO 10 X 2 0
C X OCCUR RESULT
C EVAL COL1='array result set'
C ENDDO
C EVAL X=X-1
C/EXEC SQL DECLARE C2 CURSOR WITH RETURN TO CLIENT
C+ FOR SELECT LSTNAM FROM QIWS.QCUSTCDT FOR FETCH ONLY
C/END-EXEC
C/EXEC SQL
C+ OPEN C2
C/END-EXEC
C/EXEC SQL
C+ SET RESULT SETS FOR RETURN TO CLIENT ARRAY :RESULT FOR :X ROWS,
C+ CURSOR C2
C/END-EXEC
C SETON LR
C RETURN
ODBC application
//*******************************************************************
//
// Module:
// Examples.C
//
// Purpose:
// Perform calls to stored procedures to get back result sets.
//
// *******************************************************************
#include "common.h"
#include "stdio.h"
// *******************************************************************
//
// Local function prototypes.
//
// *******************************************************************
SWORD FAR PASCAL RetClient(lpSERVERINFO lpSI);
BOOL FAR PASCAL Bind_Params(HSTMT);
BOOL FAR PASCAL Bind_First_RS(HSTMT);
BOOL FAR PASCAL Bind_Second_RS(HSTMT);
// *******************************************************************
//
// Constant strings definitions for SQL statements used in
// the auto test.
//
// *******************************************************************
//
// Declarations of variables global to the auto test.
//
// *******************************************************************
#define ARRAYCOL_LEN 16
#define LSTNAM_LEN 8
char stmt[2048];
char buf[2000];
UDWORD rowcnt;
char arraycol[ARRAYCOL_LEN+1];
char lstnam[LSTNAM_LEN+1];
SDWORD cbcol1,cbcol2;
lpSERVERINFO lpSI; /* Pointer to a SERVERINFO structure. */
// ********************************************************************
//
// Define the auto test name and the number of test cases
// for the current auto test. These informations will
// be returned by AutoTestName().
//
// ********************************************************************
LPSTR szAutoTestName = CREATE_NAME("Result Sets Examples");
UINT iNumOfTestCases = 1;
// *******************************************************************
//
// Define the structure for test case names, descriptions,
// and function names for the current auto test.
// Test case names and descriptions will be returned by
// AutoTestDesc(). Functions will be run by
// AutoTestFunc() if the bits for the corresponding test cases
// are set in the rglMask member of the SERVERINFO
// structure.
//
// *******************************************************************
struct TestCase TestCasesInfo[] =
{
"Return to Client",
"2 result sets ",
RetClient
};
// *******************************************************************
//
// Sample return to Client:
// Return to Client result sets. Call a CL program which in turn
// calls an RPG program which returns 2 result sets. The first
// result set is an array result set and the second is a cursor
// result set.
//
//
// *******************************************************************
SWORD FAR PASCAL RetClient(lpSERVERINFO lpSI)
{
SWORD sRC = SUCCESS;
RETCODE returncode;
HENV henv;
HDBC hdbc;
HSTMT hstmt;
if (FullConnect(lpSI, &henv, &hdbc, &hstmt) == FALSE)
{
sRC = FAIL;
goto ExitNoDisconnect;
}
// ********************************************************
// Call CL program PROD.RTNNESTED, which in turn calls RPG
// program RTNCLIENT.
// ********************************************************
strcpy(stmt,"CALL PROD.RTNNESTED()");
// **************************************************************
// Call the CL program prod.rtnnested. This program will in turn
// call the RPG program proc.rtnclient, which will open 2 result
// sets for return to this ODBC application.
// *************************************************************
returncode = SQLExecDirect(hstmt,stmt,SQL_NTS);
if (returncode != SQL_SUCCESS)
{
vWrite(lpSI, "CALL PROD.RTNNESTED is not Successful", TRUE);
}
else
{
vWrite(lpSI, "CALL PROC.RTNNESTED was Successful", TRUE);
}
// **************************************************************
// Bind the array result set output column. Note that the result
// sets are returned to the application in the order that they
// are specified on the SET RESULT SETS statement.
// *************************************************************
if (Bind_First_RS(hstmt) == FALSE)
{
myRETCHECK(lpSI, henv, hdbc, hstmt, SQL_SUCCESS,
returncode, "Bind_First_RS");
sRC = FAIL;
goto ErrorRet;
}
else
{
vWrite(lpSI, "Bind_First_RS Complete...", TRUE);
}
// **************************************************************
// Fetch the rows from the array result set. After the last row
// is read, a returncode of SQL_NO_DATA_FOUND will be returned to
// the application on the SQLFetch request.
// **************************************************************
returncode = SQLFetch(hstmt);
while(returncode == SQL_SUCCESS)
{
wsprintf(stmt,"array column = %s",arraycol);
vWrite(lpSI,stmt,TRUE);
returncode = SQLFetch(hstmt);
}
if (returncode == SQL_NO_DATA_FOUND) ;
else {
myRETCHECK(lpSI, henv, hdbc, hstmt, SQL_SUCCESS_WITH_INFO,
returncode, "SQLFetch");
sRC = FAIL;
goto ErrorRet;
}
// ********************************************************
// Get any remaining result sets from the call. The next
// result set corresponds to cursor C2 opened in the RPG
// Program.
// ********************************************************
returncode = SQLMoreResults(hstmt);
if (returncode != SQL_SUCCESS)
{
myRETCHECK(lpSI, henv, hdbc, hstmt, SQL_SUCCESS, returncode, "SQLMoreResults");
sRC = FAIL;
goto ErrorRet;
}
// **************************************************************
// Bind the cursor result set output column. Note that the result
// sets are returned to the application in the order that they
// are specified on the SET RESULT SETS statement.
// *************************************************************
if (Bind_Second_RS(hstmt) == FALSE)
{
myRETCHECK(lpSI, henv, hdbc, hstmt, SQL_SUCCESS,
returncode, "Bind_Second_RS");
sRC = FAIL;
goto ErrorRet;
}
else
{
vWrite(lpSI, "Bind_Second_RS Complete...", TRUE);
}
// **************************************************************
// Fetch the rows from the cursor result set. After the last row
// is read, a returncode of SQL_NO_DATA_FOUND will be returned to
// the application on the SQLFetch request.
// **************************************************************
returncode = SQLFetch(hstmt);
while(returncode == SQL_SUCCESS)
{
wsprintf(stmt,"lstnam = %s",lstnam);
vWrite(lpSI,stmt,TRUE);
returncode = SQLFetch(hstmt);
}
if (returncode == SQL_NO_DATA_FOUND) ;
else {
myRETCHECK(lpSI, henv, hdbc, hstmt, SQL_SUCCESS_WITH_INFO,
returncode, "SQLFetch");
sRC = FAIL;
goto ErrorRet;
}
returncode = SQLFreeStmt(hstmt,SQL_CLOSE);
if (returncode != SQL_SUCCESS)
{
myRETCHECK(lpSI, henv, hdbc, hstmt, SQL_SUCCESS,
returncode, "Close statement");
sRC = FAIL;
goto ErrorRet;
}
else
{
vWrite(lpSI, "Close statement...", TRUE);
}
ErrorRet:
FullDisconnect(lpSI, henv, hdbc, hstmt);
if (sRC == FAIL)
{
// a failure in an ODBC function that prevents completion of the
// test - for example, connect to the server
vWrite(lpSI, "\t\t *** Unrecoverable RTNClient Test FAILURE ***", TRUE);
} /* endif */
ExitNoDisconnect:
return(sRC);
} // RetClient
BOOL FAR PASCAL Bind_First_RS(HSTMT hstmt)
{
RETCODE rc = SQL_SUCCESS;
rc = SQLBindCol(hstmt,1,SQL_C_CHAR,arraycol,ARRAYCOL_LEN+1, &cbcol1);
if (rc != SQL_SUCCESS) return FALSE;
return TRUE;
}
BOOL FAR PASCAL Bind_Second_RS(HSTMT hstmt)
{
RETCODE rc = SQL_SUCCESS;
rc = SQLBindCol(hstmt,1,SQL_C_CHAR,lstnam,LSTNAM_LEN+1,&dbcol2);
if (rc != SQL_SUCCESS) return FALSE;
return TRUE;
}
JDBC application
//-----------------------------------------------------------
// Call Nested procedures which return result sets to the
// client, in this case a JDBC client.
//-----------------------------------------------------------
import java.sql.*;
public class callNested
{
public static void main (String argv[]) // Main entry point
{
try {
Class.forName("com.ibm.db2.jdbc.app.DB2Driver");
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
Connection jdbcCon =
DriverManager.getConnection("jdbc:db2:lp066ab","Userid","xxxxxxx");
jdbcCon.setAutoCommit(false);
CallableStatement cs = jdbcCon.prepareCall("CALL PROD.RTNNESTED");
cs.execute();
ResultSet rs1 = cs.getResultSet();
int r = 0;
while (rs1.next())
{
r++;
String s1 = rs1.getString(1);
System.out.print("Result set 1 Row: " + r + ": ");
System.out.print(s1 + " " );
System.out.println();
}
cs.getMoreResults();
r = 0;
ResultSet rs2 = cs.getResultSet();
while (rs2.next())
{
r++;
String s2 = rs2.getString(1);
System.out.print("Result set 2 Row: " + r + ": ");
System.out.print(s2 + " ");
System.out.println();
}
}
catch ( SQLException e ) {
System.out.println( "SQLState: " + e.getSQLState() );
System.out.println( "Message : " + e.getMessage() );
e.printStackTrace();
}
} // main
}