SQL/MX 3.2 Guide to Stored Procedures in Java (H06.25+, J06.14+)

Writing SPJ Methods
HP NonStop SQL/MX Release 3.2 Guide to Stored Procedures in Java—691166-001
3-5
Using the main() Method
// 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, " +
" sales.orders ORDERS, persnl.employee EMPS " +
" WHERE AMOUNTS.ordernum = ORDERS.ordernum " +
" AND ORDERS.salesrep = EMPS.empnum " +
" ORDER BY ORDERS.ordernum ";
PreparedStatement ps2 = c.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 complete example, see the ORDERSUMMARY Stored Procedure on
page A-23.
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:
public static void main (java.lang.String [] args) {
...
}
When you register a main() method as an SPJ, the CREATE PROCEDURE
statement can contain zero or more SQL parameters, even though the underlying
main() method has only one array parameter. All the SQL parameters of the SPJ
must have the character string data type, CHAR or VARCHAR, and be declared with
the IN mode.
Caution. In an SPJ method that returns result sets, do not explicitly close the default
connection or the statement object. SQL/MX 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.