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) 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:

ORA-01795: maximum number of expressions in a list is 1000

Lots of stuff on the interwebs about that issue, 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 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:

Too many parameters were provided in this RPC request

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

Caused by: java.sql.SQLException: Prepared or callable statement has more than 2000 parameter markers.
at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:1139)
at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:156)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.(JtdsPreparedStatement.java:104)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement(ConnectionJDBC2.java:2372)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement(ConnectionJDBC2.java:2330)

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:
// 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");
}
}
If you’re super odd and have access to a SQL Server instance and want to actually see this in action:
int count = 3000;
String connectionstring = "jdbc:jtds:sqlserver://yourserver:1435/dbname;user=username;password=password;";
Class.forName("net.sourceforge.jtds.jdbc.Driver");
Connection con = DriverManager.getConnection(connectionstring);
List ids = new ArrayList();
for (int i=0; i
and grab the setValues and preparePlaceHolders methods from the code pasted in this stackoverflow.com thread. 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.