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
<[EMAIL PROTECTED]> 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
>
