Calling stored procedures in JDBC applications

To call stored procedures, you invoke methods in the CallableStatement or PreparedStatement class.

The basic steps for calling a stored procedures using standard CallableStatement methods are:

1. Invoke the Connection.prepareCall method with the CALL statement as its argument to create a CallableStatement object.

   You can represent parameters with standard parameter markers (?), named parameter markers, or named parameters. You can mix named parameters and standard parameter markers in the same CALL statement, but you cannot mix named parameter markers with standard parameter markers or named parameters.

   Restriction: The parameter types that are permitted depend on whether the data source supports dynamic execution of the CALL statement. DB2® for z/OS® does not support dynamic execution of the CALL statement. For a call to a stored procedure that is on a DB2 for z/OS database server, the parameters can be parameter markers or literals, but not expressions. Even if all parameters are literals, you cannot use Statement methods to execute CALL statements. You must use PreparedStatement methods or CallableStatement methods. The following table lists the types of literals that are supported, and the JDBC types to which they map.

   Table 1. Supported literal types in parameters in DB2 for z/OS stored procedure calls

   Literal parameter type	JDBC type	Examples
   Integer	java.sql.Types.INTEGER	-122, 40022, +27
   Floating-point decimal	java.sql.Types.DOUBLE	23E12, 40022E-4, +2723E+15, 1E+23, 0E0
   Fixed-point decimal	java.sql.Types.DECIMAL	-23.12, 40022.4295, 0.0, +2723.23, 10000000000
   Character	java.sql.Types.VARCHAR	'Grantham Lutz', 'O''Conner', 'ABcde?z?'
   Hexadecimal	java.sql.Types.VARBINARY	X'C1C30427', X'00CF18E0'
   Unicode string	java.sql.Types.VARCHAR	UX'0041', UX'0054006500730074'

   Important: In a prepareCall method invocation, you cannot specify the scrollability, updatability, or holdability of result sets that are returned from a stored procedure. Those characteristics are determined by the stored procedure code, when it declares the cursors for the result sets that are returned. If you specify any of the forms of prepareCall that include scrollability, updatability, or holdability parameters, the IBM® Data Server Driver for JDBC and SQLJ does not use those parameter values. A prepareCall method with scrollability, updatability, or holdability parameters applies only to preparation of SQL statements other than the CALL statement.
2. Invoke the CallableStatement.setXXX methods to pass values to the input parameters (parameters that are defined as IN or INOUT in the CREATE PROCEDURE statement).

   This step assumes that you use standard parameter markers or named parameters. Alternatively, if you use named parameter markers, you use IBM Data Server Driver for JDBC and SQLJ-only methods to pass values to the input parameters.

   Restriction: If the data source does not support dynamic execution of the CALL statement, you must specify the data types for CALL statement input parameters exactly as they are specified in the stored procedure definition.
   Restriction: Invoking CallableStatement.setXXX methods to pass values to the OUT parameters is not supported. There is no need to set values for the OUT parameters of a stored procedure because the stored procedure does not use those values.
3. Invoke the CallableStatement.registerOutParameter method to register parameters that are defined as OUT in the CREATE PROCEDURE statement with specific data types.

   This step assumes that you use standard parameter markers or named parameters. Alternatively, if you use named parameter markers, you use IBM Data Server Driver for JDBC and SQLJ-only methods to register OUT parameters with specific data types.

   Restriction: If the data source does not support dynamic execution of the CALL statement, you must specify the data types for CALL statement OUT, IN, or INOUT parameters exactly as they are specified in the stored procedure definition.
4. Invoke one of the following methods to call the stored procedure:

   CallableStatement.executeUpdate

   Invoke this method if the stored procedure does not return result sets.

   CallableStatement.executeQuery

   Invoke this method if the stored procedure returns one result set.

   You can invoke CallableStatement.executeQuery for a stored procedure that returns no result sets if you set property allowNullResultSetForExecuteQuery to DB2BaseDataSource.YES (1). In that case, CallableStatement.executeQuery returns null. This behavior does not conform to the JDBC standard.

   CallableStatement.execute

   Invoke this method if the stored procedure returns multiple result sets, or an unknown number of result sets.

   Restriction: IBM Informix® data sources do not support multiple result sets.

5. If the stored procedure returns multiple result sets, retrieve the result sets.
   Restriction: IBM Informix data sources do not support multiple result sets.
6. Invoke the CallableStatement.getXXX methods to retrieve values from the OUT parameters or INOUT parameters.
7. Invoke the CallableStatement.close method to close the CallableStatement object when you have finished using that object.

int ifcaret;
int ifcareas;
int xsbytes;
String errbuff;
Connection con;
CallableStatement cstmt;
ResultSet rs;
…
cstmt = con.prepareCall("CALL DSN8.DSN8ED2(?,?,?,?,?)");                1 
                                  // Create a CallableStatement object
cstmt.setString (1, "DISPLAY THREAD(*)");                               2 
                                  // Set input parameter (DB2 command) 
cstmt.registerOutParameter (2, Types.INTEGER);                          3 
                                  // Register output parameters
cstmt.registerOutParameter (3, Types.INTEGER);
cstmt.registerOutParameter (4, Types.INTEGER);
cstmt.registerOutParameter (5, Types.VARCHAR);
cstmt.executeUpdate();            // Call the stored procedure          4 
ifcaret = cstmt.getInt(2);        // Get the output parameter values    6 
ifcareas = cstmt.getInt(3);
xsbytes = cstmt.getInt(4);
errbuff = cstmt.getString(5);
cstmt.close();                                                          7