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

"'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);
}
CREATE PROCEDURE Statement
CREATE PROCEDURE samdbcat.sales.totalprice(IN qty NUMERIC(18),
IN rate VARCHAR(10),
INOUT price NUMERIC(18,2))
EXTERNAL NAME 'Sales.totalPrice'
EXTERNAL PATH '/usr/mydir/myclasses'
LANGUAGE JAVA
PARAMETER STYLE JAVA
READS SQL DATA;
CALL Statement to Invoke the SPJ
To invoke the TOTALPRICE procedure in MXCI:
SET PARAM ?p 10; CALL samdbcat.sales.totalprice(23, 'standard', ?p);
The TOTALPRICE procedure calculates the total price of a purchase and returns this output in MXCI:
PRICE
---------------------
253.96
--- SQL operation complete.
The total price of 23 items, which cost $10 each and which are shipped at the standard rate, is
$253.96, including sales tax.
ADJUSTSALARY Stored Procedure
Java Method
public static void adjustSalary(BigDecimal empNum,
double percent,
BigDecimal[] newSalary)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement setSalary =
conn.prepareStatement("UPDATE samdbcat.persnl.employee "+
"SET salary = salary * (1 + (? / 100))" +
"WHERE empnum = ?");
PreparedStatement getSalary =
conn.prepareStatement("SELECT salary " +
"FROM samdbcat.persnl.employee " +
"WHERE empnum = ?");
setSalary.setDouble(1, percent);
setSalary.setBigDecimal(2, empNum);
setSalary.executeUpdate();
getSalary.setBigDecimal(1, empNum);
Examples of the Sample SPJs 117