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 19:37:48 GMT
On 3/5/08, Mike Kienenberger <mkienenb@gmail.com> wrote:
> The code you posted doesn't seem like it'd compile:
>  setFetchinDataRows instead of setFetchin[g]DataRows

Yeah, there are typos in that code, I was in the middle of doing work,
but you get the gist of it.

>
>  I don't understand why you need multiple queries either.   You can
>  write whatever sql you like, so start by figuring out what you want to
>  do in raw sql.

Because, like i said, i'm doing the multiple subqueries on my own.
Note the original post of this thread that has the original SQL
statement I'm trying to work with.  But the SQL supported by cayenne
doesn't support EXISTS subqueries.  I'm trying to find a workaround
for this problem.  I understand your example and have done that very
thing in the past.  This isn't the problem.  The problem is the EXISTS
subqueries.  More specifically NOT EXISTS subqueries.

>
>  Once you've got that, parameterize the parts you want to be variable.
>
>  Then replace the root "select *" with "select #result(),...#result()"
>  so that each of the fields returned in the query match up to the
>  DBAttributes expected by T1.   If you're not fetching data rows, the
>  T1-classed DataObjects should be created automatically.
>
>
>  Here's an example (sanitized by hand) where I've done something similar:
>
>     protected /* com.xyz.entity.Role */ List
>  getAncestorsListOracle(com.xyz.entity.Role role)
>     {
>         String sql = "select "
>             + " #result('g.ID' 'long' 'ID'),"
>             + " #result('g.SHORT_NAME' 'String' 'SHORT_NAME'),"
>             + " #result('g.SYSTEM_ID' 'long' 'SYSTEM_ID')"
>             + " from ROLE r, GROUP g, ROLE_NETWORK n"
>             + " where g.id = r.id"
>             + " and n.parent_role_id = r.id"
>             + " and r.is_active = " + dbVal(true);
>         sql = sql + " start with n.child_role_id #bindEqual($id)";
>         sql = sql + " connect by prior n.parent_role_id = n.child_role_id ";
>
>         // set parameters and run it...
>         Map parameters = new HashMap();
>         parameters.put("id", role.getPrimaryKey());
>
>         SQLTemplate rawSelect = new
>  SQLTemplate(com.xyz.entity.Group.class, sql);
>         rawSelect.setParameters(parameters);
>
>         return threadDataContext().performQuery(rawSelect);
>     }
>
>     protected /* com.xyz.entity.Role */ List getAncestorsList(Role role)
>     {
>         if (isOracleDriver("DataMapNameHere"))
>         {
>             return getAncestorsListOracle(role);
>         }
>         else
>         {
>             return getAncestorsListGeneric(role);
>
>         }
>     }
>
>
>  On 3/5/08, Eric Polino <eric@campgroundautomation.com> wrote:
>  > On 3/5/08, Eric Polino <eric@campgroundautomation.com> wrote:
>  >  > 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
>  >
>  >
>  > A bit of info on that code.  Since I haven't been able to do
>  >  subqueries on the DB side, I've been working on setting up a batch
>  >  query to do the sub-querying manually.  I HATE this idea, but I can't
>  >  find a way to get Cayenne to do it for me, so I've been forced to do
>  >  it like this until I can find a _nice_ solution.  This solution isn't
>  >  complete yet, this is where I'm at now.
>  >
>  >
>  >  Eric
>  >
>  >
>  >  >
>  >  >  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
>  >  >
>  >
>  >
>  >
>  > --
>  >
>  > Eric Polino
>  >  Campground Automated Systems
>  >
>


-- 
Eric Polino
Campground Automated Systems

Mime
View raw message