SQL/MX 3.2.1 Guide to Stored Procedures in Java (H06.26+, J06.15+)

String[] stateName,
String[] postCode)
throws SQLException
{
...
The supplyQuantities() method returns an average quantity, a minimum quantity, and a
maximum quantity to separate output parameters of the integer type:
public static void supplyQuantities(int[] avgQty,
int[] minQty,
int[] maxQty)
throws SQLException
{
...
For more information about the SPJ examples, see Appendix A: Sample SPJs.
Type Mapping of Output Parameters
When writing an SPJ method, consider how the output of the SPJ will be used in the calling
application. For output parameters, the Java data type of the SPJ method must map to an SQL/MX
data type. See Table 2 The SQL/MX data type must then map to a compatible data type in the
calling application. See Chapter 5: Invoking SPJs in NonStop SQL/MX, for the client application
programming interfaces (APIs) that support SPJs and for cross-references to the appropriate manuals
for type mappings between NonStop SQL/MX and the API.
Stored Procedure Result Sets
SQL/MX supports SPJs that return stored procedure result sets. A stored procedure result set is a
cursor that is left open after the SPJ method executes (that is, after the CALL statement executes
successfully). After the CALL statement executes successfully, the calling application can issue
requests to open and then retrieve multiple rows of data from the returned result sets.
An SPJ method returns an ordered collection of result sets to the calling application by executing
SELECT statements and placing each returned ResultSet object into a one-element Java array of
type java.sql.ResultSet[ ]. The java.sql.ResultSet[ ] array is part of the Java method's signature and
is recognized by SQL/MX as a container for a single stored procedure result set.
Place the java.sql.ResultSet[ ] parameters after the other Java parameters, if any, in the Java
signature. If you do not place the java.sql.ResultSet[ ] parameters after the other parameters in the
signature, SQL/MX prevents you from creating an SPJ using that Java method.
NOTE: Stored procedures in Java that return Result Sets are supported on systems running J06.05
and later J-series RVUs and H06.16 and later H-series RVUs.
The following example shows the declaration of SPJ method orderSummary for the sample stored
procedure SALES.ORDER_SUMMARY. This Java method can return a maximum of two result sets.
public static void orderSummary(java.lang.String onOrAfter,
long[] numOrders,
java.sql.ResultSet[] orders,
java.sql.ResultSet[] detail)
The following code fragment shows how the SALES.ORDER_SUMMARY SPJ method returns one
of its result sets by executing a SELECT statement and assigning the acquired java.sql.ResultSet
object to a java.sql.ResultSet[ ] output array.
// Open a result set for <order num, order info> rows
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, " +
Guidelines for Writing SPJ Methods 51