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

" AND o.order_date >= CAST(? AS DATE) " +
" ORDER BY d.ordernum ";
java.sql.PreparedStatement ps3 = conn.prepareStatement(s);
ps3.setString(1, onOrAfter);
detail[0] = ps3.executeQuery();
} // See the “ORDERSUMMARY Procedure” (page 81).
}
See the following sections for more information about each SPJ method.
LOWERPRICE Procedure
The LOWERPRICE procedure determines which items are selling poorly (that is, have less than
50 orders) and lowers the price of these items in the database by 10 percent.
Java Method: lowerPrice()
Example A-2 lowerPrice() 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 sales.parts p " +
"LEFT JOIN sales.odetail o " +
" ON p.partnum = o.partnum " +
"GROUP BY p.partnum");
PreparedStatement updateParts =
conn.prepareStatement("UPDATE 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();
}
}
rs.close();
conn.close();
}
Creating the Procedure: LOWERPRICE
To create this procedure in the SALES schema, upload the Sales.jar file to the Neoview
platform, navigate to the SALES schema in HPDM, and then enter or select these values in the
Create Procedure dialog box of HPDM. For more information, see the HP Database Manager
(HPDM) User Guide.
lowerpriceName:
Sales.jar > Sales > lowerPriceCode:
Procedures in the SALES Schema 69