Oracle 'TIMESTAMP WITH TIME ZONE' and Jooq = ORA-01843

[sims...@gmail.com]

Lukas,

We use Jooq for connecting to an oracle database.

Our usecase is to store timestamps with timezone and converting them to LocalDateTime in the Java-Object using a converter:

example Table:

CREATE TABLE TEST_TABLE

(

    ID CHAR(36) PRIMARY KEY NOT NULL,

    ORDERED_AT TIMESTAMP(6) WITH TIME ZONE

);

converter:

public class OffsetDateTimeToLocalDateTimeConverter implements Converter<OffsetDateTime, LocalDateTime> {

@Override public LocalDateTime from(OffsetDateTime offsetDateTime) {

if(offsetDateTime == null) {

return null;

}

return zonedDateTime(offsetDateTime).toLocalDateTime();

}

@Override public OffsetDateTime to(LocalDateTime localDateTime) {

if(localDateTime == null) {

return null;

}

return zonedDateTime(localDateTime).toOffsetDateTime();

}

private ZonedDateTime zonedDateTime(LocalDateTime localDateTime) {

return ZonedDateTime.of(localDateTime, ZoneId.systemDefault());

}

private ZonedDateTime zonedDateTime(OffsetDateTime offsetDateTime) {

return offsetDateTime.atZoneSameInstant(ZoneId.systemDefault());

}

@Override public Class<OffsetDateTime> fromType() {

return OffsetDateTime.class;

}

@Override public Class<LocalDateTime> toType() {

return LocalDateTime.class;

}

}

pojo:

public class TestTable implements Serializable {

 [...]

    private String        id;

    private LocalDateTime orderedAt;

   

[...]

}

Storing a Java-Object TestTable using jooq results in an insert Query as follows:

insert into "TEST_TABLE" (

  "ID", 

  "ORDERED_AT"

)

values (

  '910c5b72-e216-5cbb-a31d-7b63cc9ebb5c', 

  '2016-04-25T17:29+02:00'

)

which is answered by oracle with the message "[22008][1843] ORA-01843: Kein gültiger Monat"

it looks to me like the literal for TIMESTAMP WITH TIME ZONE is not correct, shouldn't it look like '2016-04-25 17:29:00 +02:00' instead of '2016-04-25T17:29+02:00'? Or is our config wrong?

Using Jooq Version 3.8.1

Cheers

Simon

[Lukas Eder]

Hi Simon,

jOOQ's current JSR 310 support is still flawed, unfortunately (and I'm not sure if Oracle's jdbc driver already supports it?)

I'm afraid the Converter won't be sufficient here. You can work around these issues by writing a data type binding that takes care of binding your custom type directly to JDBC. Some examples are shown here:

http://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings

I hope this helps. Let me know if you have any additional questions

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.