Using parameter metadata

Download JDBC driver

To query a SQLServerPreparedStatement or a SQLServerCallableStatement object about the parameters that they contain, the Microsoft JDBC Driver for SQL Server implements the SQLServerParameterMetaData class. This class contains numerous fields and methods that return information in the form of a single value.

To create a SQLServerParameterMetaData object, you can use the getParameterMetaData methods of the SQLServerPreparedStatement and SQLServerCallableStatement classes.

In the following example, an open connection to the AdventureWorks2022 sample database is passed in to the function, the getParameterMetaData method of the SQLServerCallableStatement class is used to return a SQLServerParameterMetaData object, and then various methods of the SQLServerParameterMetaData object are used to display information about the type and mode of the parameters that are contained within the HumanResources.uspUpdateEmployeeHireInfo stored procedure.

public static void getParameterMetaData(Connection con) {
    try(CallableStatement cstmt = con.prepareCall("{call HumanResources.uspUpdateEmployeeHireInfo(?, ?, ?, ?, ?)}");) {
        ParameterMetaData pmd = cstmt.getParameterMetaData();
        int count = pmd.getParameterCount();
        for (int i = 1; i <= count; i++) {
            System.out.println("TYPE: " + pmd.getParameterTypeName(i) + " MODE: " + pmd.getParameterMode(i));
        }
    }
    // Handle any errors that may have occurred.
    catch (SQLException e) {
        e.printStackTrace();
    }
}

Note

There are some limitations when using the SQLServerParameterMetaData class with prepared statements.

With Microsoft JDBC Driver 6.0 (or higher) for SQL Server: When using SQL Server 2008 or 2008 R2, the JDBC driver supports SELECT, DELETE, INSERT, and UPDATE statements as long as these statements does not contain subqueries and/or joins.

MERGE queries are also not supported for SQLServerParameterMetaData class when using SQL Server 2008 or 2008 R2. For SQL Server 2012 and higher versions parameter metadata with complex queries are supported.

Retrieval of parameter metadata for encrypted columns are not supported. With Microsoft JDBC Driver 4.1 or 4.2 for SQL Server: The JDBC driver supports SELECT, DELETE, INSERT, and UPDATE statements as long as these statements does not contain subqueries and/or joins. MERGE queries are also not supported for SQLServerParameterMetaData class.