1

I have a Table with a String[] and Time[] array column. Reading from the table works fine. But when I'm trying to create a record...

MyTableRecord r = txJooq.newRecord(MY_TABLE);
r.setId(id);
r.setStringArr("foo", "bar");
r.setTimeArr(LocalTime.of(7, 0), LocalTime.of(10, 0));
r.store();

... I get the following Exception.

org.jooq.exception.DataAccessException: SQL [null]; Can't infer the SQL type to use for an instance of [Ljava.lang.String;. Use setObject() with an explicit Types value to specify the type to use.
        at org.jooq_3.9.1.H2.debug(Unknown Source)
        at org.jooq.impl.Tools.translate(Tools.java:1983)
        at org.jooq.impl.AbstractBindContext.bindValue(AbstractBindContext.java:130)
        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:402)
        at org.jooq.impl.AbstractContext.visit(AbstractContext.java:168)
        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:402)
        at org.jooq.impl.AbstractContext.visit(AbstractContext.java:168)
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:345)
        at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:135)
        at ch.waschenda.backend.web.control.BootstrapController.createLandlord(BootstrapController.java:66)
        at ch.waschenda.backend.web.control.BootstrapController.lambda$bootstrap$0(BootstrapController.java:41)
        at com.github.witoldsz.ultm.internal.ThreadLocalTxManager.lambda$tx$2(ThreadLocalTxManager.java:81)
        at com.github.witoldsz.ultm.internal.ThreadLocalTxManager.txUnwrappedResult(ThreadLocalTxManager.java:55)
        at com.github.witoldsz.ultm.internal.ThreadLocalTxManager.txResult(ThreadLocalTxManager.java:67)
        at com.github.witoldsz.ultm.internal.ThreadLocalTxManager.tx(ThreadLocalTxManager.java:81)
        at ch.waschenda.backend.web.control.BootstrapController.bootstrap(BootstrapController.java:40)
        at ch.waschenda.backend.web.Api.bootstrap(Api.java:67)
        at spark.RouteImpl$1.handle(RouteImpl.java:61)
        at spark.http.matching.Routes.execute(Routes.java:61)
        at spark.http.matching.MatcherFilter.doFilter(MatcherFilter.java:130)
        at spark.embeddedserver.jetty.JettyHandler.doHandle(JettyHandler.java:50)
        at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:189)
        at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:119)
        at org.eclipse.jetty.server.Server.handle(Server.java:517)
        at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
        at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:242)
        at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:261)
        at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
        at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:75)
        at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:213)
        at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:147)
        at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
        at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
        at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of [Ljava.lang.String;. Use setObject() with an explicit Types value to specify the type to use.
        at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1051)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java)
        at org.jooq.tools.jdbc.DefaultPreparedStatement.setObject(DefaultPreparedStatement.java:189)
        at org.jooq.impl.DefaultBinding.set(DefaultBinding.java:1165)
        at org.jooq.impl.DefaultBindContext.bindValue0(DefaultBindContext.java:62)
        at org.jooq.impl.AbstractBindContext.bindValue(AbstractBindContext.java:127)
        ... 38 more

It seems that jOOQ is generating an invalid SQL Statement. It creates

values (
  ...
  ('foo', 'bar'), 
  ...
);

instead of the correct

values (
  ...
  '{"foo", "bar"}', 
  ...
);

I'm using:

  • org.jooq : jooq : 3.9.1
  • org.jooq : jooq-codegen : 3.9.1
  • org.postgresql : postgresql : 9.4.1212
  • postgres 9.6

1 Answer 1

4

Silly me, I configured jOOQ with the wrong SQL Dialect 🙈

new DefaultConfiguration().set(SQLDialect.POSTGRES)

Did the trick

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.