Re: Outer Join Error in generated SQL statement (Report Query) for ex tended Ojb classes

[Jakob Braeuchi]

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


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