UUID, Postgresql and INSERT in JOOQ

[EREZ KATZ]

Hi all,

We are encountering difficulties trying to perform a simple insert on a table that happens to have a UUID type field.

Here is our table code. I would like to add that we have no problem reading the fields in select statements. We have yet to try to use them a query parameters.

public class Notes extends TableImpl<Record>  {
    public final TableField<Record, UUID> UUID = createField("uuid", SQLDataType.UUID);
    public final TableField<Record, String> NOTE = createField("note", SQLDataType.VARCHAR.length(1000));


    public Notes() {
        super("accounting_notes");
    }
}

and this is our insert code:

dslContext.insertInto(NOTES_TABLE)

        .set(ACCOUNTING_NOTES_TABLE.UUID, UUID.randomUUID())
        .set(ACCOUNTING_NOTES_TABLE.NOTE, "noted")
        .execute();

This throws the following exception:

ERROR [2018-07-16 15:15:45,987] com.bendb.dropwizard.jooq.jersey.LoggingDataAccessExceptionMapper: Error handling a request: dc0265fcd880385a

! org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.UUID. Use setObject() with an explicit Types value to specify the type to use.

! at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1801)

! at org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:37)

! at org.postgresql.jdbc4.AbstractJdbc4Statement.setObject(AbstractJdbc4Statement.java:46)

! at org.jooq.tools.jdbc.DefaultPreparedStatement.setObject(DefaultPreparedStatement.java:189)

! at org.jooq.impl.DefaultBinding.set(DefaultBinding.java:1086)

! at org.jooq.impl.DefaultBindContext.bindValue0(DefaultBindContext.java:62)

! at org.jooq.impl.AbstractBindContext.bindValue(AbstractBindContext.java:127)

! at org.jooq.impl.Val.accept(Val.java:96)

! at org.jooq.impl.AbstractBindContext.bindInternal(AbstractBindContext.java:257)

! at org.jooq.impl.AbstractBindContext.visit0(AbstractBindContext.java:91)

! at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:394)

! at org.jooq.impl.AbstractContext.visit(AbstractContext.java:159)

! at org.jooq.impl.QueryPartList.accept(QueryPartList.java:104)

! at org.jooq.impl.AbstractBindContext.bindInternal(AbstractBindContext.java:257)

! at org.jooq.impl.AbstractBindContext.visit0(AbstractBindContext.java:91)

! at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:394)

! at org.jooq.impl.AbstractContext.visit(AbstractContext.java:159)

! at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:344)

! at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:135)

Versions:

   postgres 9.6 

   postgres jdbc : 9.1-901

   jooq 3.8.4  (we tried jooq3.9.1 with the same results...)

What are we doing wrong?

I could see that jooq was passing the uuid object 'as is' to the jdbc driver, which in turn has no notion of UUID 

What would be the correct way to achieve this functionality? 

Cordially,

 Erez Katz

[Lukas Eder]

Hi Erez,

I suspect your version of the postgresql JDBC driver didn't support UUID types yet. Have you tried upgrading your driver? Your version seems to be from 2011!

http://search.maven.org/#artifactdetails%7Cpostgresql%7Cpostgresql%7C9.1-901.jdbc4%7Cjar

Cheers,

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+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.