To call stored procedures, you invoke methods in the CallableStatement or PreparedStatement class.
Procedure
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 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.
- 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.
Example
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