[ http://issues.apache.org/jira/browse/OJB-72?page=comments#action_12356061 ]
Ilkka Priha commented on OJB-72: -------------------------------- Actually, the current patch doesn't generate correct SQL for all platforms. HSQLDB seems to accept it, but Oracle generates the following when refreshing a mn-collection-descriptor field: Caused by: org.apache.ojb.broker.PersistenceBrokerSQLException: * SQLException during execution of sql-statement: * sql statement was 'SELECT A0.HUONE,A0.RAKENNUS,A0.TILA,A0.TASO,A0.NIMI FROM OMISTAJA_LATU.HUONEET A0,OMISTAJA_LATU.LP_HUONEET M_N WHERE (LAITEPAIKKA = ?) AND HUONE = A0.HUONE' * Exception message is [ORA-00918: column ambiguously defined ] * Vendor error code [918] * SQL state code [42000] As you can see, the where clause doesn't contain aliases. The reason is that the SelectionCriteria.isTranslateAttribute() is false for the generated criteria causing SqlQueryStatement.getColName() called by SqlQueryStatement.appendSelectionCriteria() to ignore the alias. One fix that seems to work for all of our use cases is to switch the check of the new mn-alias before the translate check (see attachment). Another one would obviously be to change the translateAttribute value in appropriate cases. -- Ilkka > 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 > Attachments: SqlQueryStatement.patch > > 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]
