DSL Context Query using JOOQ classes having compatibility issue with Postgres

[selvait90]

In existing Java application, I am trying to migrate the database from SQL Server to Postgres. Code using JOOQ generated tables classes and it was working good with MSSQL. After migrated the database to Postgres and generated the Java Class for tables using JOOQ, DSL context pointing to JOOQ generated tables creates queries with open( [ ) and close( ] ) brackets which was incompatible with Postgres. Could you please help how should i handle this issue

Code:

val HE = RR_HISTORY_EXPERT
val newId = dsl.insertInto(HE).set(HE.EXPERT_ID, consultantId)
       .set(HE.RR_USER_ID, ruid)
       .set(HE.CREATED_DT, now)
       .returning(HE.HISTORY_EXPERT_ID).fetchOne().getValue(HE.HISTORY_EXPERT_ID)

Error Log:

org.jooq.exception.DataAccessException: SQL [insert into [rr_schema].[rr_history_expert] ([expert_id], [rr_user_id], [created_dt]) values (?, ?, ?)]; ERROR: syntax error at or near "["
  Position: 13
    at org.jooq.impl.Utils.translate(Utils.java:1078)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:462)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:302)
    at org.jooq.impl.InsertImpl.fetchOne(InsertImpl.java:505)
    at com.ridgetopresearch.db.impl.sqlserver.ConsultantDaoDbImpl$$anonfun$getConsultantHistory$1.apply(ConsultantDaoDbImpl.scala:369)
    at com.ridgetopresearch.db.impl.sqlserver.ConsultantDaoDbImpl$$anonfun$getConsultantHistory$1.apply(ConsultantDaoDbImpl.scala:360)
    at com.ridgetopresearch.db.Database$$anonfun$withDbDsl$1.apply(Database.scala:302)
    at com.ridgetopresearch.db.Database$$anonfun$withDbDsl$1.apply(Database.scala:300)
    at com.ridgetopresearch.db.Database$.withConnection(Database.scala:225)
    at com.ridgetopresearch.db.Database$.withDbDsl(Database.scala:300)
    at com.ridgetopresearch.db.Database$.withDbDslTransaction(Database.scala:310)
    at com.ridgetopresearch.db.impl.sqlserver.ConsultantDaoDbImpl.getConsultantHistory(ConsultantDaoDbImpl.scala:360)
    at com.ridgetopresearch.cache.ConsultantHistoryCache$.com$ridgetopresearch$cache$ConsultantHistoryCache$$getTheOldWay$2(DataCache.scala:145)
    at com.ridgetopresearch.cache.ConsultantHistoryCache$$anonfun$makeAndGetHistory$1.apply(DataCache.scala:148)
    at com.ridgetopresearch.cache.ConsultantHistoryCache$$anonfun$makeAndGetHistory$1.apply(DataCache.scala:148)
    at com.ridgetopresearch.cache.BasicDataCache.getOrCompute(DataCache.scala:77)
    at com.ridgetopresearch.cache.ConsultantHistoryCache$.makeAndGetHistory(DataCache.scala:148)
    at com.ridgetopresearch.cache.ConsultantHistoryCache.makeAndGetHistory(DataCache.scala)
    at com.ridgetopresearch.rrportal.action.BaseDispatchAction.execute(BaseDispatchAction.java:63)
    at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
    at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
    at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
    at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at com.ridgetopresearch.rrportal.servlet.CatchAllServlet.service(CatchAllServlet.java:130)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:800)
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:587)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
    at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)
    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1125)
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1059)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
    at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:215)
    at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:110)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
    at org.eclipse.jetty.server.Server.handle(Server.java:497)
    at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310)
    at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:248)
    at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:620)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:540)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "["
  Position: 13
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)
    at org.jooq.tools.jdbc.DefaultPreparedStatement.executeUpdate(DefaultPreparedStatement.java:83)
    at org.jooq.impl.AbstractStoreQuery.execute(AbstractStoreQuery.java:295)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:298)
    ... 44 more

[lukaseder]

Hello, and sorry for the delay.

Your dsl object still references SQLDialect.SQLSERVER, which causes table / column names to be quoted using MSSQL-style brackets. You will want to switch the SQLDialect when you execute the same query using PostgreSQL.

I hope this helps

[selvait90]

Thanks @lukaseder , It helped to fix the problem.