Problem with ORDER BY clause in ejb 2.1

[albert_steed]

I have a pretty straightforward problem with an ORDER BY clause. Here it is:

I have a number of parameters passed in argument; they all come from the web tier. One of them (the sixth parameter) is the sort order criterion.

I have my ejb ql ORDER BY clause as follows:

ORDER BY ?6

My app server complains.

Any help appreciated.

[sesques]

As the spec says:

The syntax of the ORDER BY clause is
orderby_clause ::=ORDER BY orderby_item [, orderby_item]*
orderby_item ::= cmp_path_expression [ASC | DESC]

So the orderby_item cannot be an input parameter, but only a cmp_path_expression.

[albert_steed]

Hello Sesques and thanks,
Does that mean the sort order cannot be chosen dynamically? If so I find it a serious limitation. Don't you agree? Could you suggest any way around that limitation?
Albert.

[triathlon98]

You can build EJB-QL queries dynamically. I have not used it mysel just yet, but it is explained in the Jboss manual in the CMP chapter (for pay docs, $10).

Joachim

[sesques]

Hi Albert,
The EJB spec limitations is a big subject, I agree with you. In my opinion, EJB is a good choice when talking about persistence and business processes, but not when talking about data display. In this case, I prefer use a bean with JDBC calls.

Hi Joachim,
Your remark is very interresting, but what do you mean by "build EJB-QL queries dynamically" ?
- JBoss private extensions to EJB-QL (like BEA does) ?
- JBOSS-QL capabilities ?
- Write your own finder method, even in a CMP entity bean (Loubiansky says it is possible) ?

I'm very interrested
Pascal

[triathlon98]

You can build your EJB-QL (Jboss-QL to be precise) query at runtime.

If Loubyanski says it can be done, then it can be done. He is in charge of the CMP developments.

Get the for-pay docos (it only costs $10) and read the CMP chapter. It is all in there.

Joachim

[aloubyansky]

Parameters in order by are not supported even JBossQL at the moment.
What would you pass as the parameter? Column name or a CMP field path?
BTW, I tried to execute a prepared statement passing in a parameter to the order by against hypersonic and it failed.
The only way to do it is to use dynamic-ql.

[albert_steed]

Hello All,
jboss ql seems a pretty powerful extension. The standard spec by sun should try and include something similar.

What would you pass as the parameter? Column name or a CMP field path?

---->column name
Thanks,
Albert.