Learning about parameters in a PreparedStatement using ParameterMetaData methods

The IBM® Data Server Driver for JDBC and SQLJ includes support for the ParameterMetaData interface. The ParameterMetaData interface contains methods that retrieve information about the parameter markers in a PreparedStatement object.

About this task

ParameterMetaData methods provide the following types of information:
  • The data types of parameters, including the precision and scale of decimal parameters.
  • The parameters' database-specific type names. For parameters that correspond to table columns that are defined with distinct types, these names are the distinct type names.
  • Whether parameters are nullable.
  • Whether parameters are input or output parameters.
  • Whether the values of a numeric parameter can be signed.
  • The fully-qualified Java™ class name that PreparedStatement.setObject uses when it sets a parameter value.

Procedure

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

  1. Invoke the Connection.prepareStatement method to create a PreparedStatement object.
  2. Invoke the PreparedStatement.getParameterMetaData method to retrieve a ParameterMetaData object.
  3. Invoke ParameterMetaData.getParameterCount to determine the number of parameters in the PreparedStatement.
  4. Invoke ParameterMetaData methods on individual parameters.

Example

The following code demonstrates how to use ParameterMetaData methods to determine the number and data types of parameters in an SQL UPDATE statement. The numbers to the right of selected statements correspond to the previously-described steps.
Figure 1. Using ParameterMetaData methods to get information about a PreparedStatement
Connection con;
ParameterMetaData pmtadta;
int mtadtacnt;
String sqlType;
…
pstmt = con.prepareStatement(
  "UPDATE EMPLOYEE SET PHONENO=? WHERE EMPNO=?"); 
                                  // Create a PreparedStatement object   1 
pmtadta = pstmt.getParameterMetaData();                                  2 
                                  // Create a ParameterMetaData object
mtadtacnt = pmtadta.getParameterCount();                                 3 
                                  // Determine the number of parameters
System.out.println("Number of statement parameters: " + mtadtacnt);
for (int i = 1; i <= mtadtacnt; i++) {
  sqlType = pmtadta.getParameterTypeName(i);                             4 
                                  // Get SQL type for each parameter
 System.out.println("SQL type of parameter " + i " is " + sqlType);
}
…
pstmt.close();                       // Close the PreparedStatement