Hi again, Im using Oracle 9 and castor 1.0.5 and ive also tried it on 1.1.
Here is my mapping for Empresa
<class name="com.imaxin.dxcm.bean.Empresa" identity="idempresa"
key-generator="MAX">
<!-- <description>Empresa</description><cache-type
type="none"/> -->
<description>Empresa</description><cache-type type="none"/>
<map-to table="empresa" xml="empresa"/>
<field name="idempresa" type="integer">
<sql name="idempresa" type="integer"/>
<bind-xml node="element"/>
</field>
<field name="nifempresa" type="string">
<sql name="nifempresa" type="varchar"/>
<bind-xml node="element"/>
</field>
<field name="nomempresa" type="string">
<sql name="nomempresa" type="varchar"/>
<bind-xml node="element"/>
</field>
<field name="importando" type="char">
<sql name="importando" type="char"/>
<bind-xml node="element"/>
</field>
<field name="numusuarios" type="integer">
<sql name="numusuarios" type="integer"/>
<bind-xml node="element"/>
</field>
<field name="datamodificacion" type="date">
<sql name="datamodificacion" type="integer[YMDhmsS]"/>
<bind-xml node="element"/>
</field>
<field name="datacreacion" type="date">
<sql name="datacreacion" type="integer[YMDhmsS]"/>
<bind-xml node="element"/>
</field>
<field name="dataeliminacion" type="date">
<sql name="dataeliminacion" type="integer[YMDhmsS]"/>
<bind-xml node="element"/>
</field>
<field name="ultimousuario" type="string">
<sql name="ultimousuario" type="varchar"/>
<bind-xml node="element"/>
</field>
<field name="informes" type="com.imaxin.dxcm.bean.Informe"
collection="arraylist" lazy="true">
<sql many-key="id_empresa"/>
<bind-xml node="element"/>
</field>
</class>
and for its collectiob informes
<class name="com.imaxin.dxcm.bean.Informe" identity="idinforme"
key-generator="MAX">
<description>Informe</description><cache-type type="none"/>
<map-to table="informe" xml="informe"/>
<field name="idinforme" type="integer">
<sql name="idinforme" type="integer"/>
<bind-xml node="element"/>
</field>
<field name="id_empresa" type="integer">
<sql name="id_empresa" type="integer"/>
<bind-xml node="element"/>
</field>
<field name="numinforme" type="integer">
<sql name="numinforme" type="integer"/>
<bind-xml node="element"/>
</field>
<field name="estadoinforme" type="char">
<sql name="estadoinforme" type="char"/>
<bind-xml node="element"/>
</field>
<field name="tipo" type="string">
<sql name="tipo" type="varchar"/>
<bind-xml node="element"/>
</field>
<field name="razonsocial" type="string">
<sql name="razonsocial" type="varchar"/>
<bind-xml node="element"/>
</field>
<field name="anoconstitucion" type="integer">
<sql name="anoconstitucion" type="integer"/>
<bind-xml node="element"/>
</field>
<field name="enderezo" type="string">
<sql name="enderezo" type="varchar"/>
<bind-xml node="element"/>
</field>
<field name="codigopostal" type="integer">
<sql name="codigopostal" type="integer"/>
<bind-xml node="element"/>
</field>
<field name="id_concello" type="integer">
<sql name="id_concello" type="integer"/>
<bind-xml node="element"/>
</field>
<field name="paxinaweb" type="string">
<sql name="paxinaweb" type="varchar"/>
<bind-xml node="element"/>
</field>
<field name="dnipropietario" type="string">
<sql name="dnipropietario" type="varchar"/>
<bind-xml node="element"/>
</field>
<field name="nomepropietario" type="string">
<sql name="nomepropietario" type="varchar"/>
<bind-xml node="element"/>
</field>
<field name="datamodificacion" type="date">
<sql name="datamodificacion" type="integer[YMDhmsS]"/>
<bind-xml node="element"/>
</field>
<field name="datacreacion" type="date">
<sql name="datacreacion" type="integer[YMDhmsS]"/>
<bind-xml node="element"/>
</field>
<field name="dataeliminacion" type="date">
<sql name="dataeliminacion" type="integer[YMDhmsS]"/>
<bind-xml node="element"/>
</field>
<field name="ultimousuario" type="string">
<sql name="ultimousuario" type="varchar"/>
<bind-xml node="element"/>
</field>
<field name="docsanexos" type="com.imaxin.dxcm.bean.Docanexo"
collection="arraylist" lazy="true">
<sql many-key="id_informe"/>
<bind-xml node="element"/>
</field>
<field name="emprazamentos" type="com.imaxin.dxcm.bean.Emprazamento"
collection="arraylist" lazy="true">
<sql many-key="id_informe"/>
<bind-xml node="element"/>
</field>
<field name="observacions" type="string">
<sql name="observacions" type="varchar"/>
<bind-xml node="element"/>
</field>
<field name="outroconcello" type="string">
<sql name="outroconcello" type="varchar"/>
<bind-xml node="element"/>
</field>
<field name="outraprovincia" type="string">
<sql name="outraprovincia" type="varchar"/>
<bind-xml node="element"/>
</field>
<field name="nifempresa" type="string">
<sql name="nifempresa" type="varchar"/>
<bind-xml node="element"/>
</field>
<field name="datarexistro" type="string">
<sql name="datarexistro" type="varchar"/>
<bind-xml node="element"/>
</field>
</class>
Thanks
PS ... looking through the forum i found a similar problem of a user who
submitted a bug at http://jira.codehaus.org/browse/CASTOR-1252 but i cant
access jira (is it down???)
Thanks again.
----- Original Message -----
From: "Ralf Joachim" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Thursday, February 22, 2007 5:13 PM
Subject: Re: [castor-user] OQL problems
Ola Stefanos,
I'll try to take a look at this but could you please tell me the version
of Castor and the database system you are using. Could also post the
relevant part of your mapping?
Ralf
Stefanos schrieb:
Hi there. I have a problem with an oql statement that uses orderby,
offset and limit
The objects im trying to load (company) have a collection (workers)
if i oql them normally i get the full resultset back. If i use the order
by, ofset, limit it only gives my part of what i should be getting
for example:
oql = db.getOQLQuery("SELECT e FROM com.bean.company e WHERE
is_undefined(e.date) ORDER BY e.companyid LIMIT $1 OFFSET $2");
oql.bind(10);
oql.bind(1);
this oql will return me like 3 rows. Depending on the ofset this
changes(the number of objects returned)
If i take out the collection from the mapping it returns me the correct
ones.
any ideas??
here is the query returned by the results object for the same query with
and without the collection
"select * from ( SELECT
\"EMPRESA\".\"IDEMPRESA\",\"EMPRESA\".\"NIFEMPRESA\",\"EMPRESA\".\"NOMEMPRESA\",\"EMPRESA\".\"IMPORTANDO\",\"EMPRESA\".\"NUMUSUARIOS\",\"EMPRESA\".\"DATAMODIFICACION\",\"EMPRESA\".\"DATACREACION\",\"EMPRESA\".\"DATAELIMINACION\",\"EMPRESA\".\"ULTIMOUSUARIO\",\"INFORME\".\"IDINFORME\"
, rank() over ( ORDER BY \"EMPRESA\".\"IDEMPRESA\" ) rnk FROM
\"INFORME\",\"EMPRESA\" WHERE
\"EMPRESA\".\"IDEMPRESA\"=\"INFORME\".\"ID_EMPRESA\"(+) AND
(\"EMPRESA\".\"DATAELIMINACION\" IS NULL ) ) where rnk - ?2 between 1
and ?1 "
"select * from ( SELECT
\"EMPRESA\".\"IDEMPRESA\",\"EMPRESA\".\"NIFEMPRESA\",\"EMPRESA\".\"NOMEMPRESA\",\"EMPRESA\".\"IMPORTANDO\",\"EMPRESA\".\"NUMUSUARIOS\",\"EMPRESA\".\"DATAMODIFICACION\",\"EMPRESA\".\"DATACREACION\",\"EMPRESA\".\"DATAELIMINACION\",\"EMPRESA\".\"ULTIMOUSUARIO\"
, rank() over ( ORDER BY \"EMPRESA\".\"IDEMPRESA\" ) rnk FROM
\"EMPRESA\" WHERE (\"EMPRESA\".\"DATAELIMINACION\" IS NULL ) ) where rnk
- ?2 between 1 and ?1 "
It seems that the problem is with objets that have more than one item in
the collection. It counts them without distinct so out of the 10 objects
i should get, i get only five because one of them has five items in the
collection.
I have tried using distinct but no dice.
any ideas. Is this a known issue or do you think its my programming?
Stefanos Kollias
Dptos. Enxenería do Software e Web
__________________________________________________________
Imaxin|Software
Rúa Salgueiriños de Abaixo, nº 11 - Local 6
15891 Santiago de Compostela A Coruña Gz Sp
Voz +34 981 554 068 Fax +34 981 554 988
E-mail [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
__________________________________________________________
Web www.imaxin.com <http://www.imaxin.com>
--
Syscon Ingenieurbüro für
Meß- und Datentechnik GmbH
Ralf Joachim
Raiffeisenstraße 11
D-72127 Kusterdingen
Germany
Tel. +49 7071 3690 52
Mobil: +49 173 9630135
Fax +49 7071 3690 98
Email: [EMAIL PROTECTED]
Web: www.syscon-informatics.de
---------------------------------------------------------------------
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
---------------------------------------------------------------------
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
