Fun with JDBC CallableStatements

I spent the better part of a day debugging an issue involving stored procedure calls over Spring JDBC, and came across some interesting gotchas which I felt might help others save some time in the future, so I thought I'd write a quick article about it.

Basically, I was given a defect where decimal values from the database were being rounded somewhere in my service method, and was able to isolate it to within my StoredProcedureImpl class. In Spring JDBC, a common approach to calling a stored procedure is to extend StoredProcedure, wherein you declare the input and output parameters and their corresponding types:

    private class MyStoredProcedure extends StoredProcedure {        
        private static final String SQL = "sysdate";

        public MyStoredProcedure(DataSource ds) {
            setDataSource(ds);
            setFunction(true);
            setSql(SQL);
            declareParameter(new SqlOutParameter("date", Types.DATE));
            compile();
        }

        public Map execute() {
            return execute(new HashMap());
        }
    }

This works well, and is indeed an elegant way of calling a stored procedure, as it eliminates all of the JDBC nastiness (try / catch / finally (try / finally / etc / etc) / etc). The problem lies in the (obscure?) fact that when you specify DECIMAL or NUMERIC types for input or output parameters in the JDBC API, you are expected to also specify a scale, or it will default to 0 (and either truncate or round, depending on your database driver).

From the Java API for CallableStatement:

void registerOutParameter(int parameterIndex,
                          int sqlType,
                          int scale)
                          throws SQLException

    Registers the parameter in ordinal position parameterIndex 
to be of JDBC type sqlType. All OUT parameters must be 
registered before a stored procedure is executed.

    The JDBC type specified by sqlType for an OUT parameter 
determines the Java type that must be used in the get method 
to read the value of that parameter.

    This version of registerOutParameter should be used 
when the parameter is of JDBC type NUMERIC or DECIMAL.

    Parameters:
        parameterIndex - the first parameter is 1, the second 
        is 2, and so on
        sqlType - the SQL type code defined by java.sql.Types.
        scale - the desired number of digits to the right 
           of the decimal point. It must be greater than or 
           equal to zero. 

As it turns out, Spring JDBC's SqlParameter class didn't support setting scale on them until 2.0.5 (and here), and SqlOutParameter didn't have this in a final release until 2.5.

So, to make a long story short (too late, I know), if you are calling stored procedures in JDBC which use DECIMAL or NUMERIC parameters in either the input or output, you must specify a scale to use, and if you are currently running a non-current version of Spring (less than 2.5.1), this is a great reason to upgrade.

P.S. When researching this issue, I noticed a new set of classes in the Spring JDBC set (SimpleJdbc) which looked cool; they may warrant an article later on...