Re: Oracle exception: column ambiguously defined - need help

Mon, 13 Jun 2005 11:11:25 -0700 

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

[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]>

To: "OJB Users List" <[email protected]>
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

[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]>

To: "OJB Users List" <[email protected]>
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

[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]>

To: "OJB Users List" <[email protected]>
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

[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]>

To: "OJB Users List" <[email protected]>
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

[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]




---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]























					Reply via email to