Inserting/updating null in a timestamp field with PostgreSQL 8.x

• Key: DBUTILS-41
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: Unassigned
• Reporter: Malcolm McLean
• Affects Version/s: 1.1

Description

When inserting or updating a value to null on timestamp (date) field in a PostgreSQL database, the following error is returned:
column "foo" is of type timestamp without time zone but expression is of type character varying

Overriding the fillStatement method by doing something like:

QueryRunner run = new QueryRunner() {

      /* (non-Javadoc)
       * @see org.apache.commons.dbutils.QueryRunner#fillStatement(java.sql.PreparedStatement, java.lang.Object[])
       */
      @Override
      protected void fillStatement(PreparedStatement stmt, Object[] params) throws SQLException {
        if (params == null) {
          return;
        }
        for (int i = 0; i < params.length; i++) {
          if (params[i] != null) {
            stmt.setObject(i + 1, params[i]);
          } else {
            // VARCHAR works with many drivers regardless
            // of the actual column type (Maybe not with Postgres 8.x).  Oddly, NULL and
            // OTHER don't work with Oracle's drivers.
            stmt.setNull(i + 1, Types.NULL);
          }
        }
      }
    };

work, but it will have a problem if other databases are used instead of PostgreSQL.

Comments

[Henri Yandell]

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.

[Dan Fabulich]

Removing "Fix Version: 1.2" since this is really a dupe; it clutters the release notes.