From: Craig L Russell <Craig.Russell_at_Sun.COM>
Date: Tue, 09 Jan 2007 09:00:40 -0800

Hi Tim,

On Jan 9, 2007, at 7:05 AM, Romanowski, Tim wrote:

> Thanks for the suggestion; that looks like a reasonable
> alternative. Out of curiosity, is there a technical reason that
> the ORDER BY clause cannot accept a parameter?
Generally, parameters are used as values not as names of fields. For
example, in the query "SELECT t FROM Task t WHERE :fieldname
= :value" how would you distinguish between :fieldname which is
supposed to contain the field you want to compare and :value which is
supposed to contain the value against which to compare the field?

You would need a different parameter marker to distinguish between
parameters used as substitutions in the query versus parameters used
as values. This would be an interesting feature. Perhaps use a double
"::" for a name substitution, as "SELECT t FROM Task t
WHERE ::fieldname = :value".

Craig
> I understand that the spec may not require it, but it seems like it
> would be a very useful capability (feature enhancement?) in glassfish.
>
>
>
> Tim
>
>
>
> From: Michael.Bouschen_at_Sun.COM [mailto:Michael.Bouschen_at_Sun.COM]
> Sent: Tuesday, January 09, 2007 9:17 AM
> To: persistence_at_glassfish.dev.java.net
> Subject: Re: Can you pass parameters to an Order By clause in
> NamedQuery?
>
>
>
> Hi Tim,
>
> no, the JPA query language does not support input parameters in the
> ORDER BY clause. The expression in the ORDER BY clause must be a
> state field path expression.
>
> I'm afraid, if you want to use named queries, you have to define
> multiple named queries having a different ORDER BY clause. An
> alternative may be using dynamic queries, e.g.
> String queryText = "SELECT t FROM Task t ORDER BY " + sortField +
> " " + sortOrder;
> Query q = em.createQuery(queryText);
> List<Task> result = q.getResultList();
> The above code assumes that sortField holds the name of the field
> you want to use for ordering. Variable sortOrder is either "ASC" or
> "DESC" or the empty string.
>
> I hope this helps.
>
> Regards Michael
>
>
>
> Is it possible to create a named query that uses parameters in the
> “order by” clause? When I include the following named query in an
> entity, it throws the exception below. The exception occurs as
> soon as I deploy my webapp; I only declare the namedquery, I do not
> use it anywhere, so this is the point of failure. The reason I
> want to do this is so that I can have sortable columns in my JSF
> app’s datatables…if there is a more efficient way to do this, I’d
> be interested in suggestions!
>
>
>
> @NamedQuery(name = "Task.findAllOrderByField", query = "SELECT t
> FROM Task t ORDER BY :sortField :sortOrder")
>
>
>
> Exception [TOPLINK-8024] (Oracle TopLink Essentials - 9.1 (Build
> b23)): oracle.toplink.essentials.exceptions.EJBQLException
> Exception Description: Syntax error parsing the query [SELECT t
> FROM Task t ORDER BY :sortField :sortOrder] at [:sortField].
> Internal Exception: line 1:31: expecting IDENT, found ':sortField'
>
>
>
>
>
> Tim
>
>
>
>

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell_at_sun.com
P.S. A good JDO? O, Gasp!