db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jakob Braeuchi <jbraeu...@gmx.ch>
Subject Re: Another issue
Date Sat, 31 Jan 2004 20:00:16 GMT
hi andy,

i used the distinct in the prefetch query because the same child may be 
referenced from many parents (see person-project testcases). when looking for 
all person working for a list of projects i'll get some persons who are on 
multiple projects. this isn't really a problem because in associateBatched 
parents and children are put in hashtables.
the query to retrieve mn-implementors works perfectly without distinct.

i'll do further testings before i change the queries ;)

jakob

Jakob Braeuchi wrote:

> hi andy,
> 
> sorry i forgot about the DISTINCT. i'll have a look at it tomorrow.
> 
> jakob
> 
> Andy Malakov wrote:
> 
>> Hello Jacob,
>>  
>> Thank you very much! I believe it will be helpful feature.
>>  
>>  
>> What about not-using DISTINCT (or making it optional) while selecting 
>> link tables (MtoNCollectionPrefetcher)?
>>  
>>     protected Query buildPrefetchQuery(Collection ids)
>>     {
>>         ...                  return new 
>> QueryByMtoNCriteria(cds.getItemClass(),
>>            cds.getIndirectionTable(), crit,
>>            true); // <=true causes "select DISTINCT ..." and problems 
>> in Oracle
>>     }
>>  
>> If disable usage of distinct in this context all my tests seem to be 
>> just fine.
>>  
>> ===================================================================
>> Using SELECT DISTINCT in 
>> MtoNCollectionPrefetcher.buildPrefetchQuery(Collection ids) may cause 
>> problem in Oracle.
>>  
>> Problem: If 'child' table in M:N association has columns of type LOB, 
>> VARRAY, etc (in my case it is SDO geometry type MDSYS.SDO_GEOMETRY) 
>> prefetch query will fail because Oracle does not allow using such 
>> columns in SELECT DISTINCT queries (it cannot compare such types 
>> easily). Here is what error message looks like:
>>  
>> ORA-22901 cannot compare nested table or VARRAY or LOB attributes of 
>> an object type
>> Cause: Comparison of nested table or VARRAY or LOB attributes of an 
>> object type was attempted in the absence of a MAP or ORDER method.
>> Action: Define a MAP or ORDER method for the object type.
>>  
>> Can you please explain why OJB uses DISTINCT select in association 
>> prefetching? It seems that queries like
>>  
>> SELECT [DISTINCT] CHILD_TABLE.* FROM CHILD_TABLE, LINK_TABLE
>> WHERE
>>     LINK_TABLE.PARENT_FK =  '12345'  AND
>>     CHILD_TABLE_PK = LINK_TABLE.CHILD_FK
>>  
>> should not produce duplicate rows (unless there are duplicate records 
>> in LINK_TABLE)
>> ===================================================================
>>  
>> All the Best,
>> Andy
>>  
>> ----- Original Message -----
>> From: "Jakob Braeuchi" <jbraeuchi@gmx.ch <mailto:jbraeuchi@gmx.ch>>
>> To: "OJB Developers List" <ojb-dev@db.apache.org 
>> <mailto:ojb-dev@db.apache.org>>
>> Cc: "Armin Waibel" <armin@code-au-lait.de <mailto:armin@code-au-lait.de>>
>> Sent: Thursday, January 29, 2004 3:55 PM
>> Subject: Re: Another issue
>>
>>  > hi andy,
>>  >
>>  > i just commited the patch to solve this problem.
>>  > the types of the columns can now be defined in the report-query:
>>  >
>>  > Criteria crit = new Criteria();
>>  > Collection results = new Vector();
>>  > int types[] = new int[]{Types.DECIMAL, Types.VARCHAR, Types.BIGINT};
>>  >
>>  > crit.addLike("firstname", "%o%");
>>  > ReportQueryByCriteria q = QueryFactory.newReportQuery(Person.class, 
>> crit);
>>  > q.setColumns(new String[] { "id", "firstname", "count(*)" });
>>  > q.addGroupBy(new String[] { "id", "firstname" });
>>  > q.setJdbcTypes(types);
>>  >
>>  > see QueryTest#testReportQueryWithJdbcTypes()
>>  >
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
> 
> 

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


Mime
View raw message