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.
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
This archive was generated by hypermail 2.0.0 : Wed Mar 05 2008 - 13:34:56 EST