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

OUT maxm INT)
EXTERNAL NAME 'Inventory.supplyQuantities'
EXTERNAL PATH '<mydir>'
LANGUAGE JAVA
PARAMETER STYLE JAVA
READS SQL DATA;
Before executing the OBEY command file, createprocs.sql, change the external paths from
<dir> to the OSS directories that contain the SPJ class files.
The catalog and schemas of stored procedures must exist before you issue CREATE PROCEDURE
statements. The CREATE PROCEDURE statements in createprocs.sql use the SAMDBCAT
catalog and the SALES, PERSNL, and INVENT schemas, which are part of the SQL/MX sample
database. To install the sample database, see the SQL/MX Quick Start.
To execute the CREATE PROCEDURE statements in createprocs.sql, you must be either the
schema owner (which you are if you install the sample database yourself) or the super ID.
Examples of the Sample SPJs
These examples show each SPJ method, the CREATE PROCEDURE statement that registers the SPJ,
the CALL statement that invokes the SPJ, and the output in MXCI:
• LOWERPRICE Stored Procedure (page 113)
• DAILYORDERS Stored Procedure (page 115)
• MONTHLYORDERS Stored Procedure (page 115)
• TOTALPRICE Stored Procedure (page 116)
• ADJUSTSALARY Stored Procedure (page 117)
• EMPLOYEEJOB Stored Procedure (page 118)
• SUPPLIERINFO Stored Procedure (page 119)
• SUPPLYNUMBERS Stored Procedure (page 120)
LOWERPRICE Stored Procedure
Java Method
public static void lowerPrice()
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement getParts =
conn.prepareStatement("SELECT p.partnum, " +
" SUM(qty_ordered) AS qtyOrdered" +
"FROM samdbcat.sales.parts p " +
"LEFT JOIN samdbcat.sales.odetailo " +
" ON p.partnum = o.partnum " +
"GROUP BY p.partnum");
PreparedStatement updateParts =
conn.prepareStatement("UPDATE samdbcat.sales.parts " +
"SET price = price * 0.9 " +
"WHERE partnum = ?");
ResultSet rs = getParts.executeQuery();
while (rs.next())
{
BigDecimal qtyOrdered = rs.getBigDecimal(2);
if ((qtyOrdered == null) || (qtyOrdered.intValue() <50))
{
BigDecimal partnum = rs.getBigDecimal(1);
updateParts.setBigDecimal(1, partnum);
updateParts.executeUpdate();
}
Examples of the Sample SPJs 113