Solved

Problem in using prepared statement bind variables in ORDER BY clause

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Java Message handling in Service Layer 3 58
Eclipse IDE - Cannot copy/paste from console output 8 132
github account with ecipse 1 42
javap bin 2 29
INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now