On 3/3/08, Andrus Adamchik <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 > >> <[EMAIL PROTECTED]> 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
