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

Writing SPJ Methods
HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
3-9
Referring to Database Objects in an SPJ Method
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
{
Connection conn = DriverManager.getConnection("jdbc:sqlmx:");
PreparedStatement getNumOrders =
conn.prepareStatement("SELECT COUNT(order_date) " +
"FROM orders " +
"WHERE order_date = ?");
getNumOrders.setDate(1, date);
ResultSet rs = getNumOrders.executeQuery();
rs.next();
numOrders[0] = rs.getInt(1);
rs.close();
conn.close();
}
In the SPJ environment, the ORDERS table is qualified by default with the same
catalog and schema, SAMDBCAT.SALES, as the SPJ.
The default behavior takes effect only when getConnection() and
UDR_JAVA_OPTIONS do not contain catalog or schema properties. Catalog and
schema property values specified in UDR_JAVA_OPTIONS have higher precedence
over the default behavior. Catalog and schema property values in getConnection()
have higher precedence over both the default behavior and UDR_JAVA_OPTIONS.
To override the default catalog and schema values and associate a database
connection in an SPJ method with a different catalog or schema, specify the catalog or
schema properties during connection creation. For example, getConnection() in
this SPJ method specifies the catalog, CAT, which overrides the default catalog,
SAMDBCAT, while the default schema remains SALES:
public static void numDailyOrders(Date date,
int[] numOrders)
throws SQLException
{
Properties prop = new Properties();
prop.setProperty("catalog", "CAT");
Connection conn = DriverManager.getConnection("jdbc:sqlmx:", prop);
PreparedStatement getNumOrders =
conn.prepareStatement("SELECT COUNT(order_date) " +