SQL/MX 3.2 Guide to Stored Procedures in Java (H06.25+, J06.14+)

Writing SPJ Methods
HP NonStop SQL/MX Release 3.2 Guide to Stored Procedures in Java—691166-001
3-8
Referring to Database Objects in an SPJ Method
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
objects have Guardian file names that include the node, volume, subvolume, and file
name. Typically, SQL/MP aliases, which are three-part logical names, are used to refer
to SQL/MP database objects. For more information about database object names, see
the SQL/MX Reference Manual.