hugh_borst wrote:

>Hello all;
>
>We're having some troubles with a query using FB 1.0.3 and JayBird
>(1.01) with JBoss (3.21). It seems that JBoss generates a query in
>the attempt to load a bunch of records as:
>
>SELECT * FROM EMAIL
>WHERE EMAIL_ID = ?
>OR EMAIL_ID = ?
>OR EMAIL_ID = ? … and so on…
>
>One of our customers has a scenario that produces more than 1400 IDs
>to be retrieved. Firebird &/or Jaybird throw the following exception
>and everything falls apart at this point:
>
>org.firebirdsql.gds.GDSException: Error reading data from the
>connection.
>at org.firebirdsql.jgds.GDS_Impl.isc_dsql_execute2(GDS_Impl.java:858)
>
>We are now trying to get JBoss to construct a query of the form:
>
>SELECT * FROM EMAIL
>WHERE EMAIL_ID IN ( ?, ?, ?, ? … and so on…
>
>However, we feel that this path will end in the same way as the
>former. We're also looking at JBoss to see about getting it to
>request N records in the query in the first place (and make it submit
>multiple queries instead of just one very large one), but we're
>not sure what value N should take.
>
>Does anyone know what the maximum size of the parameter list is for
>FireBird and or JayBird? In other words, what is the maximum number
>of parameters that a query can accept and/or what is the maximum
>number of bytes that can be used to make up the query parameters?
>
>Thanks very much in advance.
>
>Regards,
>
>Hugh J Borst
>
>

Hugh,

I remember something around 256 values are the limit on an "in clause",
but not sure about it...

There is any chance to create a table that holds the ID's (the 1400
values) and make a joint to get the relevant ones ? I think this
solution will be better and without limit.

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br