Hi Bruce.
For 1)
I solved the problem with storing many-to-many relationship by making sure
all objects in the relationship were visible to the current db transaction.
I use long transactions and hence this was causing a problem with them
persisting. The solution was to make a recursive walker that walked across
the parent object. When it came to non-dependent collections I would make
sure to call db.update on those object(s) directly. After this I would then
call db.commit() and the relationships would be saved.
For 2)
I have two classes that share a many-to-many relationship.
The first is called Product and the second is called Category where a
category is a composite PK made up of a category id and a CategoryType. A
product can belong to 0,1 or more Categories. Likewise a Category can have
0,1 or more products. To do this I needed to use the following tables:
Product
Category
ProductCategoryMap (Linkage between Product and Category)
The end-result is that I wish to issue the following query which normally
works perfectly fine for 1-n relationships and is:
SELECT product from Product product where product.categories.id = $1 and
product.categories.type.id = $2
Unfortunately, It doesn't seem to like the generated SQL and throws the
following exception:
[Tue Jun 04 16:43:18 EDT 2002] dtd:DataMgrQueryTest:main-E DataMgrQueryTest:
Error during query execution. Exception: Error in DataMgr.search() while
attempting to retrieve objects using OQL: select product from Product
product where product.categories.id = $1 and
product.categories.type.idAsInteger = $2 Parameters: 11111 111112
java.lang.ArrayIndexOutOfBoundsException
at
org.exolab.castor.jdo.drivers.SQLServerQueryExpression.getStatement(SQLServe
rQueryExpression.java:130)
at
org.exolab.castor.jdo.engine.SQLEngine.createQuery(SQLEngine.java:393)
at
org.exolab.castor.jdo.engine.OQLQueryImpl.execute(OQLQueryImpl.java:463)
at
org.exolab.castor.jdo.engine.OQLQueryImpl.execute(OQLQueryImpl.java:425)
at
org.exolab.castor.jdo.engine.OQLQueryImpl.execute(OQLQueryImpl.java:417)
If I try and use the following OQL which I believe should also work:
SELECT product from Product product where product.categories = $1
I get a SQLException:
java.sql.SQLException: [EXIT41ASP]Invalid column name 'categoryId'.
at com.inet.tds.TdsConnection.a(TdsConnection.java:1066)
at com.inet.tds.TdsStatement.a(TdsStatement.java:261)
at com.inet.tds.TdsStatement._mthif(TdsStatement.java:409)
at com.inet.tds.TdsStatement.executeQuery(TdsStatement.java:391)
The SQL is generates does not use the Mapping table and assumes that
categoryId is in the parent table which is incorrect since its really should
be referenced in the many-table.
I've also seen similar postings to this. So far nobody has mentioned that
they've gotten it to work. Here are some examples:
http://www.mail-archive.com/[email protected]/msg00922.html
http://www.mail-archive.com/[email protected]/msg00626.html
-- Don
-----Original Message-----
From: Bruce Snyder [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 04, 2002 2:46 PM
To: [EMAIL PROTECTED]
Subject: Re: [castor-dev] Any luck with many-to-many queries?
This one time, at band camp, Don DeLuca said:
DD>I have the many-to-many relationships working correctly for my objects,
but
DD>I can't generate the correct OQL to use them in a WHERE clause.
Don,
I have two questions for you:
1) What was the problem with getting the many-to-many relations
working? I remember you posted a while back stating that you were
having trouble, but there was never a solutions posted.
2) What kind of query are you trying to construct? Please explain
further, even if it is pseudo code.
Bruce
--
perl -e 'print
unpack("u30","<0G)U8V4\@4VYY9&5R\"F9E<G)E=\$\!F<FEI+F-O;0\`\`");'
-----------------------------------------------------------
If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
unsubscribe castor-dev
-----------------------------------------------------------
If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
unsubscribe castor-dev
