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-19
EMPLOYEEJOB Stored Procedure
CALL Statement to Invoke the SPJ
To invoke the ADJUSTSALARY procedure in MXCI:
CALL samdbcat.persnl.adjustsalary(29, 2.5, ?);
The ADJUSTSALARY procedure updates the salary of employee number 29 by 2.5
percent and returns this output in MXCI:
NEWSALARY
------------
139400.00
--- SQL operation complete.
The salary of employee number 29 was originally $136,000.00 and became
$139,400.00 after the invocation of ADJUSTSALARY.
EMPLOYEEJOB Stored Procedure
Java Method
public static void employeeJob(int empNum,
java.lang.Integer[] jobCode)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:sqlmx:");
PreparedStatement getJobcode =
conn.prepareStatement("SELECT jobcode " +
"FROM samdbcat.persnl.employee " +
"WHERE empnum = ?");
getJobcode.setInt(1, empNum);
ResultSet rs = getJobcode.executeQuery();
rs.next();
int num = rs.getInt(1);
if (rs.wasNull())
jobCode[0] = null;
else
jobCode[0] = new Integer(num);
rs.close();
conn.close();
}
CREATE PROCEDURE Statement
CREATE PROCEDURE samdbcat.persnl.employeejob(IN empnum INT,
OUT jobcode INT)
EXTERNAL NAME 'Payroll.employeeJob(int, java.lang.Integer[])'
EXTERNAL PATH '/usr/mydir/myclasses'
LANGUAGE JAVA
PARAMETER STYLE JAVA
READS SQL DATA;