You can use named parameter markers instead of standard
parameter markers in CallableStatement objects
to assign values to IN or INOUT parameters and to register OUT parameters.
Before you begin
To ensure that applications with named parameters work correctly,
regardless of the data server type and version, before you use named
parameter markers in your applications, set the Connection or DataSource property
enableNamedParameterMarkers to DB2BaseDataSource.YES
.
Procedure
To use named parameter markers with CallableStatement objects,
follow these steps:
- Execute the Connection.prepareCall method
on an SQL statement string that contains named parameter markers.
The named parameter markers must follow the rules for SQL
host variable names.
You cannot mix named parameter markers
and standard parameter markers in the same SQL statement string.
Named
parameter markers are case-insensitive.
- If you do not know the names of the named
parameter markers in the CALL statement, or the mode of the parameters
(IN, OUT, or INOUT):
- Call the CallableStatement.getParameterMetaData method
to obtain a ParameterMetaData object with information
about the parameters.
- Call the ParameterMetaData.getParameterMode method
to retrieve the parameter mode.
- Cast the ParameterMetaData object
to a DB2ParameterMetaData object.
- Call the DB2ParameterMetaData.getParameterMarkerNames method
to retrieve the parameter names.
- For each named parameter marker that represents an OUT
parameter, use a DB2CallableStatement.registerJccOutParameterAtName method
to register the OUT parameter with a data type.
If you
use the same named parameter marker more than once in the same SQL
statement string, you need to call a registerJccOutParameterAtName method
for that parameter marker only once. All parameters with the same
name are registered as the same data type.
Restriction: You
cannot use standard JDBC CallableStatement.registerOutParameter methods
with named parameter markers. Doing so causes an exception to be thrown.
- For each named parameter marker for an input parameter,
use a DB2CallableStatement.setJccXXXAtName method
to assign a value to each named input parameter.
setJccXXXAtName methods
are inherited from DB2PreparedStatement.
If
you use the same named parameter marker more than once in the same
SQL statement string, you need to call a setJccXXXAtName method
for that parameter marker only once.
Recommendation: Do not use the same named parameter marker more than
once in the same SQL statement string if the input to that parameter
marker is a stream. Doing so can cause unexpected results.
- Execute the CallableStatement.
- Call CallableStatement.getXXX methods
or DB2CallableStatement.getJccXXXAtName methods
to retrieve output parameter values.
Example
The following code illustrates calling a stored procedure
that has one input VARCHAR parameter and one output INTEGER parameter,
which are represented by named parameter markers. The numbers to the
right of selected statements correspond to the previously described
steps.
…
CallableStatement cstmt =
con.prepareCall("CALL MYSP(:inparm,:outparm)");
// Create a CallableStatement object 1
((com.ibm.db2.jcc.DB2CallableStatement)cstmt).
registerJccOutParameterAtName("outparm", java.sql.Types.INTEGER);
// Register OUT parameter data type 3
((com.ibm.db2.jcc.DB2CallableStatement)cstmt).setJccStringAtName("inparm", "4567");
// Assign a value to inparm parameter 4
cstmt.executeUpdate(); // Call the stored procedure 5
int outssid = cstmt.getInt(2); // Get the output parameter value 6
cstmt.close();