Solved

Problem in using prepared statement bind variables in ORDER BY clause

Posted on 2004-09-21
5
942 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 20 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 20 total points
ID: 12120067
Objects is right.
0
 
LVL 12

Accepted Solution

by:
Giant2 earned 85 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

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…
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 will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will learn how to implement Singleton Design Pattern in Java.

696 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