friedkiwi/ibm-information-center
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
ibm-information-center/dist/eclipse/plugins/i5OS.ic.sqlp_5.4.0.1/rbafyresultsete2.htm

401 lines
15 KiB
HTML
Raw Permalink Blame History

<?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, &amp;henv, &amp;hdbc, &amp;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, &amp;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,&amp;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>
Reference in New Issue View Git Blame Copy Permalink