Hello,
Here are more input on the subject. I'm working on porting the Content Management
System www.infoglue.org to DB2.
Extract from mapping file:
..
<!-- Mapping for RepositoryLanguage -->
<class
name="org.infoglue.cms.entities.management.impl.simple.RepositoryLanguageImpl"
identity="repositoryLanguageId" key-generator="IDENTITY">
<description>RepositoryLanguage</description>
<cache-type type="none"/>
<map-to table="cmRepositoryLanguage"/>
<field name="repositoryLanguageId" type="java.lang.Integer">
<sql name="repositoryLanguageId" type="integer" dirty="check" />
</field>
<field name="isPublished" type="java.lang.Boolean">
<sql name="isPublished" type="integer" dirty="check" />
</field>
<field name="language"
type="org.infoglue.cms.entities.management.impl.simple.LanguageImpl">
<sql name="languageId"/>
</field>
<field name="repository"
type="org.infoglue.cms.entities.management.impl.simple.RepositoryImpl">
<sql name="repositoryId"/>
</field>
</class>
..
<!-- Mapping for Language -->
<class name="org.infoglue.cms.entities.management.impl.simple.LanguageImpl"
identity="languageId" key-generator="IDENTITY">
<description>Language</description>
<cache-type type="none"/>
<map-to table="cmLanguage"/>
<field name="languageId" type="java.lang.Integer">
<sql name="languageId" type="integer" dirty="check" />
</field>
<field name="name" type="java.lang.String">
<sql name="name" type="varchar" dirty="check" />
</field>
<field name="languageCode" type="java.lang.String">
<sql name="languageCode" type="varchar" dirty="check" />
</field>
<field name="charset" type="java.lang.String">
<sql name="charset" type="varchar" dirty="check" />
</field>
<field name="repositoryLanguages"
type="org.infoglue.cms.entities.management.impl.simple.RepositoryLanguageImpl"
lazy="true" collection="collection">
<sql many-key="languageId"/>
</field>
</class>
..
OQL query:
Don't know.
Generated SQL:
SELECT cmLanguage.languageId,cmLanguage.name,cmLanguage.languageCode,
cmLanguage.charset,cmRepositoryLanguage.repositoryLanguageId
FROM cmLanguage LEFT OUTER JOIN cmRepositoryLanguage on
cmLanguage.languageId=cmRepositoryLanguage.languageId;
Generated SQL I want to have:
SELECT cmLanguage.languageId,cmLanguage.name,cmLanguage.languageCode,
cmLanguage.charset,cmRepositoryLanguage.repositoryLanguageId
FROM cmLanguage LEFT OUTER JOIN cmRepositoryLanguage on
cmLanguage.languageId=cmRepositoryLanguage.languageId
ORDER BY cmLanguage.languageId BY cmRepositoryLanguage.repositoryLanguageId;
Without the "ORDER BY" clause DB2 returns the records in a different order. IBM says
that the sortorder is undefined and ORDER BY must be used.
As I see it it would not have a negative impact on other databases to add an ORDER BY
clause on all the "identity" columns since Castor internally removes doubles (copies)
of records and only returns unique records based on the "identity" column.
Regards,
Fredrik
WM-data
-----Original Message-----
From: Werner Guttmann [mailto:[EMAIL PROTECTED]
Sent: den 25 maj 2004 16:35
To: [EMAIL PROTECTED]
Subject: Re: [castor-user] DB2 sortorder for OUTER JOINs
Fredrik,
can you please provide me with a full example, incl. the OQL statement you want to
use, the SQL generated, the classes used, mapping file, etc.
Regards
Werner
On Tue, 25 May 2004 16:12:09 +0200, Richtn�r Fredrik wrote:
>Hello,
>
>I'm using Castor on DB2. The thing is that DB2 sorts the resultset differently than
>other databases (MySQL, Oracle) for OUTER JOINs. The hint I got from
IBM is to add ORDER BY to the SQL query. So my question to the list is if it is
possible to configure Castor to add ORDER BY clauses to the SQL
generated?
>
>/Fredrik
>WM-data
>
>
-----------------------------------------------------------
If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
unsubscribe castor-user
