Ok, here goes...
We use OJB 0.9.5, MS SQL Server 2000 (and JBoss but that doesn't really
matter)
The exception is created by the following code:
...
crit.addEqualTo("categories.id", categoryId);
Criteria crit2 = new Criteria();
crit2.addEqualTo("categories.mainCategory.id", categoryId);
crit.addOrCriteria(crit2);
Query q = new QueryByCriteria(ContentItem.class, crit, true);
broker.getIteratorByQuery(q);
...
Repository.xml snippet:
...
<class-descriptor
class="nl.casthere.uib.ojb.Category"
table="@[email protected]_category"
>
<extent-class class-ref="nl.casthere.uib.ojb.CourseCategory" />
<extent-class class-ref="nl.casthere.uib.ojb.ShowCategory" />
<extent-class class-ref="nl.casthere.uib.ojb.AccomodationCategory" />
<extent-class class-ref="nl.casthere.uib.ojb.LinkCategory" />
&category;
</class-descriptor>
<!-- Definitions for CourseCategory -->
<class-descriptor
class="nl.casthere.uib.ojb.CourseCategory"
table="@[email protected]_category"
>
<!-- Category properties and relations -->
&category;
<collection-descriptor
name="contentItems"
element-class-ref="nl.casthere.uib.ojb.Course"
auto-update="false"
auto-retrieve="true"
auto-delete="false"
proxy="true"
indirection-table="@[email protected]_content_category"
>
<fk-pointing-to-this-class column="category_id"/>
<fk-pointing-to-element-class column="content_item_id"/>
</collection-descriptor>
</class-descriptor>
...
the reference to &category is another xml file, containing (amongst other
things):
...
<reference-descriptor
name="mainCategory"
class-ref="nl.casthere.uib.ojb.Category"
auto-update="false"
auto-retrieve="true"
auto-delete="false"
proxy="true"
>
<foreignkey field-id-ref="5" />
</reference-descriptor>
...
Jboss server.log fragment:
...
2002-10-04 14:48:02,780 DEBUG
[org.apache.ojb.broker.singlevm.PersistenceBrokerImpl] getIteratorFromQuery
class nl.casthere.uib.ojb.ContentItem, Query from class
nl.casthere.uib.ojb.ContentItem where categories.id = ? OR
categories.mainCategory.id = ?
2002-10-04 14:48:02,780 DEBUG [org.apache.ojb.broker.accesslayer.RsIterator]
RsIterator(Query from class nl.casthere.uib.ojb.ContentItem where
categories.id = ? OR categories.mainCategory.id = ? , table:
uib.uib_location
FieldDescriptions: [Lorg.apache.ojb.broker.metadata.FieldDescriptor;@84ff20)
2002-10-04 14:48:02,780 DEBUG [org.apache.ojb.broker.accesslayer.JdbcAccess]
executeQuery : Query from class nl.casthere.uib.ojb.ContentItem where
categories.id = ? OR categories.mainCategory.id = ?
2002-10-04 14:48:02,780 DEBUG
[org.apache.ojb.broker.accesslayer.SqlGenerator] SQL: SELECT DISTINCT
A0.image_id,A0.originator_id,A0.description,A0.street_number_addition,A0.url
,A0.city,A0.keywords,A0.dummy,A0.opening_hours,A0.last_modified_by,A0.id,A0.
zipcode_number,A0.ticket_sale,A0.ojb_concrete_class,A0.feed_remarks,A0.last_
modified,A0.state,A0.zipcode,A0.created_by,A0.creation_date,A0.name,A0.route
,A0.facility_text,A0.street_name,A0.street_number FROM (uib.uib_location A0
INNER JOIN uib.uib_content_category A3 ON A0.id=A3.content_item_id) INNER
JOIN (uib.uib_content_category A1 INNER JOIN (uib.uib_category A2 INNER JOIN
uib.uib_category A3 ON A2.main_category_id=A3.id) ON A1.category_id=A2.id)
ON A0.id=A1.content_item_id WHERE A2.id = ? OR A3.id = ?
2002-10-04 14:48:02,780 DEBUG
[org.apache.ojb.broker.accesslayer.ConnectionManager] Request new connection
from ConnectionFactory:
com.microsoft.jdbc.sqlserver.SQLServerConnection@2ba88c
2002-10-04 14:48:02,800 ERROR [org.apache.ojb.broker.accesslayer.JdbcAccess]
SQLException during the execution of the query (for a
nl.casthere.uib.ojb.Location): [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]The correlation name 'A3' is specified multiple times in a
FROM clause.
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]The correlation name 'A3' is specified multiple times in a
FROM clause.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.openCursor(Unknown
Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.execute(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown
Source)
at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown
Source)
at org.apache.ojb.broker.accesslayer.JdbcAccess.executeQuery(Unknown
Source)
at org.apache.ojb.broker.accesslayer.RsIterator.<init>(Unknown Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getIteratorFromQuery(Un
known Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Un
known Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Un
known Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Un
known Source)
at
nl.casthere.uib.ejb.IteratorSessionBean.getIterator(IteratorSessionBean.java
:93)
...
----- Original Message -----
From: "Jakob Braeuchi" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Sent: Friday, October 04, 2002 1:47 PM
Subject: Re: Query on collections
> include only some of the top stack entries and only relevant descriptors
> from your repository.
>
> jakob
> ----- Original Message -----
> From: "Rudi Alberda" <[EMAIL PROTECTED]>
> To: "OJB Users List" <[EMAIL PROTECTED]>
> Sent: Friday, October 04, 2002 12:02 PM
> Subject: Re: Query on collections
>
>
> > I'll post the sample later on when I have some more time.
> > Do you want the entire stack trace along with the repository and the
code
> > that generates it? Because it's an awful lot (we've got a big repository
> > with loads of objects)
> >
> > ----- Original Message -----
> > From: "Jakob Braeuchi" <[EMAIL PROTECTED]>
> > To: "OJB Users List" <[EMAIL PROTECTED]>
> > Sent: Friday, October 04, 2002 10:51 AM
> > Subject: Re: Query on collections
> >
> >
> > > hi rudi,
> > >
> > > > Oh my... so that actually works! Wow... I tried it and it
> > > > seemed OJB generates the SQL that we did before 'by hand',
> > > > so that's perfect! Just wish I'd known about this sooner,
> > >
> > > well, we invested a lot of time to keep users happy...
> > >
> > > > 'The correlation name 'A3' is specified multiple times in a FROM
> > clause.'
> > > > which was indeed the case. Is this a bug?
> > >
> > > can you please post the sample and the generated sql ?
> > >
> > > jakob
> > >
> > > ----- Original Message -----
> > > From: "Rudi Alberda" <[EMAIL PROTECTED]>
> > > To: "OJB Users List" <[EMAIL PROTECTED]>
> > > Sent: Friday, October 04, 2002 9:30 AM
> > > Subject: Re: Query on collections
> > >
> > >
> > > > Oh my... so that actually works! Wow... I tried it and it
> > > > seemed OJB generates the SQL that we did before 'by hand',
> > > > so that's perfect! Just wish I'd known about this sooner,
> > > > there is no mentioning of this in any tutorial.
> > > > One more thing; when I tried to add 2 criteria on an m:n
> > > > relation (on a m:n relation and its parent), the generated
> > > > SQL was not accepted by SQL server 2000 and I got an exception
> > > > saying something about
> > > > 'The correlation name 'A3' is specified multiple times in a FROM
> > clause.'
> > > > which was indeed the case. Is this a bug?
> > > >
> > > > ----- Original Message -----
> > > > From: "Jakob Braeuchi" <[EMAIL PROTECTED]>
> > > > To: "OJB Users List" <[EMAIL PROTECTED]>
> > > > Sent: Thursday, October 03, 2002 5:36 PM
> > > > Subject: Re: Query on collections
> > > >
> > > >
> > > > > hi rudi,
> > > > >
> > > > > this snippet is from test case QueryTest:
> > > > >
> > > > > Criteria crit = new Criteria();
> > > > > crit.addEqualTo("projects.title", "HSQLDB"); // direct m:n
> > > > > Query q = QueryFactory.newQuery(Person.class, crit);
> > > > > Collection results = broker.getCollectionByQuery(q);
> > > > >
> > > > > it's looking for persons working on project named "HSQLDB"
> > > > >
> > > > > hth
> > > > > jakob
> > > > >
> > > > > ----- Original Message -----
> > > > > From: "Rudi Alberda" <[EMAIL PROTECTED]>
> > > > > To: "OJB Users List" <[EMAIL PROTECTED]>
> > > > > Sent: Thursday, October 03, 2002 2:44 PM
> > > > > Subject: Re: Query on collections
> > > > >
> > > > >
> > > > > > Hey Chris,
> > > > > >
> > > > > > Thanks for your tip, but we (and indeed I forgot to mention
this)
> > > > already
> > > > > do
> > > > > > the query on m:n
> > > > > > objects like this (except we use 'hard' table names and PK field
> > > names).
> > > > > > What I was wondering
> > > > > > was if there was an easier way to do it than like this :)
> > > > > >
> > > > > > For example something like this would be extremely cool to have
in
> > > OJB:
> > > > > > Criteria c = new Criteria();
> > > > > > c.addIn("bees[]", B);
> > > > > > Query q = new QueryByCriteria(B.class, c, false);
> > > > > > broker.getCollectionByQuery(q);
> > > > > >
> > > > > > or even
> > > > > > Criteria c = new Criteria();
> > > > > > c.addEqualTo("bees[0].name", B.getName());
> > > > > > Query q = new QueryByCriteria(B.class, c, false);
> > > > > > broker.getCollectionByQuery(q);
> > > > > >
> > > > > > Nevertheless, thanks for your input! :)
> > > > > >
> > > > > >
> > > > > > ----- Original Message -----
> > > > > > From: "Chris Lewington" <[EMAIL PROTECTED]>
> > > > > > To: "OJB Users List" <[EMAIL PROTECTED]>
> > > > > > Sent: Thursday, October 03, 2002 2:28 PM
> > > > > > Subject: Re: Query on collections
> > > > > >
> > > > > >
> > > > > > > Hi Rudi,
> > > > > > >
> > > > > > > I had to address a similar problem as part of implementing a
> > "query
> > > by
> > > > > > object"
> > > > > > > mechanism (tricky!) to search on "partially complete" objects
> > rather
> > > > > than
> > > > > > using
> > > > > > > straight queries themselves.. The basic idea I had was as
> follows:
> > > > > > >
> > > > > > > - build an empty A and set your concrete B into its collection
> > (use
> > > > this
> > > > > > as a
> > > > > > > basis for what follows);
> > > > > > >
> > > > > > > - use the CollectionDescriptor of class A to get the FK column
> > names
> > > > of
> > > > > > the
> > > > > > > indirection table to both the item class (B) and the "this"
> class
> > > (A),
> > > > > and
> > > > > > also
> > > > > > > the name of the indirection table itself;
> > > > > > >
> > > > > > > - then, from your concrete B, use its ClassDescriptor to get
the
> > PK
> > > > > field
> > > > > > info
> > > > > > > and hence from that a PersistentField from which you can
obtain
> > the
> > > > > actual
> > > > > > PK
> > > > > > > value of your B object;
> > > > > > >
> > > > > > > - then build a Criteria object with the following:
> > > > > > > 1) an "addEqualToColumn" which matches the PK field for A to
> its
> > > > > > > corresponding column name in the indirection table, plus
> > > > > > > 2) an "addEqualTo" which matches the actual PK value you have
> for
> > B
> > > > > with
> > > > > > its
> > > > > > > corresponding column name in the indirection table;
> > > > > > >
> > > > > > > - then finally create a "QueryByMtoNCriteria" with the class
of
> A,
> > > the
> > > > > > > indirection table name and the newly built Criteria object.
> > > > > > >
> > > > > > > From there you can do a getCollectionByQuery with your newly
> built
> > > > Query
> > > > > > object
> > > > > > > above. I'm sure there are easier ways but that was the easiest
I
> > > could
> > > > > > come up
> > > > > > > with for now and it works OK for me.
> > > > > > >
> > > > > > > Hope that helps,
> > > > > > >
> > > > > > > Cheers,
> > > > > > >
> > > > > > >
> > > > > > > Chris
> > > > > > >
> > > > > > > Rudi Alberda wrote:
> > > > > > >
> > > > > > > > Hi all,
> > > > > > > >
> > > > > > > > I have a question regarding queries on m:n relations.
Suppose
> I
> > > have
> > > > > two
> > > > > > > > classes which are associated in OJB through a m:n relation,
> > > objects
> > > > of
> > > > > > type
> > > > > > > > 'A' and 'B'. 'A' has a property called 'bees' returning all
> > > objects
> > > > of
> > > > > > type
> > > > > > > > 'B', 'B' has a property called 'as' which returns all 'A'
> > objects.
> > > > > > > > This all works nicely in OJB, storing, updating, deleting.
But
> > now
> > > I
> > > > > > want to
> > > > > > > > query. I have an object of type 'B' and I want to know which
> > > objects
> > > > > of
> > > > > > type
> > > > > > > > 'A' are associated to it. In other words, I'd like a query
> which
> > > > > results
> > > > > > in
> > > > > > > > a collection of 'A' objects for which 'B' is in the
collection
> > > > > 'A.bees'.
> > > > > > > > How would this work?
> > > > > > > > I cannot simply go and create a query like:
> > > > > > > >
> > > > > > > > Criteria c = new Criteria();
> > > > > > > > c.addEqualTo("bees", B);
> > > > > > > > Query q = new QueryByCriteria(B.class, c, false);
> > > > > > > > broker.getCollectionByQuery(q);
> > > > > > > >
> > > > > > > > because bees is a collection. Will Criteria.addIn work? And
if
> > > this
> > > > > > doesn't,
> > > > > > > > what will?
> > > > > > > > I have looked at QueryByMtoNCriteria but the documentation
of
> > this
> > > > was
> > > > > > > > rather unclear.
> > > > > > > > Also, I might be able to use Criteria.addExists, but I need
to
> > > refer
> > > > > to
> > > > > > a
> > > > > > > > field from the main query in the subquery, otherwise my
> subQuery
> > > > isn't
> > > > > > of
> > > > > > > > much use.
> > > > > > > > Help!
> > > > > > > >
> > > > > > > > --
> > > > > > > > To unsubscribe, e-mail:
> > > > > > <mailto:[EMAIL PROTECTED]>
> > > > > > > > For additional commands, e-mail:
> > > > > > <mailto:[EMAIL PROTECTED]>
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > To unsubscribe, e-mail:
> > > > > <mailto:[EMAIL PROTECTED]>
> > > > > > > For additional commands, e-mail:
> > > > > <mailto:[EMAIL PROTECTED]>
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > To unsubscribe, e-mail:
> > > > <mailto:[EMAIL PROTECTED]>
> > > > > > For additional commands, e-mail:
> > > > <mailto:[EMAIL PROTECTED]>
> > > > > >
> > > > >
> > > > >
> > > > > --
> > > > > To unsubscribe, e-mail:
> > > <mailto:[EMAIL PROTECTED]>
> > > > > For additional commands, e-mail:
> > > <mailto:[EMAIL PROTECTED]>
> > > > >
> > > >
> > > >
> > > > --
> > > > To unsubscribe, e-mail:
> > <mailto:[EMAIL PROTECTED]>
> > > > For additional commands, e-mail:
> > <mailto:[EMAIL PROTECTED]>
> > > >
> > >
> > >
> > > --
> > > To unsubscribe, e-mail:
> <mailto:[EMAIL PROTECTED]>
> > > For additional commands, e-mail:
> <mailto:[EMAIL PROTECTED]>
> > >
> > >
> >
> >
> > --
> > To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
> > For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
> >
>
>
> --
> To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
