aspose file tools*
The moose likes JSP and the fly likes problem in sorting query in JSTL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Bookmark "problem in sorting query in JSTL" Watch "problem in sorting query in JSTL" New topic
Author

problem in sorting query in JSTL

sachin yadav
Ranch Hand

Joined: Nov 24, 2005
Posts: 156
hi all,

i am doing a code using JSTL to fire a query. everything come fine except the resule is not sorted as desired. i am putting the code below--



now the resule is always sorted by USER_ID. if i want to sort it using USER_NAME i pass parameter from controller to this page in sort_oredr variable which comes fine but the result doesn't sort by name, only by id. if i hardcode USER_NAME in query then the result is as desired. please suggest.
Darren Edwards
Ranch Hand

Joined: Aug 17, 2005
Posts: 69
Shouldn't the servlet side of the code read


[ June 21, 2006: Message edited by: Darren Edwards ]
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61218
    
  66

Originally posted by Darren Edwards:
Shouldn't the servlet side of the code read


I'm not sure where you're coming from with that. No mention has been made of the source of "sort_order".


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Darren Edwards
Ranch Hand

Joined: Aug 17, 2005
Posts: 69
I can't see anything technically wrong with the code and jsp sample that's been posted (especially as it works when hardcoded) and I think it's pretty strange to get a request attribute, then set it back exactly the same. IMO, it's more likely that 'sort_order' should come from an external source and getAttribute/getParameter is an easy typo to make. Lots of assumptions though, which is why the answer is phrased as more of a question back to the poster, rather than 'this is what you did wrong ..'.
Stefan Evans
Bartender

Joined: Jul 06, 2005
Posts: 1018
Prepared statements are meant to substitute in values only.
You can't use it in this manner to specify the sort order, any more than you could use it to specify which table to select from eg Select * from ? is an illegal use.
This isn't a limitation of the JSTL sql library - its how prepared statements work in Java.

You might try this:


This will dynamically construct the sql for you - ie the sql string for the query is dynamic, as opposed to using the same string with a prepared statement, and substituting in values.
If you don't have a JSP2 container, replace ${sort_order} with <cut value="${sort_order}"/>

Cheers,
evnafets
sachin yadav
Ranch Hand

Joined: Nov 24, 2005
Posts: 156
Stefan,

thanx for provideing such productive information. But the proble still persists. I have user all the techniques you havee described but now my server (oracle 10g) is throwing this Erorr in error log:-



i have checked the value of sort_order it's coming fine in the page, but the sorting is not done. what you say.....




Originally posted by Stefan Evans:

You might try this:


This will dynamically construct the sql for you - ie the sql string for the query is dynamic, as opposed to using the same string with a prepared statement, and substituting in values.
If you don't have a JSP2 container, replace ${sort_order} with <cut value="${sort_order}"/>

Cheers,
evnafets
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

Is there actually an attribute called "sort_order" in your request ? It seems to be empty/null.


[My Blog]
All roads lead to JavaRanch
sachin yadav
Ranch Hand

Joined: Nov 24, 2005
Posts: 156
i have checked sort_order, and sure that this is not null.

Originally posted by Satou kurinosuke:
Is there actually an attribute called "sort_order" in your request ? It seems to be empty/null.
Stefan Evans
Bartender

Joined: Jul 06, 2005
Posts: 1018
Did you try the following?


Both approaches worked for me on Tomcat5.0.28 with oracle 9 database.

I guess now is the time to bring up the standard reponse of - you shouldn't be using SQL in a JSP page anyway. Write the query in a bean/servlet using java code.
That way you have full control over the JDBC access, and know exactly what it is doing.
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

That's strange. At least it works fine on my machine.
Where did you check the content of sort_order ?
It really looks empty.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: problem in sorting query in JSTL