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

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 RhodeIsland 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:default:connection");
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();
}
CREATE PROCEDURE Statement
CREATE PROCEDURE samdbcat.invent.supplynumbers(OUT avrg INT,
OUT minm INT,
OUT maxm INT)
EXTERNAL NAME 'Inventory.supplyQuantities'
EXTERNAL PATH '/usr/mydir/myclasses'
LANGUAGE JAVA
PARAMETER STYLE JAVA
READS SQL DATA;
CALL Statement to Invoke the SPJ
To invoke the SUPPLYNUMBERS procedure in MXCI:
CALL samdbcat.invent.supplynumbers(?, ?, ?);
The SUPPLYNUMBERS procedure returns this output in MXCI:
AVRG MINM MAXM
----------- ----------- -----------
167 0 1132
--- SQL operation complete.
The average number of items in inventory is 167, the minimum number is 0, and the maximum
number is 1132.
120 Sample SPJs