[ http://issues.apache.org/jira/browse/OJB-72?page=comments#action_12356002 ]
Jakob Braeuchi commented on OJB-72: ----------------------------------- fixed to use the special alias M_N for the indirection table: SELECT A0.OBJ_ID,A0.OBJ_ID2,A0.NAME FROM M2N_TEST_ACTOR A0,M2N_TEST_ROLE M_N WHERE ( (A0.OBJ_ID = M_N.ACTOR_ID) AND A0.OBJ_ID2 = M_N.ACTOR_ID2) AND (((MOVIE_ID_INT = ?) AND MOVIE_ID2_INT = ?) AND MOVIE_ID_STR = ? ) > SQL for a collection query should apply an alias for the indirection table > with a schema > ---------------------------------------------------------------------------------------- > > Key: OJB-72 > URL: http://issues.apache.org/jira/browse/OJB-72 > Project: OJB > Type: Bug > Components: PB-API > Versions: 1.0.4 > Environment: Java 1.5, OJB 1.0.4-cvs, HSQLDB 1.8.0 > Reporter: Ilkka Priha > Assignee: Jakob Braeuchi > > This problem has been discussed earlier (Edson Richter/m:n mappings), but it > seems to be still there. The problem is that OJB generates a query that > doesn't work in all DB platforms for collections applying an indirection > table with a schema specification. > The collection-descriptor contains a schema as part of the indirection table > name as it has no separate schema attribute. > <class-descriptor > class="fi.simsoft.ttke.rt.olx.Table" > table="TAULUKKO" > schema="TTKE"> > <collection-descriptor > name="Systems" > element-class-ref="fi.simsoft.ttke.rt.olx.System" > auto-retrieve="false" > auto-update="none" > auto-delete="none" > indirection-table="TTKE.TAULUKKO_JARJESTELMA"> > <fk-pointing-to-this-class column="TAULUKKO_ID" /> > <fk-pointing-to-element-class column="JARJESTELMA_ID" /> > </collection-descriptor> > The element table has the schema specified as an attribute. > <class-descriptor > class="fi.simsoft.ttke.rt.olx.System" > table="JARJESTELMA" > schema="TTKE"> > <attribute > attribute-name="visible" > attribute-value="false" /> > <field-descriptor > name="Id" > column="ID" > jdbc-type="BIGINT" > primarykey="true" > autoincrement="true"> > </field-descriptor> > The generated SQL applies an alias for the element table, but uses the full > name of the indirection table. > SELECT A0.ID,A0.TUNNUS,A0.KUVAUS,A0.LAITOS > FROM TTKE.JARJESTELMA A0,TTKE.TAULUKKO_JARJESTELMA > WHERE (TTKE.TAULUKKO_JARJESTELMA.TAULUKKO_ID = ?) > AND TTKE.TAULUKKO_JARJESTELMA.JARJESTELMA_ID = A0.ID > However, all DB platforms don't support column references containing a > schema, below is an exception thrown by HSQDB 1.8.0. > java.sql.SQLException: Three part identifiers prohibited in statement [SELECT > A0.ID,A0.TUNNUS,A0.KUVAUS,A0.LAITOS FROM TTKE.JARJESTELMA > A0,TTKE.TAULUKKO_JARJESTELMA WHERE (TTKE.TAULUKKO_JARJESTELMA.TAULUKKO_ID = > ?) AND TTKE.TAULUKKO_JARJESTELMA.JARJESTELMA_ID = A0.ID] -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
