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

// 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 88).
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 29).
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.
For information about processing result sets in different calling applications, see:
• “Returning Result Sets in Neoview Script” (page 67)
• “Returning Result Sets in an ODBC Client Application” (page 68)
• “Returning Result Sets in a JDBC Client Application” (page 69)
Using the main() Method
You can use the main() method of a Java class file as an SPJ method. The main() method is
different from other Java methods because it accepts input values in an array of
java.lang.String objects and does not return any values in its array parameter.
For example, you can register this main() method as an SPJ:
28 Developing SPJ Methods