Commons DbUtils
  1. Commons DbUtils
  2. DBUTILS-31

fillStatement setNull bug with the Derby JDBC driver

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.0
    • Fix Version/s: 1.2
    • Labels:
      None
    • Environment:

      Derby 10.1.2.1

      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.

        Activity

        Hide
        Henri Yandell added a comment -

        Evaluate for 1.1.

        Show
        Henri Yandell added a comment - Evaluate for 1.1.
        Hide
        Francis Townsend added a comment -

        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.

        Show
        Francis Townsend added a comment - 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.
        Hide
        Liam Seamus Coughlin added a comment -

        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.

        Show
        Liam Seamus Coughlin added a comment - 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.
        Hide
        Dan Fabulich added a comment -

        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!

        Show
        Dan Fabulich added a comment - 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!
        Hide
        Henri Yandell added a comment -

        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.

        Show
        Henri Yandell added a comment - 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.

          People

          • Assignee:
            Dan Fabulich
            Reporter:
            Francis Townsend
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development