DatabaseMetaData getProcedureColumns stored procedure column names order?

user_8538776 0
Viewed 895

I use databasemetadata to get columns (read parameters) of a stored procedure on SQL server:

Connection connection = getConnection(); //getting the connection -   
DatabaseMetaData dbMetaData = connection.getMetaData();

HashMap<String, Integer> paramInfo = new HashMap<String, Integer>();
if (dbMetaData != null){
    ResultSet rs = dbMetaData.getProcedureColumns (null, null, sp_name.toUpperCase(), "%");
    while (rs.next())
        paramInfo.put(rs.getString(4), rs.getInt(6));
    rs.close();
}

Does getProcedureColumns() return the procedure columns in an ordered way? Meaning if in the database the stored procedure parameters are- abc(@a int,@b int,@c int), would I always get @a, @b and @c in an ordered way?

If yes, is there any documentation to suggest the same?

callable-statement mssql-jdbc database-metadata jdbc sql-server
1 Answers

All JDBC drivers are required to follow the JDBC specification and API documentation, and the API documentation of DatabaseMetaData.getProcedureColumns specifies:

Retrieves a description of the given catalog's stored procedure parameter and result columns.

Only descriptions matching the schema, procedure and parameter name criteria are returned. They are ordered by PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME and SPECIFIC_NAME. Within this, the return value, if any, is first. Next are the parameter descriptions in call order. The column descriptions follow in column number order.

If a driver does not follow this specification, then that is a bug in the driver. So in general you should be able to rely on above description.

Otherwise, check the values in columns COLUMN_TYPE (index 5) which specifies the type (in parameter, result set column, etc) and ORDINAL_POSITION (index 18) which describes the position (check the details in the javadoc).

user_4668620
Related