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

{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
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:default:connection", 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();
}
Be aware that overriding the default values by using getConnection() or UDR_JAVA_OPTIONS
requires you to hard-code the catalog and schema values and might make SPJ methods less portable
across systems.
Exception Handling
For SPJ methods that access an SQL/MP or SQL/MX database, no special code is necessary for
handling exceptions. If an SQL operation fails inside the SPJ, the error message associated with
the failure is returned to the application that issues the CALL statement.
56 Writing SPJ Methods