fillStatement setNull bug with the Derby JDBC driver

• Key: DBUTILS-31
• Type: Improvement
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Dan Fabulich
• Reporter: Francis Townsend
• Affects Version/s: 1.0
• Fix Version/s: 1.2

Description

This has been documented many times before, but I was not happy with the existing code fixes. The following small code snippet should fix it for all conforming JDBC drivers.

protected void fillStatement(PreparedStatement stmt, Object[] params)
        throws SQLException {

        if (params == null) {
            return;
        }
        ParameterMetaData pmd = stmt.getParameterMetaData();
        for (int i = 0; i < params.length; i++) {
            if (params[i] != null) {
                stmt.setObject(i + 1, params[i]);
            } else {
                stmt.setNull(i + 1, pmd.getParameterType(i + 1));
            }
        }
    }

The only difference is that you get the parameter meta data and pass that type information to the setNull method. This should neatly fix this problem, with a very slight additional overhead.

Comments

[Henri Yandell]

Evaluate for 1.1.

[Francis Townsend]

This code was coded for the JDBC 3.0 specification. It would need to be modified to run with JDBC 2.1 drivers (check the JDBCMajor version to determine which code to use).

Also, I have read that the Oracle JDBC driver throws an Exception. Unfortunately, it does not look as if there is no method that can determine this before calling the getParameterType method. Which means we would need to catch the exception and role back to the previous code, namely use the VARCHAR type when setting null. This would always be thrown by the Oracle driver, severely slowing it down.

So, I would suggest we hold off putting this into the next release until we have a better solution.

[Liam Seamus Coughlin]

I think this is the best of the proposed solutions – it's certainly the simplest, and given that future versions of dbutils will be focusing on java 5+ I don't think we should be worrying about ancient versions of JDBC.

[Dan Fabulich]

Unfortunately, Oracle 10.2 JDBC drivers still don't support getParameterMetaData, even as recently as Oct 2008. http://forums.oracle.com/forums/thread.jspa?threadID=585880

You have to register to read that link (it's free but it's a hassle). But the most recent post is confirming that it's still broken in Oct 2008. If you call getParameterType, you get error ORA-17023 "Unsupported feature." Lame!

svn ci -m "Merging in Dab Fabulich's work on https://svn.apache.org/repos/asf/commons/sandbox/dbutils/bugfixing from -r741987:747723. Resolving DBUTILS-34 - DBUTILS-37 - DBUTILS-29 - DBUTILS-14 - DBUTILS-31 - DBUTILS-39 - DBUTILS-41 - DBUTILS-44 - DBUTILS-33 - DBUTILS-42 - DBUTILS-40"

Sending pom.xml
Sending src/java/org/apache/commons/dbutils/BasicRowProcessor.java
Sending src/java/org/apache/commons/dbutils/BeanProcessor.java
Sending src/java/org/apache/commons/dbutils/QueryRunner.java
Adding src/java/org/apache/commons/dbutils/handlers/AbstractListHandler.java
Sending src/java/org/apache/commons/dbutils/handlers/ArrayListHandler.java
Sending src/java/org/apache/commons/dbutils/handlers/BeanListHandler.java
Sending src/java/org/apache/commons/dbutils/handlers/ColumnListHandler.java
Deleting src/java/org/apache/commons/dbutils/handlers/GenericListHandler.java
Sending src/java/org/apache/commons/dbutils/handlers/MapListHandler.java
Sending src/test/org/apache/commons/dbutils/BaseTestCase.java
Adding src/test/org/apache/commons/dbutils/QueryRunnerTest.java
Transmitting file data .........
Committed revision 747724.