Uploaded image for project: 'Commons DbUtils'
  1. Commons DbUtils
  2. DBUTILS-41

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

    Details

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

      Linux CentOS 2.6.18-8.1.4.el5, PostgreSQL 8.2.4, Java 1.6.0_01

      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.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              mmclean Malcolm McLean
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: