cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Eric Polino" <e...@campgroundautomation.com>
Subject Re: Expression string
Date Wed, 05 Mar 2008 18:34:01 GMT
On 3/3/08, Andrus Adamchik <andrus@objectstyle.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

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 <blacknext@gmail.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
>  >> <eric@campgroundautomation.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

Mime
View raw message