Hi Hans,
I filed an enhancement issue for this:
https://glassfish.dev.java.net/issues/show_bug.cgi?id=1175
an checked in a fix today. The fix should be available in the next
nightly build or in the the next promoted build.
I added a property
com.sun.jdo.spi.persistence.support.ejb.ejbqlc.DISABLE_ORDERBY_VALIDATION
to the CMP EJB QL compiler allowing to disable the validation of the
ORDER BY clause against the SELECT clause. By default the property is
set to false, meaning the compiler checks the ORDER BY clause against
the SELECT clause. Setting the property to true would disable the check,
such that the (non-portable) EJB QL query compiles and executes for a
database that supports this feature. You can set this property using the
glassfish Admin Console:
Application Server -> JVM Settings -> JVM Options -> Add JVM Option:
-Dcom.sun.jdo.spi.persistence.support.ejb.ejbqlc.DISABLE_ORDERBY_VALIDATION=true
I hope this helps.
Regards Michael
> Hi,
>
> I am using MySQL 4.1 and 5.0 versions. Having no problems in
> any of them. Currently I'm trying to figure out how the statements
> look like, the Appserver is generating from the ejb-ql - but I'm
> having some troubles with the P6SpyLogger... but I can acknowledge
> that the following statement works in 4.1 and 5.0:
>
> select id_ from carbean_ order by tagEx_
>
> so MySQl allows ordering by non-selected fields.
>
> hth,
> hans
>
>
>
> ****************
>
> http://hanzz.zapto.org
>
> ****************
>
>
>
>> -----Ursprüngliche Nachricht-----
>> Von: Marina.Vatkina_at_Sun.COM [mailto:Marina.Vatkina_at_Sun.COM]
>> Gesendet: Sonntag, 17. September 2006 00:22
>> An: users_at_glassfish.dev.java.net
>> Betreff: Re: AW: [Fwd: AW: AW: howto order by a CMR field in
>> EJB-QL? (not allowed?)]
>>
>> 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
>>>
>>>
>> ---------------------------------------------------------------------
>> 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
>
>