I ran it with SQuirrel using the DerbyClient and it works fine.
On 3/3/08, Fredrik Liden <flide..ranslate.com> wrote:
> Hi Eric,
>
> Lot of stuff going on in that query. Did you run a test query to made
> sure it works without Cayenne? Just to make sure it's not Derby having
> some issues with the subqueries.
>
>
> Fredrik Liden
> ENLASO Corp.
>
>
>
> -----Original Message-----
> From: Eric Polino [mailto:eri..ampgroundautomation.com]
> Sent: Monday, March 03, 2008 1:30 PM
> To: use..ayenne.apache.org
> Subject: Expression string
>
> 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
This archive was generated by hypermail 2.0.0 : Mon Mar 03 2008 - 15:56:08 EST