Thanks for your clarification. I opened issue at TEIID side, so we will see if adding support for *LIMIT a OFFSET b* (dialect DEFAULT) is possible.
Dne pátek 15. června 2018 17:04:52 UTC+2 Lukas Eder napsal(a): > > Hi Lukas > > There isn't a reliable way forward here. Each dialect is subtly different, > and even if several dialects do support the LIMIT a, b syntax, not all of > them allow for expressions, subqueries, etc. in that syntax, for example. > This is the main reason why jOOQ doesn't have something like an external > config file where users can just override the individual behaviour of each > clause / syntax element. > > I'm assuming that you're currently using *some* SQLDialect (such as HSQLDB > or DEFAULT?). You could, of course, patch your jOOQ distribution for that > dialect, and generate something else there. Or, you could patch your jOOQ > distribution by adding the TEIID dialect to SQLDialect, and then tracking > all usages of e.g. HSQLDB or DEFAULT and add TEIID there as well. The > latter approach would be a tremendous amount of work, but at least you > would be in control of the generated SQL. But you would also be stuck to > the minor release that you're patching, probably. > > Another option would be to ask the TEIID team to support more SQL > syntaxes. After all the idea behind TEIID is to abstract over many other > SQL and NoSQL dialects, so being restrictive in terms of what SQL syntax is > accepted is probably not a good idea. From what I understand, they're > already doing that, just well enough (yet). For example, the jOOQ parser > will accept any type of syntax for: > > - LIMIT a > - LIMIT a, b > - OFFSET a LIMIT b > - LIMIT a OFFSET b > - OFFSET a ROWS FETCH NEXT b ROWS ONLY (and variations of this) > - TOP a > - TOP a START AT b > - ... > > You could indeed try patching the SQLDialect for the scope of the LIMIT > clause from within a VisitListener. I have not tried this myself yet - but > it might just work, if you can ensure that there will be no thread-safety > issues. Or you use the VisitListener to prevent the LIMIT from being > rendered (by wrapping it in a comment) and render your own. > > As always with requests for using jOOQ for new dialects - you are on your > own to some extent. All options are hack-ish and brittle. After all, > they're all based on choosing a random SQLDialect, whose behaviour can > change subtly in any future minor release at our own discretion. E.g. > instead of generating LIMIT a, b on MySQL, we might suddenly choose to > generate LIMIT a OFFSET b, if there is some advantage to that syntax. > > I hope this helps, > Lukas > > Am Fr., 15. Juni 2018 um 16:47 Uhr schrieb <[email protected] <javascript:> > >: > >> Hi, >> I use jooq only as SQL builder. However I run into trouble with >> generation of valid SQL queries with TEIID dialect (not supported by jooq). >> *It >> requires limit clause in format "LIMIT 10,20" instead of "LIMIT 10 OFFSET >> 20".* >> This format is supported by CUBRID but I cannot switch whole sql >> generator into CUBRID syntax. >> >> Is it possible to use different dialects for select/where and different >> one for limit clause for example? Or is it possible to customize my own >> dialect with overwritten org.jooq.impl.Limit#accept method? >> >> I really do not want to use regexp to "fix" result of jooq's getSQL() >> string result. >> >> What is preferable way to achieve my goal? >> >> Thanks for any ideas! >> >> 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 [email protected] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
