Learning about stored procedure parameter names using DB2ParameterMetaData methods

The DB2ParameterMetaData.getProcedureParameterName method lets you retrieve the defined name of a parameter in an SQL CALL statement.

Procedure

To invoke ParameterMetaData.getProcedureParameterName, you need to perform these basic steps:

Invoke the Connection.prepareCall method with the CALL statement as its argument to create a CallableStatement object.
Pass values to the input parameters (parameters that are defined as IN or INOUT in the CREATE PROCEDURE statement).
Register parameters that are defined as OUT in the CREATE PROCEDURE statement with specific data types.
Call the stored procedure.
Invoke CallableStatement.getParameterMetaData to retrieve information about the stored procedure parameters.
Cast the retrieved ParameterMetaData object as a DB2ParameterMetaData object.
Call the DB2ParameterMetaData.getProcedureParameterName method for each CALL statement parameter for which you need to retrieve the parameter name in the CREATE PROCEDURE statement.

Example

The following code demonstrates how to use DB2ParameterMetaData.getProcedureParameterName to determine the names that correspond to standard parameter markers in a stored procedure that is defined like this:

CREATE PROCEDURE SP 
 (OUT PARM CHAR(10), IN CHAR(10))

The numbers to the right of selected statements correspond to the previously-described steps.

Connection con;
…
CallableStatement cstmt = con.prepareCall("CALL SP(?, ?)");             1 
                                  // Create a CallableStatement object
cstmt.setString (2, "INPUT_VALUE");                                     2 
                                  // Set input parameter
cstmt.registerOutParameter (1, java.sql.Types.CHAR);                    3 
                                  // Register output parameter
cstmt.execute();                  // Call the stored procedure          4 
DB2ParameterMetaData md =                                               5,6 
  (DB2ParameterMetaData)cstmt.getParameterMetaData ();
md.getProcedureParameterName(1);  // Returns "PARM"                     7 
md.getProcedureParameterName(2);  // Returns "2"

The following code demonstrates how to use DB2ParameterMetaData.getProcedureParameterName to determine the names that correspond to named parameter markers in a stored procedure that is defined like this:

CREATE PROCEDURE SP 
 (OUT PARM CHAR(10), IN CHAR(10))

The numbers to the right of selected statements correspond to the previously-described steps.

Connection con;
…
CallableStatement cstmt = con.prepareCall("CALL SP(:output, :input)");     1 
                                  // Create a CallableStatement object
((DB2PreparedStatement)cstmt).setJccStringAtName("input", "INPUT_VALUE");  2 
                                  // Set input parameter
((DB2CallableStatement)cstmt).registerJccOutParameterAtName                3 
 ("output", java.sql.Types.CHAR);
                                  // Register output parameter
cstmt.execute();                  // Call the stored procedure             4 
DB2ParameterMetaData md =                                                  5,6 
  (DB2ParameterMetaData)cstmt.getParameterMetaData ();
md.getProcedureParameterName(1); // Returns "PARM"                         7 
md.getProcedureParameterName(2); // Returns "2"