Thanks a lot :) -----Original Message----- From: Jakob Braeuchi [mailto:[EMAIL PROTECTED] Sent: quinta-feira, 3 de Abril de 2003 20:23 To: OJB Users List Subject: Re: Invalid argument value: Not unique table/alias: 'A1'
hi Jo�o, thanks for your testcase. i commited a patch to SqlQueryStatement. the sql produced is now as follows: SELECT A0.idInternal,A0.name FROM A A0 INNER JOIN AB A1 ON A0.idInternal=A1.keyA INNER JOIN B A2 ON A1.keyB=A2.idInternal INNER JOIN C A3 ON A2.idInternal=A3.keyB INNER JOIN D A4 ON A2.idInternal=A4.keyB WHERE (A3.idInternal = '101' ) AND A4.idInternal = '202' thanks jakob Jo�o Luz wrote: >I'm using rc1 version. > >-----Original Message----- >From: Jakob Braeuchi [mailto:[EMAIL PROTECTED] >Sent: quarta-feira, 2 de Abril de 2003 18:06 >To: OJB Users List >Subject: Re: Invalid argument value: Not unique table/alias: 'A1' > > >hi Jo�o, > >what version of ojb do you use ? > >jakob > >Jo�o Luz wrote: > > > >>Hi, >> >>Look to this simple case: >> A --- AB --- B ---- C >> | >> D >>1- A got a M:N relation with B >>2 - B got a 1:M to C and D >> >>The M:N relationship is implemented with collections (indirection-table >> >> >AB). > > >>If I want to get "All A's that have B's associated that have C's and D's >>with some property equals to something", i.e, if I do this OQL query: >> select all from A.class.getName() where bs.cs.idInternal = $1 and >>bs.ds.idInternal = $2 >> >>The execution result of this query is this: >> SELECT A0.name,A0.idInternal >> FROM A A0 INNER JOIN AB A1 ON A0.idInternal=A1.keyA >> INNER JOIN B A2 ON A1.keyB=A2.idInternal >> INNER JOIN AB A1 ON A0.idInternal=A1.keyA >> INNER JOIN B A2 ON A1.keyB=A2.idInternal >> WHERE ( A2.idInternal = ? ) AND (A2.idInternal = ? ) >> >>Well this don't works because A1 alias is duplicated. The error tells me >>that: >>ERROR [main] (?:?) - SQLException during the execution of the query (for a >>A): Invalid argument value: Not unique table/alias: 'A1' >>java.sql.SQLException: Invalid argument value: Not unique table/alias: 'A1' >> at com.mysql.jdbc.MysqlIO.sendCommand(Unknown Source) >> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(Unknown Source) >> at com.mysql.jdbc.Connection.execSQL(Unknown Source) >> at com.mysql.jdbc.PreparedStatement.executeQuery(Unknown Source) >> at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(Unknown >>Source) >> at org.apache.ojb.broker.accesslayer.RsIterator.<init>(Unknown Source) >> at >>org.apache.ojb.broker.singlevm.RsIteratorFactoryImpl.createRsIterator(Unkn o >> >> >w > > >>n Source) >> at >>org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getRsIteratorFromQuer y >> >> >( > > >>Unknown Source) >> at >>org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getIteratorFromQuery( U >> >> >n > > >>known Source) >> at >>org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery( U >> >> >n > > >>known Source) >> at >>org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery( U >> >> >n > > >>known Source) >> at >>org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery( U >> >> >n > > >>known Source) >> at >>org.apache.ojb.broker.singlevm.DelegatingPersistenceBroker.getCollectionBy Q >> >> >u > > >>ery(Unknown Source) >> at org.apache.ojb.odmg.oql.OQLQueryImpl.execute(Unknown Source) >> at Main.main(Main.java:54) >> >>Well, my question is: Am I doing something wrong or sqlGenerator got a >>bug/feature? :) >> >>Thanks in advance >> Jo�o >> >>PS: Mapping is below: >> >><class-descriptor class="A" table="A"> >> <field-descriptor >> name="idInternal" >> column="idInternal" >> jdbc-type="INTEGER" >> primarykey="true" >> autoincrement="true" >> id="1" >> /> >> <field-descriptor >> name="name" >> column="name" >> jdbc-type="VARCHAR"/> >> <collection-descriptor name="bs" element-class-ref="B" >>indirection-table="AB"> >> <fk-pointing-to-this-class column="keyA" /> >> <fk-pointing-to-element-class column="keyB" /> >> </collection-descriptor> >></class-descriptor> >><class-descriptor class="B" table="B"> >> <field-descriptor name="idInternal" column="idInternal" >>jdbc-type="INTEGER" primarykey="true" autoincrement="true"/> >> >> >> <field-descriptor name="name" column="name" jdbc-type="VARCHAR"/> >> >> <collection-descriptor name="cs" element-class-ref="C" proxy="true"> >> <inverse-foreignkey field-ref="idB"/> >> </collection-descriptor> >> <collection-descriptor name="ds" element-class-ref="D" proxy="true"> >> <inverse-foreignkey field-ref="idB"/> >> </collection-descriptor> >> <collection-descriptor name="as" element-class-ref="A" >>indirection-table="AB"> >> <fk-pointing-to-this-class column="keyB" /> >> <fk-pointing-to-element-class column="keyA" /> >> </collection-descriptor> >></class-descriptor> >> >><class-descriptor class="C" >> table="C"> >> <field-descriptor >> name="idInternal" >> column="idInternal" >> jdbc-type="INTEGER" >> primarykey="true" >> autoincrement="true" >> /> >> >> <field-descriptor name="idB" column="keyB" jdbc-type="INTEGER"/> >> >> <reference-descriptor name="b" class-ref="B"> >> <foreignkey field-ref="keyB"/> >> </reference-descriptor> >> >></class-descriptor> >> >><class-descriptor class="D" table="D"> >> <field-descriptor >> name="idInternal" >> column="idInternal" >> jdbc-type="INTEGER" >> primarykey="true" >> autoincrement="true" >> /> >> >> <field-descriptor name="idB" column="keyB" jdbc-type="INTEGER"/> >> <reference-descriptor name="b" class-ref="B"> >> <foreignkey field-ref="idB"/> >> </reference-descriptor> >></class-descriptor> >> >>PS2:Now the database sql: >>/* >>Mascon Dump >>Source Host: localhost >>Source Server Version: 3.23.53-max-nt >>Source Database: testDB >>Date: 2003-04-01 11:35:58 >>*/ >> >>use testDB ; >>#---------------------------- >># Table structure for A >>#---------------------------- >>drop table if exists A; >>create table A ( >> idInternal int(11) not null default '0', >> name varchar(50) not null, >> primary key (idInternal)) >> type=MyISAM; >> >>#---------------------------- >># Table structure for AB >>#---------------------------- >>drop table if exists AB; >>create table AB ( >> keyA int(11) not null default '0', >> keyB int(11) not null default '0') >> type=MyISAM; >> >>#---------------------------- >># Table structure for B >>#---------------------------- >>drop table if exists B; >>create table B ( >> idInternal int(11) not null default '0', >> keyA int(11) not null default '0', >> name varchar(50) not null, >> primary key (idInternal)) >> type=MyISAM; >> >>#---------------------------- >># Table structure for C >>#---------------------------- >>drop table if exists C; >>create table C ( >> idInternal int(11) not null default '0', >> keyB int(11) not null default '0', >> primary key (idInternal)) >> type=MyISAM; >> >>#---------------------------- >># Table structure for D >>#---------------------------- >>drop table if exists D; >>create table D ( >> idInternal int(11) not null default '0', >> keyB int(11) not null default '0', >> primary key (idInternal)) >> type=MyISAM; >> >> >> >>--------------------------------------------------------------------- >>To unsubscribe, e-mail: [EMAIL PROTECTED] >>For additional commands, e-mail: [EMAIL PROTECTED] >> >> >> >> >> >> > > >--------------------------------------------------------------------- >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] > > >--------------------------------------------------------------------- >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] > > > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
