3.8.x code generation with db2

[Incze Lajos]

i'm using jooq in a springboot-1.4.0 test project which advanced the jooq version number to 3.8.4.

in this test project, jooq generates java dsl for a db2 (expc) database using the generic org.jooq.util.jdbc.JDBCDatabase generator. all that worked fine up until springboot-1.3.6 which used jooq 3.7.4, but now the code generation fails with an error:

---
[ERROR] Could not load schemata 
org.jooq.exception.DataAccessException: SQL [select 1 "one"]; DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;select 1 "one";<table_expr>, DRIVER=4.21.29
---

obviously, db2 does not accept "select 1" (without a table expression) as a well formed sql.

my questions:

- is that something that is circumvented in the commercial org.jooq.util.db2.DB2Database generator?
- i could not find any generation meta/config option to keep away the generic driver to submit this type of select, is that possible?

(at the moment, i can go on by replacing springboot jooq dependency with 3.7.4)

[Lukas Eder]

Hi Incze,

Thank you very much for your enquiry. Spring boot by default links to the jOOQ Open Source Edition, which does not support DB2. You may be able to get things working to a certain extent (e.g. by using the JDBCDatabase generator), but that won't change the fact that the jOOQ runtime library doesn't support DB2 (which is why it generates "wrong" SQL)

You will need to replace Spring boot's jOOQ Open Source Edition by the jOOQ Trial Edition or the jOOQ Enterprise Edition and specify the SQLDialect.DB2 dialect when you configure your DSLContext / Configuration object:

DSL.using(datasource, SQLDialect.DB2)

   .selectOne()

   .fetch();

I hope this helps. If you have any additional questions, please do not hesitate to ask and I'm very happy to help.

Best Regards,

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.

[Incze Lajos]

thank you for the reply. let me clarify (i'm not an sql nerd, at all):

- it is not true, that JDBCDatabase implements a generic (least common denominator, universal) generator, usable for any database having a correct jdbc driver;

- one can't say, that the JDBCDatabase generated sql sentence in question (select 1 "one") is syntactically correct and db2 is bad at not accepting a well formed sql sentence (which error would be circumvented in a special db2 generator)

---

I had a simple use case, an application with some (not much) relatively complex queries (using an orm would really overcomplicate the life). I had some choices using native sql (e.g. though jpa, or something like ibatis), or some sort of dsl like querydsl or jooq. these queries utilize only jdbc-friendly things (constraint-based explicit table relations and really simple and everywhere present sql data types), so i was not suprised that all these choices worked perfectly with the db2jcc jdbc driver. (jooq is a very attractive opportunity for many reasons).

---

i completely understood (and, in fact, would support) the SQLDialect.DB2 license.

thanks, again.