Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem in using prepared statement bind variables in ORDER BY clause

Posted on 2004-09-21
5
Medium Priority
?
957 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Suggested Courses

597 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