On 3/5/08, Eric Polino <eri..ampgroundautomation.com> wrote:
> On 3/3/08, Andrus Adamchik <andru..bjectstyle.org> wrote:
> > Two options:
> >
> > 1. SQLTemplate
> > 2. Cayenne 3.0/EJBQL (with EJBQL is still sort of alpha, so for
> > subqueries I'd suggest grabbing a nightly building following the link
> > on the download page: http://cayenne.apache.org/download.html
>
>
> OK. I've been hacking at this for quite a while now and I can't get
> something to work _nicely_ that would do the work of the following
> query.
>
> select * from T1 where not exists (select * from T2 where T2.a = T1.a)
>
> I've heard people say try this and try that, but I'm not getting it.
> It seems that the solution lies somewhere around SQLTemplate, but I
> haven't gotten that to work. Can anyone share some light here?
> Surely there must be a nice solution to this. I'm trying to get a
> list of T1 objects in the end.
>
> Here's a link to my current code. If you spot anything that I could
> be doing better, I'd also appreciate any tips. I often struggle
> finding good docs for how to do certain things in Cayenne and my Java
> is rusty, been living in C for a while now.
>
> http://pastebin.ca/929355
A bit of info on that code. Since I haven't been able to do
subqueries on the DB side, I've been working on setting up a batch
query to do the sub-querying manually. I HATE this idea, but I can't
find a way to get Cayenne to do it for me, so I've been forced to do
it like this until I can find a _nice_ solution. This solution isn't
complete yet, this is where I'm at now.
Eric
>
> TIA,
>
> Eric
>
>
> >
> > Andrus
> >
> >
> > On Mar 3, 2008, at 11:19 PM, Eric Polino wrote:
> >
> > > Alright, well that clears up some stuff, now I'm getting issues with
> > > running an exists statement? I see from the grammar that it doesn't
> > > support them. Anyone know how to run an exists like statement? ...of
> > > course, without looping through each element of hte super query and
> > > testing them individually.
> > >
> > > On 3/3/08, Michael Gentry <blacknex..mail.com> wrote:
> > >> I could be mistaken here, but I don't believe that
> > >> Expression.fromString() supports "select...". I know I've never used
> > >> it that way. I've always started with the WHERE clause as shown on
> > >> this page:
> > >>
> > >> http://cayenne.apache.org/doc20/building-expressions.html
> > >>
> > >> The BNF likewise does not list SELECT, either:
> > >>
> > >> http://cayenne.apache.org/doc20/bnf-for-expressionparser.html
> > >>
> > >> /dev/mrg
> > >>
> > >>
> > >>
> > >> On Mon, Mar 3, 2008 at 3:30 PM, Eric Polino
> > >> <eri..ampgroundautomation.com> wrote:
> > >>> I'm trying to query my db with the following setup and it keep
> > >>> getting
> > >>> Expression parsing errors
> > >>>
> > >>> Expression exp = Expression.fromString(
> > >>> "select * from SA.Site s" +
> > >>> "where not exists ( "+
> > >>> "select * from SA.Reservation r " +
> > >>> "where " +
> > >>> "r.checkindate < $out and " +
> > >>> "r.checkoutdate > $in and " +
> > >>> "r.reservationType = 'S' and r.site = s.siteid" +
> > >>> ") and not exists ( " +
> > >>> "select * from SA.Stay st, SA.Reservation r " +
> > >>> "where " +
> > >>> "r.checkindate < $out and " +
> > >>> "r.checkoutdate > $in and " +
> > >>> "st.site = s.siteid" +
> > >>> ")");
> > >>> Map params = new HashMap();
> > >>>
> > >>> FieldPosition fp = new FieldPosition(DateFormat.DATE_FIELD);
> > >>> SimpleDateFormat sdk = new SimpleDateFormat("yyyy-MM-dd");
> > >>>
> > >>> params.put("in", sdk.format(res.getCheckInDate(), new
> > >>> StringBuffer(""), fp).toString());
> > >>> params.put("out",sdk.format(res.getCheckOutDate(), new
> > >>> StringBuffer(""), fp).toString());
> > >>> SelectQuery sq = new SelectQuery(Site.class,
> > >>> exp.expWithParameters(params));
> > >>>
> > >>> site = (Site)context.performQuery(sq).get(0);
> > >>>
> > >>> Execution never reaches the creation of the HashMap as
> > >>> Expression.fromString() always throws an exception. The following
> > >>> is
> > >>> the start of the logging. It _ends_ with the exception being
> > >>> thrown.
> > >>> There is a long stack trace that follows.
> > >>>
> > >>> INFO QueryLogger: --- will run 1 query.
> > >>> INFO QueryLogger: Opening connection: jdbc:derby://localhost:1527/
> > >>> SunriseDB
> > >>> Login: sa
> > >>> Password: *******
> > >>> INFO QueryLogger: +++ Connecting: SUCCESS.
> > >>> INFO QueryLogger: --- transaction started.
> > >>> INFO QueryLogger: Detected and installed adapter:
> > >>> org.apache.cayenne.dba.derby.DerbyAdapter
> > >>> INFO QueryLogger: SELECT t0.cancelled, t0.checkInDate,
> > >>> t0.checkOutDate, t0.confirmationCode, t0.reservationType,
> > >>> t0.siteType,
> > >>> t0.reservationId, t0.customer, t0.site FROM SA.Reservation t0 WHERE
> > >>> t0.confirmationCode = ? [bind: '32347'] - prepared in 49 ms.
> > >>> INFO QueryLogger: === returned 1 row. - took 1924 ms.
> > >>> INFO QueryLogger: +++ transaction committed.
> > >>> Mar 3, 2008 3:17:36 PM
> > >>> com.sun.xml.ws.server.sei.EndpointMethodHandler invoke
> > >>> SEVERE: [v.2.0.4 October 12 2007] Encountered "Site" at line 1,
> > >>> column 15.
> > >>> Was expecting one of:
> > >>> <EOF>
> > >>> "or" ...
> > >>> "and" ...
> > >>> "not" ...
> > >>> "!" ...
> > >>>
> > >>> I tried using a SQLTemplate to do the same thing, but I had issues
> > >>> with it figuring out what object to create (Site). Anyone know why
> > >>> I'm having this problem? I've been hunting for a few hours and
> > >>> don't
> > >>> know where to look now.
> > >>>
> > >>> Thanks,
> > >>> Eric
> > >>>
> > >>> --
> > >>> Eric Polino
> > >>> Campground Automated Systems
> > >>>
> > >>
> > >
> > >
> > > --
> > > Eric Polino
> > > Campground Automated Systems
> > >
> >
> >
>
>
>
> --
>
> Eric Polino
> Campground Automated Systems
>
-- Eric Polino Campground Automated Systems
This archive was generated by hypermail 2.0.0 : Wed Mar 05 2008 - 13:38:42 EST