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-18
ADJUSTSALARY Stored Procedure
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:sqlmx:");
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);
ResultSet rs = getSalary.executeQuery();
rs.next();
newSalary[0] = rs.getBigDecimal(1);
rs.close();
conn.close();
}
CREATE PROCEDURE Statement
CREATE PROCEDURE samdbcat.persnl.adjustsalary(IN empnum NUMERIC(4),
IN percent FLOAT,
OUT newsalary NUMERIC(8,2))
EXTERNAL NAME 'Payroll.adjustSalary'
EXTERNAL PATH '/usr/mydir/myclasses'
LANGUAGE JAVA
PARAMETER STYLE JAVA
MODIFIES SQL DATA;