SQL/MX Guide to Stored Procedures in Java (G06.24+, H06.03+)

Sample SPJs
HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
A-21
SUPPLYNUMBERS Stored Procedure
CREATE PROCEDURE Statement
CREATE PROCEDURE samdbcat.invent.supplierinfo(IN empnum NUMERIC(4),
OUT suppname CHAR(18),
OUT address CHAR(22),
OUT city CHAR(14),
OUT state CHAR(12),
OUT zipcode CHAR(10))
EXTERNAL NAME 'Inventory.supplierInfo'
EXTERNAL PATH '/usr/mydir/myclasses'
LANGUAGE JAVA
PARAMETER STYLE JAVA
READS SQL DATA;
CALL Statement to Invoke the SPJ
To invoke the SUPPLIERINFO procedure in MXCI:
CALL samdbcat.invent.supplierinfo(25, ?, ?, ?, ?, ?);
The SUPPLIERINFO procedure accepts the supplier number 25 and returns this
output in MXCI:
SUPPNAME ADDRESS CITY STATE ZIPCODE
------------------ ---------------------- -------------- ------------ ----------
Schroeder's Ltd 212 Strasse Blvd West Hamburg Rhode Island 22222
--- SQL operation complete.
Supplier number 25 is Schroeder’s Ltd. and is located in Hamburg, Rhode Island.
SUPPLYNUMBERS Stored Procedure
Java Method
public static void supplyQuantities(int[] avgQty,
int[] minQty,
int[] maxQty)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:sqlmx:");
PreparedStatement getQty =
conn.prepareStatement("SELECT AVG(qty_on_hand), " +
" MIN(qty_on_hand), " +
" MAX(qty_on_hand) " +
"FROM samdbcat.invent.partloc");
ResultSet rs = getQty.executeQuery();
rs.next();
avgQty[0] = rs.getInt(1);
minQty[0] = rs.getInt(2);
maxQty[0] = rs.getInt(3);
rs.close();
conn.close();
}