Hi Marcus, Interesting, is that a documented limitation of JTDS? We've experienced the limit with the official JDBC driver to be 2100 bind variables, after which jOOQ automatically inlines all your bind variables. Some more background can be seen in a recent issue, where we fixed jOOQ to limit PostgreSQL bind variables to 32767: https://github.com/jOOQ/jOOQ/issues/5701#issuecomment-265830847
Perhaps, JTDS tries to support both SQL Server and Sybase ASE, as the latter has a limit of 2000. If this is documented in JTDS, we'll fix jOOQ accordingly. I have registered an issue for this: https://github.com/jOOQ/jOOQ/issues/5954 In the meantime, I have these suggestions for you: 1) Try using Microsoft's JDBC driver. You should no longer experience this issue. 2) Don't use large IN lists. They're generally a bad idea as they quickly saturate your execution plan cache, which will slow down your entire system. Better put the IDs in a temporary table first, or if you can, don't pass around IDs, but re-use the original query that produced the IDs in the first place as a subquery. In your particular case, why do you need to pass the entire range? If it has no gaps, you can use a BETWEEN predicate instead. 3) If there's no way around the IN list, you can explicitly inline all bind variables either on a case-by-case basis by using DSL.inline(), or by setting Setting.statementType = StatementType.STATIC_STATEMENT. More info about the latter here: https://www.jooq.org/doc/latest/manual/sql-building/bind-values/inlined-parameters Hope this helps, Lukas 2017-03-08 18:07 GMT+01:00 gattinger1969 via jOOQ User Group < [email protected]>: > Hi, jOOQ experts! > > I'm currently evaluating jOOQ and came across the following problem I've > already resolved in a large Hibernate project. > > Suppose you have the following query: > > DSL.using(...).selectFrom(TABLE).where(TABLE.COLUMN.in(getRange(1, > 2002))).fetch(); > getRange(from, to) is a helper method that creates a sequence of integers > within the given range. > > If I execute this query against an SQL Server database using the jTDS JDBC > Driver I will get an exception that looks like this (I simplified the > generated SQL for the sake of brevity): > Exception in thread "main" org.jooq.exception.DataAccessException: SQL > [select ... from [database].[dbo].[table] where > ([database].[dbo].[table].[id] in (?, ?, ?, ...) or > [database].[dbo].[table].[id] in (?, ?, ?, ...) or > [database].[dbo].[table].[id] in (?, ?)) -- SQL rendered with a free trial > version of jOOQ 3.9.1]; Prepared or callable statement has more than 2000 > parameter markers. > at org.jooq_3.9.1.SQLSERVER2014.debug(Unknown Source) > at org.jooq.impl.Tools.translate(Tools.java:1983) > at > org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:676) > at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:363) > at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:315) > at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2708) > ... > Caused by: java.sql.SQLException: Prepared or callable statement has more > than 2000 parameter markers. > at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:1254) > ... > > I faced the same problem in Hibernate and solved it by rewriting the Criteria > prior execution, so that this technical detail must not be considered by any > developer who creates the query. > In the end the query rewriting algorithm scans the query for in() clauses and > replaces the large number of parameters by a subquery (something like select > id from temp where correlationId = ...). The subquery uses a so called > correlation id for grouping the parameters of the > in() clauses. This peace of code is very sophisticated and it requires an > intimate understanding of the Criteria-API and uses reflection. It takes me > quite a while to make this stable and to ensure that any kind query (even > with nested sub queries) can be transformed correctly. > > So I wonder if jOOQ has a built-in solution for this database specific > problem. > Or am I forced to reinvent the wheel, hence migrate the algorithm to the API > of jOOQ? > > Kind regards, > Marcus > > -- > 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. > -- 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.
