Neoview Guide to Stored Procedures in Java (R2.5)

SQLException ("Invalid value for shipping speed. " +
"Retry the CALL statement using " +
"'economy' for 7 to 9 days," +
"'standard' for 3 to 5 days, or " +
"'nextday' for one day.", "38002" );
}
BigDecimal subtotal = price[0].multiply(qtyOrdered);
BigDecimal tax = new BigDecimal(0.0825);
BigDecimal taxcharge = subtotal.multiply(tax);
BigDecimal charges = taxcharge.add(shipcharge);
price[0] = subtotal.add(charges);
} // See the “TOTALPRICE Procedure” (page 75).
public static void partData(int partNum,
String[] partDescription,
BigDecimal[] unitPrice,
int[] qtyAvailable,
ResultSet[] orders,
ResultSet[] locations,
ResultSet[] suppliers,
ResultSet[] reps)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
// Retrieve detail about this part into the output parameters
PreparedStatement getPartInfo = conn.prepareStatement(
" SELECT P.partdesc, P.price, P.qty_available " +
" FROM sales.parts P " +
" WHERE partnum = ? ");
getPartInfo.setInt(1, partNum);
ResultSet rs = getPartInfo.executeQuery();
rs.next();
partDescription[0] = rs.getString(1);
unitPrice[0] = rs.getBigDecimal(2);
qtyAvailable[0] = rs.getInt(3);
rs.close();
// Return a result set of rows from the ORDERS table listing orders
// that included this part. Each ORDERS row is augmented with the
// quantity of this part that was ordered.
PreparedStatement getOrders = conn.prepareStatement(
" SELECT O.*, QTY.QTY_ORDERED " +
" FROM sales.orders O, " +
" ( select ordernum, sum(qty_ordered) as QTY_ORDERED " +
" from sales.odetail " +
" where partnum = ? " +
" group by ordernum) QTY " +
" WHERE O.ordernum = QTY.ordernum " +
" ORDER BY O.ordernum ");
getOrders.setInt(1, partNum);
orders[0] = getOrders.executeQuery();
// Return a result set of rows from the PARTLOC table listing
// locations that have this part in stock and the quantity they
// have on hand.
PreparedStatement getLocations = conn.prepareStatement(
" SELECT * " +
" FROM invent.partloc " +
" WHERE partnum = ? ");
getLocations.setInt(1, partNum);
Procedures in the SALES Schema 67