Re: PreparedStatement + "order by ?"
- From: "Bill Karwin" <bill@xxxxxxxxxx>
- Date: Fri, 17 Feb 2006 10:18:18 -0800
"Chris" <krystofffff@xxxxxxxxx> wrote in message
news:1140182918.954098.131260@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.
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.
.
- References:
- PreparedStatement + "order by ?"
- From: Chris
- PreparedStatement + "order by ?"
- Prev by Date: Re: PreparedStatement + "order by ?"
- Next by Date: Re: link DB record through browser...
- Previous by thread: Re: PreparedStatement + "order by ?"
- Next by thread: [ANNOUNCE] Business-to-Business Data Integration in a SOA World
- Index(es):
Relevant Pages
|
|
