Thanks Andrus, that got me going. I had to create six classes to add a single word to a query! ;)
And fortunately that single word is the first one to be output, so in my appendJoinSubtree() function I am simply outputting " INNER" and then call super.appendJoinSubtree() for the rest. Would have been more tedious if I had to insert something in the middle, because then I would have had to copy the whole method plus two private data members and an inner class. Anyway, it is working now, thanks again. Wernke Am 29.06.2011 10:03, schrieb Andrus Adamchik: > There's a tree of related objects involved. Check Oracle8Adapter and related > classes. It actually switches Cayenne back to joins in WHERE clause. The > chain of overridden objects goes like this: > > Oracle8Adapter.getAction(..) > Oracle8ActionBuilder > Oracle8SelectAction > Oracle8JoinStack > > > Oracle8JoinStack is where the magic happens. BTW, you can create a simpler > subclass of JoinStack, just overriding 'appendJoinSubtree' to change " JOIN" > to " INNER JOIN". > > Andrus > > > > On Jun 28, 2011, at 10:52 AM, Wernke zur Borg wrote: > >> Thanks for your answers. I would like to stick to version 3.1, so could >> somebody give me a hint as to where to modify the JOIN syntax in a >> custom DB adapter? I looked at all the existing DB adapters and I guess >> I would subclass JdbcAdapter but I could not find a suitable method to >> override. >> >> Thanks, Wernke >> >> >> Am 21.06.2011 15:41, schrieb Andrus Adamchik: >>> Past versions of Cayenne used joins in WHERE clause, that were likely >>> compatible with MS Access. So older Cayenne 2.0.x should probably work. >>> >>> Also writing a custom Access DbAdapter that adds INNER should be possible. >>> >>> Andrus >>> >>> On Jun 21, 2011, at 11:36 AM, Aristedes Maniatis wrote: >>>> On 21/06/11 6:02 PM, Wernke zur Borg wrote: >>>>> Hi, >>>>> >>>>> I am having a few problems with queries on an MS Access database when I >>>>> use object relationships to access related tables. I am using Cayenne >>>>> 3.1 M2. >>>>> >>>>> My case is very simple. Let's say I have two tables A and B where A has >>>>> a column col containing a primary key of B. Given an object a from table >>>>> A with colValue, I want to access the related record of B using a.getB(). >>>>> >>>>> Cayenne creates a select query that joins A and B like this: >>>>> >>>>> SELECT DISTINCT t0.columns... FROM B t0 *JOIN* A t1 ON (t0.pk = t1.col) >>>>> WHERE t1.pk = ? [bind: 1-> a.pkValue] >>>>> >>>>> The first thing is that this produces a syntax error as the simple *JOIN >>>>> *keyword is not valid in MS Access. They want an explicit *INNER JOIN*. >>>> I've never heard of anyone using MS Access before as an SQL engine, so >>>> this might be one reason why. I would run, not walk, to another db if you >>>> can at all manage that within your environment. >>>> >>>> >>>>> But secondly I wonder why Cayenne needs a JOIN in this case anyway, >>>>> since it is of no use at all. Why does it not use something like: >>>>> >>>>> SELECT t0.columns... FROM B t0 WHERE t0.pk = ? [bind 1-> a.colValue] >>>>> >>>> I guess since any SQL engine will optimise that out, the fact that Cayenne >>>> does it in the most generic way usually doesn't matter. I haven't seen the >>>> code in question specifically, but Cayenne performs JOINs very often and >>>> has a bunch of code which knows how to do that (multiple keys, qualifiers, >>>> inheritance, etc) and that code is just being reused here. >>>> >>>> >>>>> My workaround is to manually create a SELECT query to access B without >>>>> joining it A: >>>>> >>>>> Expression e = ExpressionFactory.matchExp(B.primaryKeyProperty, >>>>> A.colValue); >>>>> SelectQuery q = new SelectQuery(B.class, e); >>>>> >>>>> This works but of course it would be nicer to use the getter function >>>>> A.getB(). >>>>> >>>>> Is there a possibility to either avoid the JOIN or tell Cayenne to use >>>>> INNER JOIN instead of JOIN ? >>>> Yes, you can write specific db adapters for databases of your choice. >>>> You'll need to get your hands into Cayenne itself, but the classes in >>>> question aren't too hard to understand. Let us know if you need more >>>> pointers to do that. I am guessing that none of the development team will >>>> be easily able to test against Access, and we don't currently run any unit >>>> tests against Access. But if you can help make it work, then so much the >>>> better. >>>> >>>> Cheers >>>> Ari >>>> >>>> -- >>>> --------------------------> >>>> Aristedes Maniatis >>>> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A >>>> >>
