 |
AppletTalk.com
Java discussions newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Chris
Guest
|
 Posted: Fri Feb 17, 2006 2:12 pm Post subject: PreparedStatement + "order by ?" |
 |
|
Hi
Is there a way to use a PreparedStatement with a query such as
"SELECT * FROM table ORDER BY ?;"
where the first parameter is the name of the table field I'd like to
order the results to ?
I tried to following code :
| Quote: | PreparedStatement p = new PreparedStatement("SELECT * FROM table ORDER BY ?");
p.setString(1,"name");
|
The query seems to properly compile and execute, but the ResultSet is
not ordered as it should be :(
Moreover, I'd like to do something like :
| Quote: | PreparedStatement p = new PreparedStatement("SELECT * FROM table ORDER BY ? ?");
p.setObject(1,"name");
p.setObject(2,"ASC");
But I think it is really impossible this time ... |
Any Idea ? |
|
| Back to top |
|
 |
Joe Weinstein
Guest
|
| Posted: Fri Feb 17, 2006 6:12 pm Post subject: Re: PreparedStatement + "order by ?" |
|
|
Chris wrote:
| Quote: | Hi
Is there a way to use a PreparedStatement with a query such as
"SELECT * FROM table ORDER BY ?;"
where the first parameter is the name of the table field I'd like to
order the results to ?
I tried to following code :
PreparedStatement p = new PreparedStatement("SELECT * FROM table ORDER BY ?");
p.setString(1,"name");
The query seems to properly compile and execute, but the ResultSet is
not ordered as it should be :(
Moreover, I'd like to do something like :
PreparedStatement p = new PreparedStatement("SELECT * FROM table ORDER BY ? ?");
p.setObject(1,"name");
p.setObject(2,"ASC");
But I think it is really impossible this time ...
Any Idea ?
|
Hi. This will be impossible for any commercial quality DBMS and driver.
The reason is that those will want to be able to precompile the SQL
and store a query plan in the DBMS to reuse. *Value* markers can
be filled in later, but the DBMS can't make a plan that doesn't know
what columns it will be even looking for or ordering by until later.
That would change the plan completely each time. Eg:
(1 - order by <primary key> will use existing unique index)
(2 - order by <non-indexed field> will have to fill a temp table and sort)
HTH,
Joe Weinstein at BEA Systems |
|
| Back to top |
|
|
Bill Karwin
Guest
|
| Posted: Fri Feb 17, 2006 7:12 pm Post subject: Re: PreparedStatement + "order by ?" |
|
|
"Chris" <krystofffff (AT) gmail (DOT) com> wrote in message
news:1140182918.954098.131260 (AT) o13g2000cwo (DOT) googlegroups.com...
| Quote: | Hi
Is there a way to use a PreparedStatement with a query such as
"SELECT * FROM table ORDER BY ?;"
where the first parameter is the name of the table field I'd like to
order the results to ?
|
Statement parameters can be used to substitute for constant expressions, not
metadata objects by name. You can't do the above, and for similar reasons
you can't do things like "SELECT * FROM ?". As Joe Weinstein points out,
the query optimizer can't decide which indexes to use.
| Quote: | I tried to following code :
PreparedStatement p = new PreparedStatement("SELECT * FROM table ORDER BY
?");
p.setString(1,"name");
The query seems to properly compile and execute, but the ResultSet is
not ordered as it should be 
|
You've substituted the string "name" as a constant string expression, not
the name of a field. So it's as if you had used ORDER BY 'name' instead of
ORDER BY name. Ordering by a constant value is not an error, but it does
mean that every row will tie with every other row in the sorting. Therefore
the order in which the rows are returned is arbitrary.
Regards,
Bill K. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
