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

Introduction
HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
1-2
How Do I Use SPJs?
PROCEDURE, registers a Java method as a stored procedure in the database by
storing its name, parameter types, location, and other metadata in SQL/MX system
metadata tables. An SPJ method must be registered in NonStop SQL/MX before an
SQL/MX application can call it. For more information about how SPJs operate in
NonStop SQL/MX, see SPJs in NonStop SQL/MX on page 1-6.
How Do I Use SPJs?
To create and invoke SPJs in NonStop SQL/MX:
1. Verify that you have the required software products installed on your HP NonStop
system. See Verifying Software Versions on page 2-3.
2. Write and compile a static Java method to be used as an SPJ:
Compile the Java source file that contains the method to produce a class file:
javac Payroll.java
For details, see Section 3, Writing SPJ Methods.
Note. The SQL/MX implementation of SPJs complies mostly, unless otherwise specified, with
SQL/JRT (Java Routines and Types), which extends the ANSI SQL/Foundation standard.
public class Payroll {
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();
}
}