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

parameters in the signature, Neoview SQL prevents you from creating an SPJ using that Java
method. This example shows the declaration of an SPJ method, orderSummary(), which returns
a maximum of two result sets:
public static void orderSummary(java.lang.String onOrAfter,
long[] numOrders,
java.sql.ResultSet[] orders,
java.sql.ResultSet[] detail)
This code fragment shows how the orderSummary() method returns one of its result sets by
executing a SELECT statement and assigning the java.sql.ResultSet object to a
java.sql.ResultSet[] output array:
// Open a result set for order num, order info rows
java.lang.String s = " SELECT amounts.*, orders.order_date, emps.last_name " +
" FROM ( SELECT o.ordernum, COUNT(d.partnum) AS num_parts, " +
" SUM(d.unit_price * d.qty_ordered) AS amount " +
" FROM sales.orders o, sales.odetail d " +
" WHERE o.ordernum = d.ordernum " +
" AND o.order_date >= CAST(? AS DATE) " +
" GROUP BY o.ordernum ) amounts, " +
" sales.orders orders, persnl.employee emps " +
" WHERE amounts.ordernum = orders.ordernum " +
" AND orders.salesrep = emps.empnum " +
" ORDER BY orders.ordernum ";
java.sql.PreparedStatement ps2 = conn.prepareStatement(s);
ps2.setString(1, onOrAfter);
// Assign the returned result set object to the first element of a
// java.sql.ResultSet[] output array
orders[0] = ps2.executeQuery();
For the entire example, see the “ORDERSUMMARY Procedure” (page 81).
IMPORTANT: In an SPJ method that returns result sets, do not explicitly close the default
connection or the statement object. Neoview SQL closes the connection used to return result sets
after it finishes processing the result sets. If you close the connection on which the result sets are
being returned, those result sets will be lost, and the calling application will not be able to process
them.
An SPJ method can return result sets that contain any data types, except large object (LOB) data.
An SPJ method can return a holdable or updatable cursor as a result set. However, Neoview
SQL does not expose those attributes in the calling application. An SPJ method can return a
ResultSet object that is a stored procedure result set acquired from a nested CALL statement
executed by the SPJ method. However, you are discouraged from nesting CALL statements in
SPJ methods. For more information, see “Nested Java Method Invocations” (page 27).
If an SPJ method returns multiple ResultSet objects, Neoview SQL sorts the collection of valid
result sets in chronological order according to when the underlying SQL statements were executed.
If the number of result sets exceeds the declared maximum for the SPJ, only the first set of result
sets up to the maximum number are returned. Neoview SQL discards the other result sets and
returns a warning to the calling application.
When an SPJ method returns a ResultSet object through a java.sql.ResultSet[] parameter,
Neoview SQL exposes the underlying rows of data as an SQL cursor in the calling application.
If a returned result set is a scrollable cursor, all underlying rows are included in the result set
and are available to the calling application. If a returned result set is not scrollable, only those
rows not processed by the SPJ method are included in the result set and are available to the
calling application. If an SPJ method returns multiple occurrences of the same ResultSet object,
Neoview SQL ignores all but one occurrence and makes the underlying rows available to the
calling application as a single result set.
26 Developing SPJ Methods