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

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 " +
" FROM sales.odetail D, sales.parts P, sales.orders O " +
" WHERE D.partnum = P.partnum AND D.ordernum = O.ordernum " +
" AND O.order_date >= cast(? as date) " +
" ORDER BY D.ordernum ";
java.sql.PreparedStatement ps3 = c.prepareStatement(s);
ps3.setString(1, onOrAfter);
detail[0] = ps3.executeQuery();
}
CREATE PROCEDURE Statement
CREATE PROCEDURE SAMDBCAT.SALES.ORDER_SUMMARY
(IN ON_OR_AFTER_DATE VARCHAR(20) CHARACTER SET ISO88591,
OUT NUM_ORDERS
LARGEINT)
DYNAMIC RESULT SETS 2
EXTERNAL NAME 'SPJMethods.orderSummary(java.lang.String,
long[],java.sql.ResultSet[], java.sql.ResultSet[])'
EXTERNAL PATH '/usr/mydir/myclasses';
LOCATION \ALPINE.$SYSTEM.ZSDCR2C6.L1Z7NW00
LANGUAGE JAVA
PARAMETER STYLE JAVA
READS SQL DATA
NOT DETERMINISTIC
Examples of the Sample SPJs 121