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

To register this method as an SPJ in NonStop SQL/MX, use a CREATE PROCEDURE statement. For
details, see Chapter 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.
How you qualify three-part object names in an SPJ method depends on the SQL/MX release that
you are using:
• Object Name Qualification Before NonStop SQL/MX Release 2.1.1 (page 55)
• Object Name Qualification in NonStop SQL/MX Release 2.1.1 and Later (page 55)
Object Name Qualification Before NonStop SQL/MX Release 2.1.1
Before SQL/MX Release 2.1.1 (ABX SPRs), the catalog and schema values of referenced database
objects are not set in the SPJ environment. As a result, you must fully qualify database objects that
are referenced in SPJ methods. This SPJ method uses the fully qualified object name,
SAMDBCAT.SALES.ORDERS:
public static void numDailyOrders(Date date,
int[] numOrders)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement getNumOrders =
conn.prepareStatement("SELECT COUNT(order_date) " +
"FROM samdbcat.sales.orders " +
"WHERE order_date = ?");
getNumOrders.setDate(1, date);
ResultSet rs = getNumOrders.executeQuery();
rs.next();
numOrders[0] = rs.getInt(1);
rs.close();
conn.close();
}
Using fully qualified object names in SPJ methods makes the SPJ methods less portable from one
system to another, where catalog and schema names might differ. However, if you do not fully
qualify the database object names, the default catalog and schema values will be the same as
those in the SYSTEM_DEFAULTS table.
Object Name Qualification in NonStop SQL/MX Release 2.1.1 and Later
In SQL/MX Release 2.1.1 and later, the SQL/MX UDR server propagates the values of the catalog
and schema where the SPJ is registered to the SPJ environment. By default, database connections
created in the SPJ method are associated with those catalog and schema values, meaning that
partially qualified objects with one- or two-part names in the SPJ method are qualified with the
same catalog and schema values as the SPJ. For example, this SPJ method, which is registered as
an SPJ in the SAMDBCAT.SALES schema, refers to the unqualified database object, ORDERS:
public static void numDailyOrders(Date date,
int[] numOrders)
throws SQLException
Accessing SQL/MP and SQL/MX Databases 55