Neoview Guide to Stored Procedures in Java (R2.3, R2.4)

// 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();
}
}
Calling an SPJ in a Trigger
A trigger is a mechanism in the database that enables the database engine to perform certain
actions when a specified event occurs. SPJs are useful as triggered actions because they can help
you encapsulate and enforce rules in the database. For more information about the benefits of
using SPJs, see “Benefits of SPJs” (page 19).
Neoview SQL supports a CALL statement in a trigger provided that the SPJ in the CALL statement
does not have any OUT or INOUT parameters or return any result sets. For information about
OUT and INOUT parameters, see “Returning Output Values From the Java Method” (page 26)
70 Executing SPJs