db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ilkka Priha (JIRA)" <j...@apache.org>
Subject [jira] Commented: (OJB-72) SQL for a collection query should apply an alias for the indirection table with a schema
Date Thu, 27 Oct 2005 10:06:57 GMT
    [ 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: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message