?
Solved

Problem in using prepared statement bind variables in ORDER BY clause

Posted on 2004-09-21
5
Medium Priority
?
950 Views
Last Modified: 2008-01-09
We are trying to use prepared statement bind variables in the following query.

String sqlString = select name, address, phone from loginmaster where loginid like upper(?);

then,
pstmt = dbServer.jdbc_connection.prepareStatement( sqlString );
pstmt.setString(1, loginID);
rset = pstmt.executeQuery();

This works. But when we try to use bind variables for the ORDER BY clause the result set fetched is not ordered, i.e.
String sqlString = select name, address, phone from loginmaster where loginid like upper(?) ORDER BY UPPER (?);
pstmt.setString(1, loginID);
pstmt.setString(2, orderby);
rset = pstmt.executeQuery();
The results are not ordered whereas when we don't use the bind variables and pass the variable then it orders properly,
String sqlString = select name, address, phone from loginmaster where loginid like upper(?) ORDER BY UPPER (" + orderby + ")";
pstmt.setString(1, loginID);
rset = pstmt.executeQuery();

Can't Order by clause in SQL statements be used with prepared statements?
0
Comment
Question by:innumonenu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 92

Assisted Solution

by:objects
objects earned 80 total points
ID: 12119168
I don't think you can bind for the order by clause.
0
 
LVL 22

Assisted Solution

by:Helena Marková
Helena Marková earned 80 total points
ID: 12120067
Objects is right.
0
 
LVL 12

Accepted Solution

by:
Giant2 earned 340 total points
ID: 12120185
PreparedStatement are used for prepare a statement could be executed several times.
It precompile (generally) the SQL and send it to the DB for a quicker execution.
If you insert a ORDER BY clause in the preparedStatement when sent to DB and precompiled it cannot precompile the ORDER BY clause because every time could change and even the indexed (sorted) column(s) cannot be used. So this ORDE BY clause is ignored or executed only the first time.

Hope this clarify your doubt.
Bye, Giant.
0
 
LVL 12

Expert Comment

by:Giant2
ID: 12120200
From JavaDOC:
prepareStatement
Note: This method is optimized for handling parametric SQL statements that benefit from precompilation. If the driver supports precompilation, the method prepareStatement will send the statement to the database for precompilation. Some drivers may not support precompilation. In this case, the statement may not be sent to the database until the PreparedStatement object is executed. This has no direct effect on users; however, it does affect which methods throw certain SQLException objects.

0
 

Author Comment

by:innumonenu
ID: 12127220
Thanks everyone for the input
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses
Course of the Month13 days, 15 hours left to enroll

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question