{"id":1691,"date":"2013-01-03T22:58:29","date_gmt":"2013-01-04T06:58:29","guid":{"rendered":"http:\/\/cephas.net\/blog\/?p=1691"},"modified":"2013-01-03T22:58:29","modified_gmt":"2013-01-04T06:58:29","slug":"jtds-sql-server-in-and-2000-or-more-parameters","status":"publish","type":"post","link":"https:\/\/cephas.net\/blog\/2013\/01\/03\/jtds-sql-server-in-and-2000-or-more-parameters\/","title":{"rendered":"jTDS, SQL Server, IN and 2000 or more parameters"},"content":{"rendered":"

It’s been a really really long time since I blogged about an obscure problem at work and it’s the new year and all and hey… why not write a blog post once a year? <\/p>\n

A couple weeks ago we came across a bug where someone had written some SQL that used the IN keyword (harmless) but then at some point the code that used that SQL encountered a situation where the IN clause contained more than a couple thousand parameters, which resulted in an error message from Oracle:<\/p>\n

ORA-01795: maximum number of expressions in a list is 1000<\/p><\/blockquote>\n

Lots of stuff on the interwebs about that issue<\/a>, workaround was relatively easy (query was turned into a couple queries, each with less than a thousand parameters). Weird thing was that then a couple weeks later we ran into an issue that had the same symptoms (minus the Oracle error message) but was being executed against SQL Server. There is some documentation available on MSDN<\/a> that talks about maximum number of parameters per stored procedure and maximum number of parameters for a user defined function (both 2,100 if you care), but I couldn’t \/ can’t find anything official that talks about a 1,000 parameter limit or even a 2,100 parameter limit for queries using an IN clause… guessing that stored procedure \/ user defined function are ~= parameters passed via a prepared statement. Anyway, if you write a bunch of code using C# (which I don’t have access to right now) against SQL Server and use more than 2,100 parameters in a SQL statement, then you’ll most likely get an error message that says something like this:<\/p>\n

Too many parameters were provided in this RPC request<\/p><\/blockquote>\n

which you can read more about here<\/a>. But if you’re using Java and SQL Server, then you’re most likely using the jTDS driver<\/a> and if you use more than 2,000<\/em> parameters as part of a prepared statement while using a version greater than SQL Server 7 or more than 1,000<\/em> parameters as part of a prepared statement and are using SQL Server 7, then you’ll get an error message that looks like this:<\/p>\n

\nCaused by: java.sql.SQLException: Prepared or callable statement has more than 2000 parameter markers.
\nat net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:1139)
\nat net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:156)
\nat net.sourceforge.jtds.jdbc.JtdsPreparedStatement.(JtdsPreparedStatement.java:104)
\nat net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement(ConnectionJDBC2.java:2372)
\nat net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement(ConnectionJDBC2.java:2330)\n<\/p><\/blockquote>\n

Just for kicks I checked out the source code for jTDS to see where this was actually happening, it’s in net.sourceforge.jtds.jdbc.SQLParser (as the stack trace above shows), code looks like this:
\n \/\/ Impose a reasonable maximum limit on the number of parameters
\n \/\/ unless the connection is sending statements unprepared (i.e. by
\n \/\/ building a plain query) and this is not a procedure call.
\n \/\/
\n if (params != null && params.size() > 255 && connection.getPrepareSql() != TdsCore.UNPREPARED && procName != null) {
\n int limit = 255; \/\/ SQL 6.5 and Sybase < 12.50\n if (connection.getServerType() == Driver.SYBASE) {\n if (connection.getDatabaseMajorVersion() > 12 || connection.getDatabaseMajorVersion() == 12 && connection.getDatabaseMinorVersion() >= 50) {
\n limit = 2000; \/\/ Actually 2048 but allow some head room
\n }
\n } else {
\n if (connection.getDatabaseMajorVersion() == 7) {
\n limit = 1000; \/\/ Actually 1024
\n } else
\n if (connection.getDatabaseMajorVersion() > 7) {
\n limit = 2000; \/\/ Actually 2100
\n }
\n }
\n if (params.size() > limit) {
\n throw new SQLException(Messages.get(\"error.parsesql.toomanyparams\", Integer.toString(limit)), \"22025\");
\n }
\n }<\/code>
\nIf you’re super odd and have access to a SQL Server instance and want to actually see this in action:
\nint count = 3000;
\nString connectionstring = \"jdbc:jtds:sqlserver:\/\/yourserver:1435\/dbname;user=username;password=password;\";
\nClass.forName(\"net.sourceforge.jtds.jdbc.Driver\");
\nConnection con = DriverManager.getConnection(connectionstring);
\nList ids = new ArrayList();
\nfor (int i=0; i
\nand grab the setValues and preparePlaceHolders methods from the code pasted in this stackoverflow.com thread<\/a>. Execute that from a test harness and you'll get the error from jTDS as I pasted above. Not a jTDS issue really, just obscure and different per database server, which sucks when you have to support four different versions (Oracle, Postgres, SQL Server, MySQL) like we do at work.<\/p>\n

Bunch of stackoverflow.com threads that talk about how to work around this limitation. Doesn't appear that Postgres has a limit (at least that we've found yet), brief reading I did suggests that MySQL has a limit of 65,000. <\/p>\n","protected":false},"excerpt":{"rendered":"

It’s been a really really long time since I blogged about an obscure problem at work and it’s the new year and all and hey… why not write a blog post once a year? A couple weeks ago we came across a bug where someone had written some SQL that used the IN keyword (harmless) … Continue reading jTDS, SQL Server, IN and 2000 or more parameters<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/posts\/1691"}],"collection":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/comments?post=1691"}],"version-history":[{"count":9,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/posts\/1691\/revisions"}],"predecessor-version":[{"id":1700,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/posts\/1691\/revisions\/1700"}],"wp:attachment":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/media?parent=1691"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/categories?post=1691"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/tags?post=1691"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}