db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ralph Musch <Ralph.Mu...@alabus.com>
Subject Outer Join Error in generated SQL statement (Report Query) for ex tended Ojb classes
Date Wed, 18 May 2005 09:09:59 GMT
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


Mime
View raw message