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-9
Referring to Database Objects in an SPJ Method
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 on page 3-9

Object Name Qualification in NonStop SQL/MX Release 2.1.1 and Later on
page 3-9
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:sqlmx:");
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