Hi Ilkka,
Ilkka Priha wrote:
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.
Not sure about this, but a similar bug report seems to be OJB-42 (maybe
I'm wrong). The "bad" sql looks like
SELECT A0.czz_id,A0.czz_glAtid,A0.czz_dokId FROM CzescZlozona
A0,zp_czznd INNER JOIN ElementProjektowy A1 ON A0.czz_id=A1.ep_id WHERE
(zp_czznd.czz_zp_zpIdnd IN (?,...?)) AND A0.czz_id = zp_czznd.czz_zp_czpIdnd
Table "zp_czznd" is the indirection table of a m:n relation and was used
without alias.
But even if OJB-42 is the same issue, please make a new bug report with
a more meaningful summary in jira (If you think OJB-42 refer the same
problem please add a note).
regards,
Armin
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]