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

Sample SPJs
HP NonStop SQL/MX Release 3.2 Guide to Stored Procedures in Java—691166-001
A-23
ORDERSUMMARY Stored Procedure
ORDERSUMMARY Stored Procedure
Java Method
public static void orderSummary(java.lang.String onOrAfter,
long[] numOrders,
java.sql.ResultSet[] orders,
java.sql.ResultSet[] detail)
throws SQLException
{
java.lang.String s;
java.sql.Connection c =
DriverManager.getConnection("jdbc:default:connection");
// Get the number of orders on or after this date
s = " SELECT count(ordernum) FROM sales.orders " +
" WHERE order_date >= cast(? as date) ";
java.sql.PreparedStatement ps1 = c.prepareStatement(s);
ps1.setString(1, onOrAfter);
java.sql.ResultSet rs = ps1.executeQuery();
rs.next();
numOrders[0] = rs.getLong(1);
rs.close();
// 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 ";
java.sql.PreparedStatement ps2 = c.prepareStatement(s);
ps2.setString(1, onOrAfter);
orders[0] = ps2.executeQuery();
// Open a result set for order detail rows
s = " SELECT D.*, P.partdesc " +