SQL/MX Guide to Stored Procedures in Java (G06.24+, H06.03+)

Sample SPJs
HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
A-17
TOTALPRICE Stored Procedure
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 " +
"'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);