Commons DbUtils
  1. Commons DbUtils
  2. DBUTILS-18

[dbutils] Allow user to provide type information for input parameters

    Details

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

      Operating System: Linux
      Platform: PC

      Description

      I am having trouble using dbutils with the DB2 v8.1.3 JDBCdriver because my
      driver does not support the Types.OTHER datatype and that datatype is
      automatically assigned to input parameters whose values are null.

      As a workaround to this issue, I suggest adding another overload to the
      QueryRunner::query method, which accepts an additional Object[] list containing
      the types of the parameters being passed. Alternatively, you could create a
      Parameter object that encapsulates the type and value of the input parameter
      and pass an array of those objects to query.

        Activity

        Hide
        David Graham added a comment -

        I've changed the null handling to specify Types.VARCHAR because it works with
        every tested driver. If it still doesn't work in your case, just override
        fillStatement() to pass in the type for your driver. Below is a message from
        Ronald Dauster to the commons-user list. Many thanks to Ronald for the testing!

        Ronald Dauster wrote:
        As promised, I have tested different strategies for passing null values
        to
        different databases. Although I still have MS SQL Sever and Oracle 10
        on my list, I can already offser some results:

        I have testet 6 different strategies:
        1. PreparedStatement.setObject(col, null)
        (as proposed in this thread)
        2. setNull(col, Types.VARCHAR)
        3. setNull(col, Types.INTEGER);
        4. setNull(col, Types.OTHER)
        (the current strategy of dbutils)
        5. setNull(col, Types.NULL)
        6. setNull(col, exact type)
        (where the type has been determined by performing a query and
        using the ResultSetMetaData)

        I ran the tests agains the following databases

        Firebird 1.5/firebirdsql 1.5RC3
        Oracle 9/ Thin driver
        MySQL 4.0/Msql Connecttor 3.0 and mm.mysql 2.0.4
        MaxDB 7.5
        HSQLDB 1.7.1
        MS Access/ODBC Brdge

        and got the following results:
        MySQL, MaxDB, Firebird and HSQLDB work with every strategy
        Access fails with 1, 4, and 5
        Oracle fails with 1, 3, 4, 5, and, surprisingly, 6

        Strategry 2 (Types.VARCHAR) is the only one that worked in all cases.

        The test consists of an insert of one row into a table with four
        columns

        • a varchar used as key (receiving a non-null value)
        • a small varchar/varchar2 in Oracle
        • an integer/unconstraint number in Oracle
        • a timestamp/date in Oracle

        The Testprogram, scripts and complete results are available upon
        request.

        Ronald

        Show
        David Graham added a comment - I've changed the null handling to specify Types.VARCHAR because it works with every tested driver. If it still doesn't work in your case, just override fillStatement() to pass in the type for your driver. Below is a message from Ronald Dauster to the commons-user list. Many thanks to Ronald for the testing! Ronald Dauster wrote: As promised, I have tested different strategies for passing null values to different databases. Although I still have MS SQL Sever and Oracle 10 on my list, I can already offser some results: I have testet 6 different strategies: 1. PreparedStatement.setObject(col, null) (as proposed in this thread) 2. setNull(col, Types.VARCHAR) 3. setNull(col, Types.INTEGER); 4. setNull(col, Types.OTHER) (the current strategy of dbutils) 5. setNull(col, Types.NULL) 6. setNull(col, exact type) (where the type has been determined by performing a query and using the ResultSetMetaData) I ran the tests agains the following databases Firebird 1.5/firebirdsql 1.5RC3 Oracle 9/ Thin driver MySQL 4.0/Msql Connecttor 3.0 and mm.mysql 2.0.4 MaxDB 7.5 HSQLDB 1.7.1 MS Access/ODBC Brdge and got the following results: MySQL, MaxDB, Firebird and HSQLDB work with every strategy Access fails with 1, 4, and 5 Oracle fails with 1, 3, 4, 5, and, surprisingly, 6 Strategry 2 (Types.VARCHAR) is the only one that worked in all cases. The test consists of an insert of one row into a table with four columns a varchar used as key (receiving a non-null value) a small varchar/varchar2 in Oracle an integer/unconstraint number in Oracle a timestamp/date in Oracle The Testprogram, scripts and complete results are available upon request. Ronald

          People

          • Assignee:
            Unassigned
            Reporter:
            Corey Sanders
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development