RE: Expression string

[Fredrik Liden]

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:[EMAIL PROTECTED] 
Sent: Monday, March 03, 2008 1:30 PM
To: user@cayenne.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