--- In firebird-support@yahoogroups.com, Alexandre Benson Smith
<iblist@t...> wrote:

> 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...

In fact, 2000. But such a large IN usually says about not well
designed structure of data model where required result set can be
obtained by layind simple conditions on attributes of the table(s). In
this cases I usually recommend to think once more - why user selected
namely this records and give him opportunity not to choice them
one-by-one but describe conditions of his choice.
One trick how to overcome this limit if re-disegn id still
impossible: build from values string (ParamString) like
'~Value1~Value2~...~ValueN~' and select as

Select Columns
From Table T
Where :ParamString Containing '~'||T.ID||~

I use in such a cases delimiter '~' simply because this symbol IMO
have minimal chances to be inside IDs if they a string type too :)

Best regards,
Alexander