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:
- 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 callsLiteral 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.
- 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.
- 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.
- 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.
- If the stored procedure returns multiple result sets, retrieve
the result sets.
Restriction: IBM Informix data
sources do not support multiple result sets.
- Invoke the CallableStatement.getXXX methods
to retrieve values from the OUT parameters or INOUT parameters.
- Invoke the CallableStatement.close method
to close the CallableStatement object when you
have finished using that object.
The following code illustrates calling a stored procedure
that has one input parameter, four output parameters, and no returned
ResultSets.
The numbers to the right of selected statements correspond to the
previously-described steps.
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