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

" sales.orders ORDERS, persnl.employee EMPS " +
" WHERE AMOUNTS.ordernum = ORDERS.ordernum " +
" AND ORDERS.salesrep = EMPS.empnum " +
" ORDER BY ORDERS.ordernum ";
PreparedStatement ps2 = c.prepareStatement(s);
ps2.setString(1, onOrAfter);
// Assign the returned result set object to the first element of a
// java.sql.ResultSet[] output array
orders[0] = ps2.executeQuery();
For the complete example, see the ORDERSUMMARY Stored Procedure (page 121).
CAUTION: In an SPJ method that returns result sets, do not explicitly close the default connection
or the statement object. SQL/MX closes the connection used to return result sets after it finishes
processing the result sets. If you close the connection on which the result sets are being returned,
those result sets will be lost, and the calling application will not be able to process them.
Using the main() Method
You can use the main() method of a Java class file as an SPJ method. The main() method is
different from other Java methods because it accepts input values in an array of
java.lang.String objects and does not return any values in its array parameter.
For example, you can register this main() method as an SPJ:
public static void main (java.lang.String [] args) {
...
}
When you register a main() method as an SPJ, the CREATE PROCEDURE statement can contain
zero or more SQL parameters, even though the underlying main() method has only one array
parameter. All the SQL parameters of the SPJ must have the character string data type, CHAR or
VARCHAR, and be declared with the IN mode.
If you specify the optional Java signature in the EXTERNAL NAME clause of the CREATE PROCEDURE
statement, the signature must be (java.lang.String []). For more information about creating
an SPJ, see the Using the CREATE PROCEDURE Statement (page 59).
Null Input and Output
You can pass a null value as input to or output from an SPJ method, provided that the Java data
type of the parameter supports nulls. Java primitive data types do not support nulls. However, Java
wrapper classes that correspond to primitive data types do support nulls. If a null is input or output
for a parameter that does not support nulls, NonStop SQL/MX raises an error condition.
To anticipate null input or output for your SPJ, use Java wrapper classes instead of primitive data
types in the method signature.
For example, this Java method uses a Java primitive data type in its signature where no null values
are expected:
public static void employeeJob(int empNum)
This Java method also uses a Java wrapper class in its signature to anticipate a possible returned
null value:
public static void employeeJob(Integer[] jobCode)
Static Java Variables
To ensure that your SPJ method is portable, you should avoid declaring static variables in the
method. NonStop SQL/MX does not ensure the scope and persistence of static Java variables.
52 Writing SPJ Methods