Using named parameter markers with CallableStatement objects

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.

About this task

Procedure

To use named parameter markers with CallableStatement objects, follow these steps:

  1. 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.

  2. 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):
    1. Call the CallableStatement.getParameterMetaData method to obtain a ParameterMetaData object with information about the parameters.
    2. Call the ParameterMetaData.getParameterMode method to retrieve the parameter mode.
    3. Cast the ParameterMetaData object to a DB2ParameterMetaData object.
    4. Call the DB2ParameterMetaData.getParameterMarkerNames method to retrieve the parameter names.
  3. 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.
  4. 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.
  5. Execute the CallableStatement.
  6. 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();

The following code illustrates the use of named parameter markers in a PL/SQL block. The numbers to the right of selected statements correspond to the previously described steps.

…
// Read in a PL/SQL block, and assign it to String variable plsql
CallableStatement cstmt = con.prepareCall(plsql);
                                     // Create a CallableStatement object  1 
DB2ParameterMetaData pm = 
  (DB2ParameterMetaData)cs.getParameterMetaData(); 
                                     // Get ParameterMetaData, cast it to  2a,2b 
                                     // DB2ParameterMetaData
String[] markers = pm.getParameterMarkerNames();
                                     // Get parameter marker names         2c 
// Process ParameterMetaData. Assume that the first parameter marker
// is the only OUT parameter, and it has an INTEGER data type.
…
String parameterName = markers[0];   // Get OUT parameter name
((com.ibm.db2.jcc.DB2CallableStatement)cstmt).
   registerJccOutParameterAtName(parameterName, java.sql.Types.INTEGER);
                                     // Register OUT parameter data type   3 
// Assign a values to input parameters                                     4 
…
cstmt.executeUpdate();            // Call the stored procedure             5 
int outval = cs.getJccIntAtName(parameterName);
// Get the output parameter value using the parameter name                 6 
cstmt.close();