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
