You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Eetu Huisman EFECTE <Ee...@efecte.fi> on 2008/08/20 14:48:10 UTC
Working around MSSQL parameter list length limitation
Hi,
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
http://www.efecte.com
RE: Working around MSSQL parameter list length limitation
Posted by Eetu Huisman EFECTE <Ee...@efecte.fi>.
> -----Original Message-----
> From: Larry Meadors [mailto:larry.meadors@gmail.com]
> Sent: 20. elokuuta 2008 16:04
> To: user-java@ibatis.apache.org
> Subject: Re: Working around MSSQL parameter list length limitation
>
> IMO, ibatis should never, ever handle this sort of thing. :-P
Well, that may be true.
> You can however, use this table function (or a variant -
> listing below).
[...]
The problem with this approach is that we don't have control over the database. I would've wanted to handle the problem in iBatis, because it is the lowest level we can work on. It is quite trivial to do it in the java code which uses iBatis, so we'll just do it there.
--
Eetu Huisman | Software Developer
Efecte Corp. Global Operations
Kumpulantie 3, FI-00520 Helsinki, Finland
Mobile +358 50 910 7958
http://www.efecte.com
Re: Working around MSSQL parameter list length limitation
Posted by Larry Meadors <la...@gmail.com>.
IMO, ibatis should never, ever handle this sort of thing. :-P
You can however, use this table function (or a variant - listing below).
Now, you can change your join to do this:
select *
from order
join orderline on order.orderid = orderline.orderid
join dbo.stringsplit(#someIdValues#) SomeIdValues on order.orderid =
SomeIdValues.scalarId
Then you pass one parameter (someIdValues) that is "1,2,3,4,5", and it
gets split on the server...so now you can pass 2000000 parameters. ;-)
Larry
--------
CREATE FUNCTION [dbo].[StringSplit] (@CommaList varchar(8000) )
RETURNS @SCALARLIST TABLE (ScalarId varchar(8000) collate database_default)
AS BEGIN
DECLARE @firstpos integer, @nextscalarid varchar(8000)
SET @firstpos = 8000
WHILE @firstpos > 0 AND @CommaList <> ','
BEGIN
SELECT @firstpos = CHARINDEX (',', @CommaList)
IF @firstpos = 0
BEGIN
SELECT @nextscalarid = CAST ( @CommaList AS varchar(8000) )
END
ELSE
BEGIN
SELECT @nextscalarid = CAST ( (SUBSTRING (@CommaList, 1, @firstpos
- 1) ) AS varchar(8000) )
END
IF @firstpos < LEN (@CommaList)
SET @CommaList = SUBSTRING (@CommaList, @firstpos + 1, 8000)
ELSE
SET @CommaList = SUBSTRING (@CommaList, @firstpos, 8000)
INSERT INTO @SCALARLIST
VALUES (
LTRIM(@nextscalarid) )
END
RETURN
END
--------
On Wed, Aug 20, 2008 at 6:48 AM, Eetu Huisman EFECTE
<Ee...@efecte.fi> wrote:
> Hi,
>
> 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
> http://www.efecte.com
>