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