hi rudi,
the problem is the same alias A3 is used for different tables.
i've tried to build a similar tests case using current ojb. i do not have
the same problem but at least i could prevent creation of superfluous
TableAlias.
if you want to dig deeper set a breakpoint in
SqlQueryStatement#getTableAlias
...
indirect = (TableAlias) m_pathToAlias.get(attrPath + "*");
if (indirect == null)
{
indirect = createTableAlias(cod.getIndirectionTable(), attrPath + "*");
}
...
the if == null stuff is new, so please try it with your ojb version (it
solved my problems)
hth
jakob
----- Original Message -----
From: "Rudi Alberda" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Sent: Friday, October 04, 2002 3:05 PM
Subject: Re: Query on collections
> 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]>
>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
