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-10
Referring to Database Objects in an SPJ Method
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) " +
"FROM orders " +
"WHERE order_date = ?");
getNumOrders.setDate(1, date);
ResultSet rs = getNumOrders.executeQuery();
rs.next();
numOrders[0] = rs.getInt(1);
rs.close();
conn.close();
}