Hello
This problem has been discussed earlier (Edson Richter/m:n mappings), but it
seems to be still there and I didn't find anything about it in Jira. Maybe I'm
missing something, but 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 the 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]
Is there a workaround or is this a known problem already registered? Any helping
hints appreciated.
-- Ilkka
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
