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:
Silly me, I configured jOOQ with the wrong SQL Dialect 🙈
new DefaultConfiguration().set(SQLDialect.POSTGRES)
Did the trick