Return-Path: 
 <ojb-dev-return-12891-apmail-db-ojb-dev-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-ojb-dev-archive@www.apache.org
Received: (qmail 29076 invoked from network); 26 Oct 2005 19:57:31 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199)
  by minotaur.apache.org with SMTP; 26 Oct 2005 19:57:31 -0000
Received: (qmail 22072 invoked by uid 500); 26 Oct 2005 19:57:31 -0000
Delivered-To: apmail-db-ojb-dev-archive@db.apache.org
Received: (qmail 21910 invoked by uid 500); 26 Oct 2005 19:57:30 -0000
Mailing-List: contact ojb-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Unsubscribe: <mailto:ojb-dev-unsubscribe@db.apache.org>
List-Help: <mailto:ojb-dev-help@db.apache.org>
List-Post: <mailto:ojb-dev@db.apache.org>
List-Id: "OJB Developers List" <ojb-dev.db.apache.org>
Reply-To: "OJB Developers List" <ojb-dev@db.apache.org>
Delivered-To: mailing list ojb-dev@db.apache.org
Received: (qmail 81830 invoked by uid 99); 26 Oct 2005 19:23:17 -0000
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=UPPERCASE_25_50
X-Spam-Check-By: apache.org
Message-ID: <1159951953.1130354576099.JavaMail.jira@ajax.apache.org>
Date: Wed, 26 Oct 2005 21:22:56 +0200 (CEST)
From: "Jakob Braeuchi (JIRA)" <jira@apache.org>
To: ojb-dev@db.apache.org
Subject: [jira] Commented: (OJB-72) SQL for a collection query should apply an
 alias for the indirection table with a schema
In-Reply-To: <2061327764.1126770775259.JavaMail.jira@ajax.apache.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-Virus-Checked: Checked by ClamAV on apache.org
X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N

    [ http://issues.apache.org/jira/browse/OJB-72?page=comments#action_12356005 ] 

Jakob Braeuchi commented on OJB-72:
-----------------------------------

previous sql didn't include M_N alias on all columns.

SELECT A0.OBJ_ID,A0.OBJ_ID2,A0.NAME FROM M2N_TEST_ACTOR A0,M2N_TEST_ROLE M_N WHERE ( (A0.OBJ_ID = M_N.ACTOR_ID) AND A0.OBJ_ID2 = M_N.ACTOR_ID2) AND  (((M_N.MOVIE_ID_INT = ?) AND M_N.MOVIE_ID2_INT = ?) AND M_N.MOVIE_ID_STR = ? OR  (((M_N.MOVIE_ID_INT = ?) AND M_N.MOVIE_ID2_INT = ?) AND M_N.MOVIE_ID_STR = ?) OR  (((M_N.MOVIE_ID_INT = ?) AND M_N.MOVIE_ID2_INT = ?) AND M_N.MOVIE_ID_STR = ?) OR  (((M_N.MOVIE_ID_INT = ?) AND M_N.MOVIE_ID2_INT = ?) AND M_N.MOVIE_ID_STR = ?) OR  (((M_N.MOVIE_ID_INT = ?) AND M_N.MOVIE_ID2_INT = ?) AND M_N.MOVIE_ID_STR = ?) OR  
.....

> 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

>
> 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