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 >
