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

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 (page 33).
2. Write and compile a static Java method to be used as an SPJ:
public class Payroll {
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);
ResultSet rs = getSalary.executeQuery();
rs.next();
newSalary[0] = rs.getBigDecimal(1);
rs.close();
conn.close();
}
}
Compile the Java source file that contains the method to produce a class file:
javac Payroll.java
For details, see Chapter 3: Writing SPJ Methods.
3. Configure the SPJ environment before registering or invoking SPJs:
• If you installed the NonStop Server for Java in a nonstandard location, set the JREHOME
location. See Setting the JREHOME Location (page 40).
• If you installed the JDBC/MX driver in a nonstandard location, set the UDR extensions
class path. See Setting the JDBC/MX Location (page 42).
• If the SPJ methods refer to application classes outside their external paths, set those
locations in the class path. See Setting the Class Path (page 43).
• If necessary, set other JVM startup options for the SPJ environment. See Controlling JVM
Startup Options (page 36).
• If necessary, configure Java security for the SPJ environment. See Establishing Java Security
(page 46).
4. As the super ID or schema owner, register each SPJ in NonStop SQL/MX by using the CREATE
PROCEDURE statement:
CREATE PROCEDURE samdbcat.persnl.adju stsalary(IN empnum NUMERIC(4),
IN percent FLOAT,
OUT newsalary NUMERIC(8,2))
EXTERNAL NAME 'Payroll.adjustSalary'
EXTERNAL PATH '/usr/mydir/myclasses'
18 Introduction