401 lines
15 KiB
HTML
401 lines
15 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="reference" />
|
||
|
|
<meta name="DC.Title" content="Example 2: Call a stored procedure which returns a result set from a nested procedure" />
|
||
|
|
<meta name="abstract" content="This example shows how a nested stored procedure can open and return a result set to the outermost procedure." />
|
||
|
|
<meta name="description" content="This example shows how a nested stored procedure can open and return a result set to the outermost procedure." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafyresultsets.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="rbafyresultsete2" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>Example 2: Call a stored procedure which returns a result set from
|
||
|
|
a nested procedure</title>
|
||
|
|
</head>
|
||
|
|
<body id="rbafyresultsete2"><a name="rbafyresultsete2"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Example 2: Call a stored procedure which returns a result set from
|
||
|
|
a nested procedure</h1>
|
||
|
|
<div><p>This example shows how a nested stored procedure can open and return
|
||
|
|
a result set to the outermost procedure.</p>
|
||
|
|
<div class="section"><p>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.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><div class="note"><span class="notetitle">Note:</span> By using the code examples, you agree to the terms of the <a href="codedisclaimer.htm">Code license and disclaimer information</a>.</div>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Defining the stored procedures:</p>
|
||
|
|
<pre><strong>CREATE PROCEDURE</strong> prod.rtnnested () <strong>LANGUAGE CL DYNAMIC RESULT SET 2</strong>
|
||
|
|
<strong>EXTERNAL NAME</strong> prod.rtnnested <strong>GENERAL</strong> </pre>
|
||
|
|
<pre><strong>CREATE PROCEDURE</strong> prod.rtnclient () <strong>LANGUAGE RPGLE</strong>
|
||
|
|
<strong>EXTERNAL NAME</strong> prod.rtnclient <strong>GENERAL</strong></pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">CL source for stored procedure prod.rtnnested</h4><pre>PGM
|
||
|
|
CALL PGM(PROD/RTNCLIENT)
|
||
|
|
|
||
|
|
</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">ILE RPG source for stored procedure prod.rtnclient</h4><pre>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
|
||
|
|
</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">ODBC application</h4><pre>//*******************************************************************
|
||
|
|
//
|
||
|
|
// 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;
|
||
|
|
}
|
||
|
|
</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">JDBC application</h4><pre>//-----------------------------------------------------------
|
||
|
|
// 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
|
||
|
|
}
|
||
|
|
|
||
|
|
</pre>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafyresultsets.htm" title="In addition to returning output parameters, stored procedures have a feature by which a result table associated with a cursor opened in the stored procedure (called a result set) can be returned to the application issuing the CALL statement. That application can then issue fetch requests to read the rows of the result set cursor.">Return result sets from stored procedures</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|