On 3/5/08, Mike Kienenberger <mkienen..mail.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 <eri..ampgroundautomation.com> wrote:
> > On 3/5/08, Eric Polino <eri..ampgroundautomation.com> wrote:
> > > On 3/3/08, Andrus Adamchik <andru..bjectstyle.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 <blacknex..mail.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
> > > > >> <eri..ampgroundautomation.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
This archive was generated by hypermail 2.0.0 : Wed Mar 05 2008 - 14:38:22 EST