ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Eetu Huisman EFECTE" <Eetu.Huis...@efecte.fi>
Subject Working around MSSQL parameter list length limitation
Date Wed, 20 Aug 2008 12:48:10 GMT

We've bumped into an issue MSSQL has with parameter list length in an "IN" query. I tried
searching around the mailing list archives, JIRA and wiki, but no-one seems to have had the
same problem before.

When MSSQL is given more than 2000 parameters for a IN, it fails:

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.<init>(JtdsPreparedStatement.java:104)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement(ConnectionJDBC2.java:2020)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement(ConnectionJDBC2.java:1980)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.ibatis.common.jdbc.SimpleDataSource$SimplePooledConnection.invoke(SimpleDataSource.java:958)
at $Proxy15.prepareStatement(Unknown Source) at com.ibatis.sqlmap.engine.execution.SqlExecutor.prepareStatement(SqlExecutor.java:494)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:176)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)

It can be worked around in higher level Java code (by splitting the parameter list into smaller
chunks and then combining the results), but it would of course be better if iBatis had a way
to handle these kind limitations. Has anyone any ideas whether it could be handled with any
kind of configuration, or should I file a ticket about it? (Some versions of Oracle had a
similar limit set at a 1000 parameters.)

Eetu Huisman | Software Developer
Efecte Corp. Global Operations
Kumpulantie 3, FI-00520 Helsinki, Finland
Mobile +358 50 910 7958

View raw message