db-ojb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jakob Braeuchi <jbraeu...@gmx.ch>
Subject Re: Oracle exception: column ambiguously defined - need help
Date Mon, 13 Jun 2005 18:10:48 GMT
hi alexandre,

my first suggestion is to use a simpler mapping:
use separate tables for 'fichier' and 'folder' and forget the table 
'element'. this has of course a disadvantage: you'll have to define all 
common attributes at least twice.

please have a look at repository_junit_reference.xml

jakob

thomas0@etu.unige.ch schrieb:
> Dear OJB Users,
> Dear Jakob,
> 
> I really need advice on how to map correctly my java classes
> in OJB.
> 
> I want to model a filesystem. I have an 'Element' class which
> defines the unit of the filesystem. An 'Element' can be a 'File' or
> a 'Folder'; these 2 classes extend the 'Element' class. The 'Folder'
> class contains a collection of 'Folder' and a collection of 'Fichier'.
> 
> My question is: How can I write the class-descriptors (especially the
> collection
> descriptor) to represent the 'Folder' containing 'Folder' objects and 'Fichier'
> object? 
> Is it possible ? 
> 
> I really tried hard but when querying 'Folder' objects, Oracle always raises
> a 'column ambiguously defined' exception.
> 
> I tried to put an alias on my Criteria when building the query but it doesn't
> solve the problem. OJB keep generating a bad query. I think my way of mapping
> is wrong, please tell me how you would do. I don't think the database is the
> problem because the troubles appear when OJB generates this wrong query.
> 
> Thanks again,
> 
> Alexandre
> 
> 
> ----- Original Message ----- 
> From: "Jakob Braeuchi" <jbraeuchi@gmx.ch>
> To: "OJB Users List" <ojb-user@db.apache.org>
> Sent: Sunday, June 12, 2005 5:43 PM
> Subject: Re: Oracle exception: column ambiguously defined - need help
> 
> 
> 
>>hi alexandre ;)
>>
>>well, you could try it with hsqldb that is shipped with ojb.
>>but i think the problem is that both relationships use the same 
>>indirection table and ojb lists it only once in the select. you could 
>>try setting a user-alias for the relationships prevent ojb from using 
>>the same alias twice.
>>http://db.apache.org/ojb/docu/guides/query.html#user+defined+alias
>>
>>
>>hth
>>jakob
>>
>>thomas0@etu.unige.ch schrieb:
>>
>>>Hello OJB Users,
>>>Hello Jakob
>>>
>>>I couldn't try on any other database since I only have access to an Oracle
> 
> one
> 
>>>(and my application must run on a Oracle database).
>>>
>>>Here's the query generated by OJB:
>>>
>>>SELECT A0.ID_FOLDER 
>>>FROM FM.FOLDER A0,INFOLD,FM.element A1,INFOLD 
>>>WHERE A0.ID_FOLDER=A1.ID_ELEMENT 
>>>AND ((INFOLD.FOL_ID_ELEMENT IN (1084, 1068, 1088)) 
>>>AND A0.ID_FOLDER = INFOLD.ID_ELEMENT);
>>>
>>>As you can see, the INFOLD.FOL_ID_ELEMENT and the INFOLD.ID_ELEMENT
>>>are the ambiguous columns since the INFOLD table is not suffixed in the
> 
> query.
> 
>>>This query would work:
>>>
>>>SELECT A0.ID_FOLDER 
>>>FROM FM.FOLDER A0,INFOLD A2,FM.element A1,INFOLD A3
>>>WHERE A0.ID_FOLDER=A1.ID_ELEMENT 
>>>AND ((A2.FOL_ID_ELEMENT IN (1084, 1068, 1088)) 
>>>AND A0.ID_FOLDER = A3.ID_ELEMENT);
>>>
>>>Thanks in advance for any explanations.
>>>
>>>Alexandre Thomas
>>>
>>>(by the way Thomas is my surname :-)
>>>
>>>----- Original Message ----- 
>>>From: "Jakob Braeuchi" <jbraeuchi@gmx.ch>
>>>To: "OJB Users List" <ojb-user@db.apache.org>
>>>Sent: Saturday, June 11, 2005 8:06 PM
>>>Subject: Re: Oracle exception: column ambiguously defined - need help
>>>
>>>
>>>
>>>
>>>>hi thomas,
>>>>
>>>>does the error only occur when using oracle ?
>>>>which column is ambiguous ?
>>>>
>>>>jakob
>>>>
>>>>thomas0@etu.unige.ch schrieb:
>>>>
>>>>
>>>>>Thanks for correcting my wrong use of criterias.
>>>>>
>>>>>Unfortunately this doesn't solve my problem.
>>>>>
>>>>>Let me explain my situation with more details. 
>>>>>
>>>>>I want to model a filesystem with java classes.
>>>>>
>>>>>First there's the Element class. Then there's the Fichier (File in french)
>>>>>class
>>>>>and the Folder class who inherits from Element.
>>>>>
>>>>>I want the Folder class to have a collection of Fichiers and a collection
> 
> of
> 
>>>>>Folders to represent the Elements contained in each folder.
>>>>>
>>>>>I want also to do a cross-link by having a collection in the Element class
>>>
>>>to
>>>
>>>
>>>>>represent the list of containers for an Element.
>>>>>
>>>>>Here's an image of the structure: 
>>>>>http://www.ashtom.net/img/objectLayerStructure.gif
>>>>>
>>>>>Here's a representation of the database structure:
>>>>>http://www.ashtom.net/img/database_model.gif
>>>>>
>>>>>Here's the repository who fits at best with what I want to represent with
> 
> my
> 
>>>>>model: 
>>>>>http://www.ashtom.net/files/not_working_repository.xml
>>>>>
>>>>>Unfortunately, this repository raises an Oracle exception (ORA-00918:
> 
> column
> 
>>>>>ambiguously defined).
>>>>>
>>>>>On the second hand, this configuration below works but it doesn't allow
to
>>>
>>>my
>>>
>>>
>>>>>application to differentiate a Folder from a Fichier:
>>>>>http://www.ashtom.net/files/working_repository.xml
>>>>>
>>>>>I know this is a lot of trouble but I really don't know how to solve this
>>>>>problem.
>>>>>
>>>>>Thanks a lot for your help !
>>>>>
>>>>>Alexandre
>>>>>
>>>>>----- Original Message ----- 
>>>>>From: "Jakob Braeuchi" <jbraeuchi@gmx.ch>
>>>>>To: "OJB Users List" <ojb-user@db.apache.org>
>>>>>Sent: Friday, June 10, 2005 6:18 PM
>>>>>Subject: Re: Oracle exception: column ambiguously defined - need help
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>hi thomas,
>>>>>>
>>>>>>if the attribute is named 'InRoot' then you should use this name in
the 
>>>>>>query and not the name of the column !
>>>>>>
>>>>>>jakob
>>>>>>
>>>>>>thomas0@etu.unige.ch schrieb:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Thanks for answering me,
>>>>>>>
>>>>>>>The two m:n relations from are here because I wanted my application
(a JSF
> 
> 
>>>>>>>application) to differentiate a "matis.component.filemanager.Fichier"
> 
> from
> 
>>>a
>>>
>>>
>>>>>>>"matis.component.filemanager.Folder", so I can use methods specific
to
> 
> each
> 
>>>>>>>class.
>>>>>>>For the query part, there's actually a "InRoot" attribute (a boolean)
>>>>>
>>>>>described
>>>>>
>>>>>
>>>>>
>>>>>>>in my repository in the
>>>>>>>"matis.component.filemanager.Element" (which is the parent class
of
> 
> Fichier
> 
>>>>>and
>>>>>
>>>>>
>>>>>
>>>>>>>Folder) class descriptor. 
>>>>>>>
>>>>>>>extract of Element class descriptor:
>>>>>>>
>>>>>>>    <field-descriptor
>>>>>>>       name="InRoot"
>>>>>>>       column="INROOT"
>>>>>>>       jdbc-type="INTEGER"
>>>>>>>      
>>>>>>>
>>>>>
>>>>>
> conversion="org.apache.ojb.broker.accesslayer.conversions.Boolean2IntFieldConversion"/>
> 
>>>>>>>How could I build a query with this attribute (as it is a boolean
I don't
>>>>>
>>>>>know
>>>>>
>>>>>
>>>>>
>>>>>>>how to compare InRoot with a boolean).
>>>>>>>
>>>>>>>Thanks again for your help,
>>>>>>>
>>>>>>>Alexandre
>>>>>>>
>>>>>>>
>>>>>>>----- Original Message ----- 
>>>>>>>From: "Jakob Braeuchi" <jbraeuchi@gmx.ch>
>>>>>>>To: "OJB Users List" <ojb-user@db.apache.org>
>>>>>>>Sent: Wednesday, June 08, 2005 6:56 PM
>>>>>>>Subject: Re: Oracle exception: column ambiguously defined - need
help
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>hi alexandre,
>>>>>>>>
>>>>>>>>the mapping looks a little strange to me. From Folder you
have two m:n 
>>>>>>>>relationships pointing to different classes, but both use
the same 
>>>>>>>>indirection table. another thing is the attribute 'INROOT'
in the 
>>>>>>>>criteria. there's no such attribute defined in the class-descriptor
of 
>>>>>>>>class 'Folder'. the attributes used in a query are usually
attributesof 
>>>>>>>>the class not columns.
>>>>>>>>
>>>>>>>>jakob
>>>>>>>>
>>>>>>>>thomas0@etu.unige.ch schrieb:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>Hello,
>>>>>>>>>
>>>>>>>>>My name is Alexandre and I'm a student in the University
of Geneva in
> 
> the
> 
>>>>>>>>>Information Systems departement.
>>>>>>>>>
>>>>>>>>>I'm currently working on a web application using an OJB
layer. I have a
>>>>>>>>>problem of column ambiguously defined when querying my
Oracle Database
>>>>>>>
>>>>>>>through
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>>OJB's QueryFactory method. I read Jakob Braeuchi's  answer
to Naveen
>>>>>>>
>>>>>>>regarding
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>>column prefixes.
>>>>>>>>>Unfortunately, I didn't quite understand his solution:
how can I base
>>>>>>>
>>>>>>>columns
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>>on
>>>>>>>>>attributes?
>>>>>>>>>
>>>>>>>>>here is the method querying the database:
>>>>>>>>>
>>>>>>>>>PersistenceBroker broker =
>>>>>>>>>PersistenceBrokerFactory.defaultPersistenceBroker();
>>>>>>>>>     Collection rootelements = null;
>>>>>>>>>     Criteria crit = new Criteria();
>>>>>>>>>     crit.addEqualTo("INROOT","1");
>>>>>>>>>     Query q = QueryFactory.newQuery(Folder.class, crit);
       
