CODEWITHSUNDEEP
jooq
metasync
metasync

Reputation: 348

Error binding OffsetDateTime [operator does not exist: timestamp with time zone <= character varying]

We are trying to execute dml which deletes records based on ZonedDateTime. We are using following code but running into an error.

dsl.execute ("delete from fieldhistory where createddate <= ? and object = ?", beforeDate.toOffsetDateTime(), objName)

Where beforeDate is ZonedDateTime and objectName is string

We are getting following error from postgres.

org.jooq.exception.DataAccessException: SQL [delete from fieldhistory where createddate <= ? and object = ?]; ERROR: operator does not exist: timestamp with time zone <= character varying
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 56
    at org.jooq_3.13.1.POSTGRES.debug(Unknown Source)
    at org.jooq.impl.Tools.translate(Tools.java:2751)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:755)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:385)
    at org.jooq.impl.DefaultDSLContext.execute(DefaultDSLContext.java:1144)

Questions is, how do we bind datetime value in Jooq?

Upvotes: 1

Views: 1097

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221106

For historic reasons, jOOQ binds all JSR-310 times as strings, not as the relevant object type. This is because until recently, JDBC drivers did not support the JSR-310 types natively, and as such, using a string was not a bad default.

Unfortunately, this leads to type ambiguities, which you would not have if either:

  • jOOQ didn't bind a string
  • you were using the code generator and thus type safe DSL API methods

As a workaround, you can do a few things, including:

Casting your bind variable explicitly

dsl.execute("delete from fieldhistory where createddate <= ?::timestamptz and object = ?",  
    beforeDate.toOffsetDateTime(), 
    objName)

Using the DSL API

dsl.deleteFrom(FIELDHISTORY)
   .where(FIELDHISTORY.CREATEDDATE.lt(beforeDate.toOffsetDateTime()))
   .and(FIELDHISTORY.OBJECT.eq(objName))
   .execute();

By writing your own binding

You can write your own data type binding and attach that to generated code, or to your plain SQL query, in case of which you would be in control of how the bind variable is sent to the JDBC driver. See: https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings/

For example:

DataType<OffsetDateTime> myType = SQLDataType.OFFSETDATETIME
                                             .asConvertedDataType(new MyBinding());
dsl.execute ("delete from fieldhistory where createddate <= {0} and object = {1}", 
    val(beforeDate.toOffsetDateTime(), myType), 
    val(objName))

There will be a fix in the future for this, so this won't be necessary anymore: https://github.com/jOOQ/jOOQ/issues/9902

Upvotes: 2

Related Questions