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.
