Details
Major Description
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]