Re: PreparedStatement + "order by ?"
- From: Joe Weinstein <joeNOSPAM@xxxxxxx>
- Date: Fri, 17 Feb 2006 10:05:09 -0800
Chris wrote:
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
.
- References:
- PreparedStatement + "order by ?"
- From: Chris
- PreparedStatement + "order by ?"
- Prev by Date: [ANNOUNCE] Business-to-Business Data Integration in a SOA World
- Next by Date: Re: PreparedStatement + "order by ?"
- Previous by thread: PreparedStatement + "order by ?"
- Next by thread: Re: PreparedStatement + "order by ?"
- Index(es):
Relevant Pages
|
|
