Commons DbUtils
  1. Commons DbUtils
  2. DBUTILS-41

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

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor 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.

        Activity

        Dan Fabulich made changes -
        Fix Version/s 1.2 [ 12312139 ]
        Henri Yandell made changes -
        Fix Version/s 1.2 [ 12312139 ]
        Henri Yandell made changes -
        Status Open [ 1 ] Closed [ 6 ]
        Resolution Fixed [ 1 ]
        Dennis Lundberg made changes -
        Field Original Value New Value
        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.
        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:

        {code}
        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);
                  }
                }
              }
            };
        {code}

        work, but it will have a problem if other databases are used instead of PostgreSQL.
        Malcolm McLean created issue -

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development