Hans,
We are looking at a possibility to ease the restriction with
some predefined settings, but we don't know yet, what will
happen next (will our runtime create the correct SQL? will
the database be able to handle such SQL?).
In the meantime we have questions for you:
Which database are you using? How does the SQL look like
for your query? We know that most databases do not support
columns in the oderby clause that are not in the select
clause, and you are not selecting the related instances.
thanks,
-marina
Hans Prueller wrote:
> Michael, Marina
>
> so I can assume that there is no possible solution
> for my order-by problem? (except migration of the whole
> project to EJB3 before we can migrate to glassfish?)
>
> hans
>
> ****************
>
> http://hanzz.zapto.org
>
> ****************
>
>
>
>>-----Ursprüngliche Nachricht-----
>>Von: Michael.Bouschen_at_Sun.COM [mailto:Michael.Bouschen_at_Sun.COM]
>>Gesendet: Dienstag, 12. September 2006 23:25
>>An: users_at_glassfish.dev.java.net
>>Betreff: Re: [Fwd: AW: AW: howto order by a CMR field in
>>EJB-QL? (not allowed?)]
>>
>>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
>>>
>>>
>>
>>---------------------------------------------------------------------
>>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
>