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

PreparedStatement getNumOrders =
conn.prepareStatement("SELECT COUNT(month(order_date)) "+
"FROM samdbcat.sales.orders " +
"WHERE month(order_date) = ?");
getNumOrders.setInt(1, month);
ResultSet rs = getNumOrders.executeQuery();
rs.next();
numOrders[0] = rs.getInt(1);
rs.close();
conn.close();
}
CREATE PROCEDURE Statement
CREATE PROCEDURE samdbcat.sales.monthlyorders(IN INT, OUT number INT)
EXTERNAL NAME 'Sales.numMonthlyOrders'
EXTERNAL PATH '/usr/mydir/myclasses'
LANGUAGE JAVA
PARAMETER STYLE JAVA
READS SQL DATA;
CALL Statement to Invoke the SPJ
To invoke the MONTHLYORDERS procedure in MXCI:
CALL samdbcat.sales.monthlyorders(3,?);
The MONTHLYORDERS procedure determines the total number of orders during a specified month
and returns this output in MXCI:
NUMBER
-----------
4
--- SQL operation complete.
In March, there were four orders.
TOTALPRICE Stored Procedure
Java Method
public static void totalPrice(BigDecimal qtyOrdered,
String shippingSpeed,
BigDecimal[] price)
throws SQLException
{
BigDecimal shipcharge = new BigDecimal(0);
if (shippingSpeed.equals("economy"))
shipcharge = new BigDecimal(1.95);
}
else if (shippingSpeed.equals("standard"))
shipcharge = new BigDecimal(4.99);
}
else if (shippingSpeed.equals("nextday"))
shipcharge = new BigDecimal(14.99);
}
else
{
throw new
SQLException ("Invalid value for shipping speed. " +
"Retry the CALL statement using " +
116 Sample SPJs