Stored procedures are great. They afford database developers the ability to perform secure server-side logic and to return one or more result sets to the caller. (A result set is simply the data returned from an SQL query.) This tip demonstrates how to work with multiple result sets in a client/server or Web environment using Java, C#, or Visual Basic for Applications (VBA).
Why would you want to return multiple result sets from a single stored procedure instead of having multiple stored procedures return only one result set each? While not applicable in all cases, the general benefits of returning multiple result sets from a stored procedure include these:
- Fewer calls to the database server are required.
- Complex procedures can process related data and create the necessary result sets in one step. For example, I built a stored procedure that required several result sets to be returned to the client. All the result sets were based on a single large temporary table that the stored procedure had to build. Duplicate logic and hefty "re-processing" would've been required to process each result set in its own procedure.
Here is a simple example of a stored procedure that returns two result sets:
parmSchema In VarChar(128),
parmTable In VarChar(128))
Language SQL
Result Sets 2
--
-- This stored procedure returns two
-- result sets:
-- 1) Table meta data
-- 2) Column meta data
-- based on schema and table names
--
Begin
Declare CursorTable Cursor For
Select *
From QSYS2.SysTables
Where Table_Schema=parmSchema
And Table_Name=parmTable;
Declare CursorColumns Cursor For
Select *
From QSYS2.SysColumns
Where Table_Schema=parmSchema
And Table_Name=parmTable;
/* Open the result sets for
processing by the client code */
Open CursorTable;
Open CursorColumns;
End
Incidentally, while this sample stored procedure is written in SQL, the same thing can be accomplished by writing stored procedures in a high-level language such as RPG or COBOL using embedded SQL.
So how do we retrieve both result sets from client code?
Figures 1, 2 , and 3 contain the VBA, C# (VB.NET can be easily substituted), and Java code required to retrieve multiple result sets.
The data access providers of each environment have special methods to get the next available result set from the stored procedure. In VBA with ADO, use the NextRecordset method of the Recordset object. In .NET using the IBM managed provider, use the NextResult method of the iDB2DataReader object. In JDBC, use the getMoreResults method of the CallableStatement object.
Here are a few extra things to note when dealing with multiple result sets:
- The VBA code uses ActiveX Data Objects (ADO) with the ODBC provider MSDASQL. I've tried the IBMDASQL and IBMDA400 native OLE DB providers for the iSeries but without success. This is not to say they can't do it; I just haven't figured out how. The old IBMDA400 documentation states that the NextRecordset method is not supported, but I'm always hopeful that old information is wrong! If someone has gotten the NextRecordset method to work with either of these providers, please post a message into this TechTip's related forum.
- The sample .NET code is demonstrated using the data reader object provided by the DB2 UDB for iSeries .NET managed provider that comes with iSeries Access V5R3. The ODBC provider should work similarly using the .NET framework's ODBC classes.
- Generally, result sets must be processed completely and sequentially. However, in Java, multiple results sets may be opened simultaneously (see the IBM documentation for more info).
These simple code examples assume that the client knows the number of result sets. Some applications may require processing an unknown number of result sets. To do this, create an outer loop (outside of the row processing loop) to test for additional available result sets. In VBA with ADO, continue to call the NextRecordset method of the ADODB.Recordset object until the special value "Nothing" is returned. In .NET, query the NextResult method against the iDB2DataReader object (or the OdbcDataReader object). This method will return True if more results are available. In Java, execute the getMoreResults method of the CallableStatement object. This method will return True if more results are available.
In summary, using stored procedures to return multiple result sets to the caller will often allow for the consolidation of related database code and will also reduce the number of calls to the database from the application.
Michael Sansoterra is a developer at i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. You can reach him at
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online