Hi Marcus, Thanks for digging this out. We'll work around this jTDS limitation in jOOQ then.
I thought so, the generated range couldn't possibly be part of the real query :) So, with 2000+ IDs, I do suggest you spill those to a temporary table and semi-join that one instead of the IN-list. In the short run, explicitly using StatementType.STATIC_STATEMENT or DSL.inline() will do the trick. In fact, I've just thought of another workaround which will be available in jOOQ 3.10 (documenting here for completeness' sake). We've implemented support for SQL Server Table Valued Parameters. They could be unnested back in a table again, to allow for this pattern: SELECT * FROM table WHERE id IN (SELECT * FROM unnest_tvp(?)) This would then work with a single bind variable. However, this also only works with the official driver, not with jTDS. The TVP feature request is here: https://github.com/jOOQ/jOOQ/issues/5171 While I'm at it: I'd like to better understand the use-case of using jTDS (to be sure we get things right in integration tests). What's the reason why you're not using the official driver? Cheers Lukas 2017-03-08 20:54 GMT+01:00 Marcus Gattinger <[email protected]>: > Hi Lukas, > > thank you very much for the quick and very informational response. > > Actually this is just a sample. In the real application, this list of ids > is the result of multiple queries that select them iteratively. After all > ids have been identified, the corresponding objects are selected. > The limitation of 2000 parameters is really a limitation of jTDS, not of > SQL Server (see if-block in method SQLParser.parse from line 1234 to > 1259). > > Here is the respective code snippet: > > // > // Impose a reasonable maximum limit on the number of parameters > // unless the connection is sending statements unprepared (i.e. by > // building a plain query) and this is not a procedure call. > // > if (params != null && params.size() > 255 > && connection.getPrepareSql() != TdsCore.UNPREPARED > && procName != null) { > int limit = 255; // SQL 6.5 and Sybase < 12.50 > if (connection.getServerType() == Driver.SYBASE) { > if (connection.getDatabaseMajorVersion() > 12 || > connection.getDatabaseMajorVersion() == 12 && > connection.getDatabaseMinorVersion() >= 50) { > limit = 2000; // Actually 2048 but allow some head room > } > } else { > if (connection.getDatabaseMajorVersion() == 7) { > limit = 1000; // Actually 1024 > } else > if (connection.getDatabaseMajorVersion() > 7) { > limit = 2000; // Actually 2100 > } > > } > if (params.size() > limit) { > throw new SQLException( > Messages.get("error.parsesql.toomanyparams", > Integer.toString(limit)), > "22025"); > } > } > > HTH, 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.
