Hi Hans,
Marina forwarded your message to me, because I joined the users list
after you posted your message.
Please find a few comments inline ...
>
>
> -------- Original Message --------
> From: Hans Prueller <hans.prueller_at_gmx.net>
> Subject: AW: AW: howto order by a CMR field in EJB-QL? (not allowed?)
> To: users_at_glassfish.dev.java.net
>
> Marina,
>
>> 1. You can't order by an item that you do not select. That explains
>> the error message.
>
> that's the point. In my first mail I mentioned that my original EJB-QL
> is violating the spec. Regarding to the comments of the mail you
> forwarded,
> I thought it should work anyway.
>
>> This query should compile ok. Please send us the error message...
>
> That's the reason why I tried it again and sent you the error message.
> So it
>
> definitely won't work - as I expected. I also had a short conversation
> with the Spec Lead Linda DeMichiel some weeks ago which came to the same
> result.
This was my fault. I thought the query is ok, because ORDER BY and
SELECT use the same the identification variable. But you cannot order by
something that is not fetched.
>
> This is why I changed the statement to using the IN-clause:
>
> select distinct object(d) from subscriberbean as d, in (d.instance) as
> inst
> order by inst.name,d.name
>
> ... using the statement above should be non-violating the spec, as also
> instancebean
> is selected in the from clause. this works well and allows ordering by
> related cmr-instance
> attributes (this construct is actually working in another appserver
> without
> problems).
> from my understanding, it should be conforming to the spec.
I think the above query is not valid, because according to the spec the
path expression in the IN clause must be a collection valued
relationship, so a single valued relationship as d.instance is not
allowed. It is very well possible that the other appserver supports
this, but this is not a portable query.
The spec defines that if the SELECT clause of an ORDER BY query is
selects an identification variable, then the ORDER BY expression must be
a state field of the type of the identification variable. So a
relationship used in an IN clause (or JOIN clause) include in the FROM
part does not change what is being selected by the query with respect to
the above rule. So I think adding an IN clause would not solve the problem.
I get from the error messages below, that you are using EJB 2.1,
correct? Is it an option to use JPA and make these EJB3 entities? The
Java Persistence query language allows multiple expressions in the
SELECT clause. So it might be an option to select the subscriberbean and
the instance in the SELECT clause and then ORDER BY an instance field? I
did not test this myself, but maybe this is worth trying.
Regards Michael
>
> but also deploying the above ejb-ql fails with the following message:
>
> Deploying application in domain failed; Fatal Error from EJB Compiler --
> JDO74025:
> JDOCodeGenerator: Caught an Exception validating CMP bean
> 'SubscriberBean'
> in application
> 'smsrouter' module 'smsrouter': JDO75006: Invalid EJBQL query Bean:
> SubscriberBean Method:
> java.util.Collection findAll() EJBQL:
> SELECT DISTINCT OBJECT(c) FROM SubscriberBean AS c, IN(c.instance) as
> inst
> ORDER BY inst.id, c.msisdn
> Error: column(58): JDO75318: Field 'instance' of type 'InstanceBean'
> is not
> a collection valued cmr-field.
> Fatal Error from EJB Compiler -- JDO74025: JDOCodeGenerator: Caught an
> Exception validating CMP bean 'SubscriberBean' in application 'smsrouter'
> module 'smsrouter': JDO75006: Invalid EJBQL query Bean: SubscriberBean
> Method: java.util.Collection findAll() EJBQL: SELECT DISTINCT
> OBJECT(c) FROM
> SubscriberBean AS c, IN(c.instance) as inst ORDER BY inst.id, c.msisdn
> Error: column(58): JDO75318: Field 'instance' of type 'InstanceBean'
> is not
> a collection valued cmr-field.
>
> I have a clear understanding of above error, as
> subscriberbean->instancebean
> is a n->1 relation.
> My problem is: how can I get a working ejb-ql that selects all
> subscribers
> of all instances and
> orders the result by instance.name, subscriber.name?
>
> I think this is a quite unspectacular requirement - somehow it has to be
> possible?
>
> regards,
> Hans
>
>
>
>
> ****************
>
> http://hanzz.zapto.org
>
> ****************
>
>
>> -----Ursprüngliche Nachricht-----
>> Von: Marina.Vatkina_at_Sun.COM [mailto:Marina.Vatkina_at_Sun.COM] Gesendet:
>> Dienstag, 12. September 2006 01:36
>> An: users_at_glassfish.dev.java.net
>> Betreff: Re: AW: howto order by a CMR field in EJB-QL? (not allowed?)
>>
>> Hans,
>>
>> Looks like we are all confused here. (But good thing we asked,
>> because now we see the actual exception).
>>
>> 1. You can't order by an item that you do not select. That explains
>> the error message.
>>
>> 2. Are you trying CMPs or new EJB3/JPA entities?
>>
>> thanks,
>> -marina
>>
>> Hans Prueller wrote On 09/10/06 23:02,:
>> > Hi,
>> > > >>This query should compile ok. Please send us the error message
>> and the >>version you are using.
>> > > > see error message below:
>> > Deploying application in domain failed; Fatal Error from EJB
>> Compiler > --
>> > JDO74025: JDOCodeGenerator: Caught an Exception validating CMP bean
>> > 'SubscriberBean' in application 'smsrouter2' module 'smsrouter': >
>> JDO75006: Invalid EJBQL query Bean:
>> > SubscriberBean Method: java.util.Collection findAll() EJBQL: SELECT
>> > DISTINCT
>> > OBJECT(c)
>> > FROM SubscriberBean AS c ORDER BY c.instance.id, c.msisdn
>> > Error: JDO75336: The orderby item 'c.instance.id' is not compatible
>> > with the select clause.
>> > Fatal Error from EJB Compiler -- JDO74025: JDOCodeGenerator: Caught
>> an > Exception validating CMP bean 'SubscriberBean' in application >
>> 'smsrouter2' module 'smsrouter':
>> > JDO75006: Invalid EJBQL
>> > query Bean: SubscriberBean Method: java.util.Collection findAll()
>> EJBQL:
>> > SELECT DISTINCT OBJECT(c)
>> > FROM SubscriberBean AS c ORDER BY c.instance.id, c.msisdn Error:
>> JDO75336:
>> > The orderby
>> > item 'c.instance.id' is not compatible with the select clause.
>> > > the version I am using is: Sun Java System Application Server
>> Platform > Edition 9.0 (build b48)
>> > > Using the JOIN statement instead of IN(c.instance) is not an
>> option > because I think that JOIN is not part of the EJB-SPEC? (at
>> least there > is another appserver I am using that does not compile
>> EJB-QL's > containing the JOIN keyword)
>> > > thanks for your help!
>> > > hans
>> > > ****************
>> > > http://hanzz.zapto.org
>> > > ****************
>> > > > >>-----Ursprüngliche Nachricht-----
>> >>Von: Marina.Vatkina_at_Sun.COM [mailto:Marina.Vatkina_at_Sun.COM]
>> >>Gesendet: Freitag, 08. September 2006 22:47
>> >>An: users_at_glassfish.dev.java.net
>> >>Betreff: Re: howto order by a CMR field in EJB-QL? (not allowed?)
>> >>
>> >>Hans,
>> >>
>> >>This is the answer that I got from persistence team members:
>> >>
>> >>Hans Prueller wrote:
>> >>
>> >>>hi together!
>> >>> >>>I have been using an open source J2EE appserver for the
>> >>
>> >>last 4 years
>> >>
>> >>>and currently I'm evaluating alternatives as I perhaps want
>> >>
>> >>to migrate
>> >>
>> >>>to another product within near future.
>> >>> >>>One problem I have is, that GlassFish does not allow ORDER
>> >>
>> >>BY clauses
>> >>
>> >>>using single-valued CMR fields which is a critical
>> >>
>> >>requirement in my
>> >>
>> >>>opinion.
>> >>> >>>Think of the following:
>> >>> >>>* @ejb:finder
>> >>> * signature= "java.util.Collection findAllSorted()"
>> >>> * query ="SELECT DISTINCT OBJECT(c) FROM SubscriberBean
>> >>
>> >>AS c ORDER
>> >>
>> >>>BY c.instance.name, c.msisdn"
>> >>
>> >>This query should compile ok. Please send us the error message and
>> the >>version you are using. There has been a problem in validating
>> the >>ORDER BY clause at some point.
>> >>
>> >>
>> >>> >>>Where "c.instance" is a CMR relation (n:1) to CMP EB
>> >>
>> >>InstanceBean. If
>> >>
>> >>>I want to deploy the .ear containing above finder, I get an error
>> >>>message that this EJB-QL is violating the EJB-SPEC which
>> >>
>> >>seems to be
>> >>
>> >>>the case.
>> >>>Then I tried to change the query to make it ejb-ql conform:
>> >>> >>>* @ejb:finder
>> >>> * signature= "java.util.Collection findAll()"
>> >>> * query ="SELECT DISTINCT OBJECT(c) FROM SubscriberBean AS c, IN
>> >>>(c.instance) as inst ORDER BY inst.name, c.msisdn"
>> >>
>> >>This does not work because the collection member declaration (the
>> IN >>clause) requires a collection valued relationship field and
>> c.instance >>seems to be a single valued relationship. But using a
>> JOIN clause >>instead of the IN clause should work:
>> >> SELECT DISTINCT c FROM SubscriberBean AS c JOIN c.instance inst
>> >> ORDER BY inst.name, c.msisdn
>> >>
>> >>
>> >>> >>>Which shouldn't be violating the spec, but I get a deployment
>> error >>>that "c.instance is not a collection valued CMR field".
>> >>> >>>The appserver I am currently using works fine with both >>
>> >>alternatives. >>
>> >>>Is there really no way to sort a ejb-ql result by a >>
>> >>cmr-eb's attribute?
>> >>
>> >>> >>>Any help appreciated,
>> >>>Hans
>> >>> >>>PS: I'm not sure how different GlassFish is from SUN AS9 >>
>> >>(or if there >>
>> >>>is any technical difference at all) but I did download >>
>> >>glassfish from >>
>> >>>sun.com - so I am running SUN AS9.
>> >>
>> >>There is no difference in persistence support.
>> >>
>> >>thanks,
>> >>-marina
>> >>
>> >>> >>
>> >>------------------------------------------------------------
>> ---------
>> >>To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
>> >>For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>> >>
>> > > >
>> ---------------------------------------------------------------------
>> > To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
>> > For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>> >
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
>> For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
> For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>
>