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: Outer Join Error in generated SQL statement (Report Query) for ex tended Ojb classes
Date Sun, 22 May 2005 16:11:20 GMT
hi ralph,

i think fixed it in OJB 1.1. but i still have problems with the 
testcase: it should return 2 companies instead of just 1:

SELECT DISTINCT A0.OBJ_ID,A0.NAME FROM INHERITANCE_MULTI_COMPANY A0 LEFT 
OUTER JOIN INHERITANCE_MULTI_MANAGER A1 ON A0.OBJ_ID=A1.OBJ_ID_2 LEFT 
OUTER JOIN INHERITANCE_MULTI_EXECUTIVE A2 ON A1.SUPER_ID=A2.OBJ_ID AND 
A1.SUPER_ID_2=A2.OBJ_ID_2 LEFT OUTER JOIN INHERITANCE_MULTI_EMPLOYEE A3 
ON A2.SUPER_ID=A3.OBJ_ID AND A2.SUPER_ID_2=A3.OBJ_ID_2 WHERE (A0.NAME 
LIKE ?) AND A3.NAME LIKE ? ORDER BY 1

jakob

Jakob Braeuchi schrieb:
> hi ralph,
> 
> super-table inheritance does have some problems in ojb 1.0.x :(
> i'll have a look at it asap.
> 
> gruss aus bern
> jakob
> 
> Ralph Musch schrieb:
> 
>> Hi all,
>>
>> I hope I can explain a possible bug I found.
>>
>> I use class descriptors similiar to those described below.
>>
>> As you can see, the class 'TestRef' uses a reference descriptor on the 
>> from
>> 'ExtendsTestA' extended class 'ExtendsTestB'.
>> When I now make a REPORT QUERY on 'TestRef', with an outer join on
>> 'extendsTestB' (I want all recs from TestRef, if they have an 
>> extendsTestB
>> or not), I only receive the ones with an extendsTestB. Thats because the
>> generated SQL statement only has an outer join on extendsTestB, but an 
>> INNER
>> JOIN from extendsTestB to extendsTestA.
>>
>> The only database this works is PostgreSql, there the generated SQL
>> statement has some brackets which solve the error. Oracle, MaxDB and 
>> MsSQL
>> have this problem.
>>
>> Heres the code I used for testing and the statement generated by OJB for
>> Oracle:
>>
>> Criteria crit = new Criteria();
>> ReportQueryByCriteria q = QueryFactory.newReportQuery(TestRef.class, new
>> String[]
>>     
>> {"id","someValueFromTest","extendsTestBId","extendsTestB.someValueFromA"},cr 
>>
>> it,true);
>> q.setPathOuterJoin("extendsTestB");
>> System.out.println(q.toString());
>> System.out.println(broker.serviceSqlGenerator().getPreparedSelectStatement(q 
>>
>> ,broker.getClassDescriptor(TestRef.class)));
>> OJBIterator iter = (OJBIterator)broker.getReportQueryIteratorByQuery(q);
>> while (iter.hasNext()) {
>>     Object[] tmp = (Object[])iter.next();
>>     System.out.println("ID: " + tmp[0]);
>>     System.out.println("someValueFromTest: " + tmp[1]);
>>     System.out.println("extendsTestBId: " + tmp[2]);
>>     System.out.println("someValueFromA: " + tmp[3]);
>> }
>> iter.releaseDbResources();
>> ----
>> ReportQuery from class zh10045.TestRef id someValueFromTest 
>> extendsTestBId
>> extendsTestB.someValueFromA
>> SELECT DISTINCT A0.ID,A0.VALUE,A0.EXTENDSTESTBID,A2.VALUE FROM TEST
>> A0,EXTENDSTESTB A1,EXTENDSTESTA A2 WHERE A1.ID=A2.ID AND 
>> A0.EXTENDSTESTBID=A1.ID(+)
>>
>> A1.ID=A2.ID --> this is the problem, it should be A1.ID=A2.ID(+)
>>
>> Greetings and thanks for the good work you do with OJB,
>> Ralph Musch
>>
>>
>>
>> <class-descriptor
>>     class="zh10045.TestRef"    table="TEST">
>>     <field-descriptor
>>         name="id"
>>         column="ID"
>>         jdbc-type="VARCHAR" size="50"
>>         primarykey="true"
>>         autoincrement="true"
>>     />
>>     <field-descriptor
>>         name="someValueFromTest"
>>         column="VALUE"
>>         jdbc-type="INTEGER"
>>     />
>>     <field-descriptor
>>         name="extendsTestBId"
>>         column="EXTENDSTESTBID"
>>         jdbc-type="VARCHAR" size="50"
>>     />
>>     <reference-descriptor name="extendsTestB"
>> class-ref="zh10045.ExtendsTestB" >
>>         <foreignkey field-ref="extendsTestBId"/>
>>     </reference-descriptor>
>>
>> </class-descriptor>
>>     
>> <class-descriptor
>>     class="zh10045.ExtendsTestA"
>>     table="EXTENDSTESTA"
>>
>>     <field-descriptor
>>         name="id"
>>         column="ID"
>>         jdbc-type="VARCHAR" size="50"
>>         primarykey="true"
>>         autoincrement="true"
>>     />
>>     <field-descriptor
>>         name="someValueFromA"
>>         column="VALUE"
>>         jdbc-type="INTEGER"
>>     />
>> </class-descriptor>
>>
>> <class-descriptor
>>     class="zh10045.ExtendsTestB"
>>     table="EXTENDSTESTB"
>>
>>     <field-descriptor
>>         name="id"
>>         column="ID"
>>         jdbc-type="VARCHAR" size="50"
>>         primarykey="true"
>>         autoincrement="true"
>>     />
>>
>>     <field-descriptor
>>         name="someValueFromB"
>>         column="VALUE"
>>         jdbc-type="INTEGER"
>>     />
>>
>>     <reference-descriptor name="super"
>>         class-ref="zh10045.ExtendsTestA"
>>         auto-retrieve="true"
>>         auto-update="true"
>>         auto-delete="true"
>>     >
>>         <foreignkey field-ref="id"/>
>>     </reference-descriptor>
>> </class-descriptor>
>>
>>
>>
>>
>> Mit freundlichen Grüssen
>> Ralph Musch
>> Diplom-Informatiker FH
>> Senior Software Engineer
>>
>> alabus ag
>> Graben 5
>> CH-6300 Zug
>>
>> Phone:+41 (0)41 729 88 77
>> Fax:    +41 (0)41 729 88 78
>> Mail:     ralph.musch@alabus.com
>> www:    www.alabus.com
>>
>> alabus ag beschäftigt Business- und Technologieexperten in den 
>> Kernbereichen
>> CRM, ERM und Business Integration. alabus legt den Fokus auf
>> business-orientierte IT-Lösungen, die unternehmensintern neue Standards
>> setzen. Zu unseren Kunden gehören zukunftsgestaltende Unternehmen aus der
>> Schweiz mit internationaler Ausrichtung.
>>
>>
>> ---------------------------------------------------------------------
>> 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
> 
> 

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