>>>>>>>>>     try{
>>>>>>>>>         rootelements = broker.getCollectionByQuery(q);
>>>>>>>>>     }
>>>>>>>>>     catch(PersistenceBrokerException e)
>>>>>>>>>     {
>>>>>>>>>         e.printStackTrace();
>>>>>>>>>         if(broker != null) broker.abortTransaction();
>>>>>>>>>     }
>>>>>>>>>     finally
>>>>>>>>>     {
>>>>>>>>>         if (broker != null) broker.close();
>>>>>>>>>     }
>>>>>>>>>
>>>>>>>>>And here is the part of the relevant repository:
>>>>>>>>>
>>>>>>>>><class-descriptor
>>>>>>>>> schema="FM"   
>>>>>>>>> class="matis.component.filemanager.Folder" 
>>>>>>>>> table="FOLDER">
>>>>>>>>> 
>>>>>>>>>   <field-descriptor
>>>>>>>>>      name="id_Elem"
>>>>>>>>>      column="ID_FOLDER"
>>>>>>>>>      jdbc-type="INTEGER"
>>>>>>>>>      primarykey="true"/>
>>>>>>>>>   
>>>>>>>>>    <collection-descriptor
>>>>>>>>>      name="fichiersContained" 
>>>>>>>>>
>>>>>>>
>>>>>>>
> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList"
> 
>>>>>>>>>      element-class-ref="matis.component.filemanager.Fichier"
>>>>>>>>>      auto-retrieve="true"
>>>>>>>>>      auto-update="true"
>>>>>>>>>      auto-delete="true"
>>>>>>>>>      indirection-table="INFOLD">
>>>>>>>>>      <fk-pointing-to-this-class column="FOL_ID_ELEMENT"/>
>>>>>>>>>      <fk-pointing-to-element-class column="ID_ELEMENT"/>
>>>>>>>>>    </collection-descriptor>
>>>>>>>>>   
>>>>>>>>>    <collection-descriptor
>>>>>>>>>      name="foldersContained" 
>>>>>>>>>
>>>>>>>
>>>>>>>
> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList"
> 
>>>>>>>>>      element-class-ref="matis.component.filemanager.Folder"
>>>>>>>>>      auto-retrieve="true"
>>>>>>>>>      auto-update="true"
>>>>>>>>>      auto-delete="true"
>>>>>>>>>      indirection-table="INFOLD">
>>>>>>>>>      <fk-pointing-to-this-class column="FOL_ID_ELEMENT"/>
>>>>>>>>>      <fk-pointing-to-element-class column="ID_ELEMENT"/>
>>>>>>>>>    </collection-descriptor>
>>>>>>>>>    
>>>>>>>>>    <reference-descriptor name="super"
>>>>>>>>>      class-ref="matis.component.filemanager.Element"
>>>>>>>>>      auto-retrieve="true"
>>>>>>>>>      auto-update="true"
>>>>>>>>>      auto-delete="true">
>>>>>>>>>     <foreignkey field-ref="id_Elem"/>
>>>>>>>>> </reference-descriptor>
>>>>>>>>> 
>>>>>>>>></class-descriptor>
>>>>>>>>>
>>>>>>>>>When logging OJB's activity, I can see the query who raises
the Oracle
>>>>>>>>>exception (ORA-00918: column ambiguously defined), here
it is:
>>>>>>>>>
>>>>>>>>>SELECT A0.ID_FOLDER 
>>>>>>>>
>>>>>>>>>FROM FM.FOLDER A0,INFOLD,FM.element A1,INFOLD 
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>WHERE A0.ID_FOLDER=A1.ID_ELEMENT 
>>>>>>>>>AND ((INFOLD.FOL_ID_ELEMENT IN (?,?,?)) 
>>>>>>>>>AND A0.ID_FOLDER = INFOLD.ID_ELEMENT)
>>>>>>>>>
>>>>>>>>>I'm a bit lost since it's my first time using OJB (I hope
it's not the
>>>>>>>>>last).
>>>>>>>>>
>>>>>>>>>Thanks a lot for reading my request and thanks in advance
for your
> 
> help.
> 
>>>>>>>>>Yours Sincerely,
>>>>>>>>>
>>>>>>>>>Alexandre Thomas
> 
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
> 
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org


Mime
View raw message