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 Thu, 19 May 2005 16:10:10 GMT
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


Mime
View raw message