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 >
