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

Writing SPJ Methods

HP NonStop SQL/MX Guide to Stored Procedures in Java—540433-003

3-8

JDBC/MX-Based Java Method

A JDBC/MX-based Java method is from a Java program that contains SQL/MX

statements in JDBC/MX method calls. You can use this type of method to create an

SPJ that performs SQL operations on an SQL/MP or SQL/MX database.

For example, the adjustSalary() method in the Payroll class adjusts an

employee’s salary in the EMPLOYEE table:

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();

}

You do not have to explicitly load the JDBC/MX driver before establishing a connection

to the database. The SQL/MX UDR server automatically loads the JDBC/MX driver

when the SPJ is called.

To register this method as an SPJ in NonStop SQL/MX, use a CREATE PROCEDURE

statement. For details, see Section 4, Registering SPJs in NonStop SQL/MX.

For other examples of JDBC/MX-based SPJs, see Appendix A, Sample SPJs.

For information about JDBC/MX, see the

JDBC Driver for SQL/MX Programmer’s

Reference

Referring to Database Objects in an SPJ Method

In an SPJ method, you can refer to both SQL/MP and SQL/MX database objects, as

you would in other SQL/MX applications. SQL/MX database objects have three-part

ANSI names that include the catalog, schema, and object name. SQL/MP database