Re: Looks like INTERVAL type support is broken for PostgreSQL in jOOQ 2.6.2

[Lukas Eder]

Hello,

Thanks for reporting this

> Is it a bug or I just use jOOQ in wrong way?

From the exception message you've posted, I suggest that this is a
bug. Specifically, given the fact that your INTERVAL data type from
the database isn't recognised by the code generator (mapping it to
java.lang.Object, instead of org.jooq.types.XXX) indicates that
there's something missing in jOOQ. I have registered #2230 for this:
https://github.com/jOOQ/jOOQ/issues/2230

Cheers
Lukas

2013/2/16 <maslen...@gmail.com>:
> Hello,
>
> I'm trying to use jOOQ 2.6.2 with PostgreSQL 9.1.7
>
> I have table defined as
>
> CREATE TABLE schedule(
> ...
> duration INTERVAL NOT NULL,
> ...
> );
>
> Then I do code generation. Code generated for the duration field:
>
> /**
> * The table column <code>public.schedule.duration</code>
> * <p>
> * The SQL type of this item (interval) could not be mapped.<br/>
> * Deserialising this field might not work!
> */
> public final
> org.jooq.TableField<ula.schema.v1.generated.tables.records.ScheduleRecord,
> java.lang.Object> DURATION = createField("duration",
> org.jooq.util.postgres.PostgresDataType.getDefaultDataType("interval"),
> this);
>
> Ok, I decided to continue with manual converting PGInterval objects. It was
> also impossible, because when I try to insert line into the table, jOOQ
> always try to determine SQL type of object as DefaultDateType without
> dialect:
>
> DefaultDataType.getDataType(null, valueType); //
> https://github.com/jOOQ/jOOQ/blob/version-2.6.2/jOOQ/src/main/java/org/jooq/impl/Factory.java#L5993
>
> And I get exception:
>
> org.jooq.exception.SQLDialectNotSupportedException: Type class
> org.postgresql.util.PGInterval is not supported in dialect null
> at org.jooq.impl.AbstractDataType.getDataType(AbstractDataType.java:478)
> at org.jooq.impl.Factory.mostSpecific(Factory.java:5993)
> at org.jooq.impl.Factory.val(Factory.java:5968)
> at org.jooq.impl.Factory.val(Factory.java:5932)
> at org.jooq.impl.AbstractStoreQuery.addValue(AbstractStoreQuery.java:83)
> at org.jooq.impl.InsertImpl.set(InsertImpl.java:224)
> at org.jooq.impl.InsertImpl.set(InsertImpl.java:63)
>
> Is it a bug or I just use jOOQ in wrong way?
>
> --
> 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+...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Дмитрий Масленников]

Thank you.

Later I found that I can pass DayToSecond object as value for INTERVAL field and get value back as PGInterval. It still does not resolve code generation problem and does not allow to use all INTERVAL SQL data type fields.

Code generation does not work for me even if I use INTERVAL DAY TO SECOND data type in my DB schema.

[Lukas Eder]

Hello,

Yes, it is true that jOOQ's INTERVAL data types are not well integrated with Postgres at the time. The original implementation mainly focused on the correct variable binding of interval types. Code generation support is currently not integration tested

Cheers

Lukas

2013/2/19 Дмитрий Масленников <maslen...@gmail.com>

[Lukas Eder]

There is a related issue on this user group, regarding PGgeometry objects:

https://groups.google.com/d/topic/jooq-user/saaOD4_tz4c/discussion

These objects all cause the same NullPointerException within jOOQ, which is easy to fix. With this fix, PG* objects can be passed through the jOOQ API in some cases. More formal support will be added later, hopefully by jOOQ 3.1

2013/2/20 Lukas Eder <lukas...@gmail.com>