Neoview Guide to Stored Procedures in Java (R2.5)

Returning Result Sets in a JDBC Client Application
This example shows serial result set processing in a JDBC client application where the result sets
are processed in order and one at a time after the CALL statement executes. The
java.sql.Statement.getMoreResults() method closes the current result set and moves
processing to the next available result set.
// Prepare a CALL statement
java.sql.CallableStatement s = myConnection.prepareCall (
"{call sales.ordersummary('01-01-2001', ?)}" );
// Register an output parameter
s.registerOutParameter(1, java.sql.Types.BIGINT);
// Execute the CALL
boolean rsAvailable = s.execute();
// Process all returned result sets. The outer while loop continues
// until there are no more result sets.
while (rsAvailable)
{
// The inner while loop processes each row of the current result set
java.sql.ResultSet rs = s.getResultSet();
while (rs.next())
{
// Process the row
}
rsAvailable = s.getMoreResults();
}
This example shows how a JDBC client application can have more than one stored procedure
result set open at a given time. The java.sql.Statement.getMoreResults(int) method
uses its input argument to decide whether currently open result sets should remain open or be
closed before the next result set is made available.
// Prepare a CALL statement
java.sql.CallableStatement s = myConnection.prepareCall (
"{call sales.ordersummary('01-01-2001', ?)}" );
// Register an output parameter
s.registerOutParameter(1, java.sql.Types.BIGINT);
// Execute the CALL
s.execute();
// Open the FIRST result set
java.sql.ResultSet firstRS = s.getResultSet();
// Open the SECOND result set but do not close the FIRST
s.getMoreResults(java.sql.Statement.KEEP_CURRENT_RESULT);
java.sql.ResultSet secondRS = s.getResultSet();
// The outer loop processes each row of the FIRST result set
while (firstRS.next())
{
// Process a row from the FIRST result set
// The inner loop processes some number of rows from the SECOND
// result set. The number depends on data extracted from the
// current row of the FIRST result set.
for (int i = 0; i < NUM_ROWS_TO_PROCESS; i++)
{
// Process a row from the SECOND result set
secondRS.next();
58 Executing SPJs