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
>>>
>
>
