ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Larry Meadors" <larry.mead...@gmail.com>
Subject Re: Working around MSSQL parameter list length limitation
Date Wed, 20 Aug 2008 13:04:06 GMT
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
<Eetu.Huisman@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
>

Mime
View raw message