I've seen Oracle data warehouse queries that push upward of tens of
thousands in a single IN, using a join index.
So I agree - most likely there are limits set by the DBA. Maybe
Cayenne could issue a info notice if you are above some size?
Cris
On 2/7/06, Gentry, Michael (Contractor) <michael_gentr..anniemae.com> wrote:
> I've hit hard limits in Oracle before. I believe they are set by the
> DBA. Of course, that's been a while ago, so the DB world may have
> changed quite a bit. I always operate under the assumption I can't do
> more than about 250 items in an IN clause ...
>
> /dev/mrg
>
>
> -----Original Message-----
> From: Cris Daniluk [mailto:cris.danilu..mail.com]
> Sent: Monday, February 06, 2006 7:50 PM
> To: cayenne-use..bjectstyle.org
> Subject: Re: SQLTemplate / inExp
>
>
> More importantly, we use IN in all sorts of other places, namely
> inExp. That's a great point and a conversation worth having, but I
> don't think its relevant to this specific issue :)
>
> In general, it seems like most DBMS have no hard limit, but definitely
> a practical limit.
>
> On 2/6/06, Andrus Adamchik <andru..bjectstyle.org> wrote:
> > I wouldn't be surprised. But then this is SQLTemplate we are talking
> > about, and Cayenne shouldn't try to second-guess the user. Any better
> > ideas?
> >
> > Andrus
> >
> >
> > On Feb 6, 2006, at 7:26 PM, Joshua Pyle wrote:
> >
> > > Some Databases have size limits for the IN statement
> > >
> > > --
> > > Joshua T. Pyle
> > > Go has always existed.
> > >
> > >
> > > On 2/6/06, Cris Daniluk <cris.danilu..mail.com> wrote:
> > >> I'm experimenting with a patch I created to do just that.
> > >> Hopefully it works :)
> > >>
> > >> On 2/6/06, Andrus Adamchik <andru..bjectstyle.org> wrote:
> > >>> For now Velocity #foreach is your friend.
> > >>>
> > >>> Also I would happily commit a patch to the
> > >>> org.objectstyle.cayenne.access.jdbc.BindDirective that would
> > >>> internally expand a list of objects the the comma-separated
> > >>> syntax :-)
> > >>>
> > >>> So that you could write "WHERE field IN (#bind($list CHAR))"
> > >>>
> > >>> Andrus
> > >>>
> > >>>
> > >>> On Feb 6, 2006, at 3:43 PM, Cris Daniluk wrote:
> > >>>
> > >>>> Apologies if I've overlooked something simple, but for the life
> > >>>> of me
> > >>>> I can't figure out how to bind a list to a query using something
> > >>>> like
> > >>>> #bind().
> > >>>>
> > >>>> I'd basically like to do a simple IN query:
> > >>>>
> > >>>> SELECT * FROM table WHERE field IN ($list)
> > >>>>
> > >>>> It doesn't seem like SQLTemplateProcessor and BindDirective
> > >>>> support it
> > >>>> now, but it seems like it could by simply detecting Collection
> and
> > >>>> making a few simple changes to the query output.
> > >>>>
> > >>>> Is there another way?
> > >>>>
> > >>>> Cris
> > >>>>
> > >>>
> > >>>
> > >>
> > >
> >
> >
>
This archive was generated by hypermail 2.0.0 : Tue Feb 07 2006 - 10:53:07 EST