Solved

Problem in using prepared statement bind variables in ORDER BY clause

Posted on 2004-09-21
5
937 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
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
eclipse shortcuts 9 62
Custom Java Application connects to database: Which one of the 20? 4 35
hibernate example using maven 12 56
Setup GlassFish 4 21
For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
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:
This video teaches viewers about errors in exception handling.

790 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