Re: PreparedStatement + "order by ?"


"Chris" <krystofffff@xxxxxxxxx> 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 :(

I'm surprised it worked at all. See below.


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 ...


Short answer: no, you can't do this.

Longer answer: The '?' syntax in PreparedStatements is intended to be used
for input parameters (only). (IE, what Oracle calls 'bind variables' --
other databases have different names for essentially the same concept.) They
are not a general-purpose placeholder for whatever bit of SQL syntax you
might find handy.

It might be possible to find a JDBC driver/database that supports the '?'
syntax to do what you propose, but it would be a non-standard feature that
wouldn't be portable to most other databases.


-- Adam Maass


.

Relevant Pages

  • Re: Comparing database dates: SELECT FROM WHERE Date(today) < MYDATECOL ?
    ... How do I specify this in a SQL query from Java? ... You can in many databases use '' around the date: ... By using a PreparedStatement, you can make your queries less db specific, ...
    (comp.lang.java.databases)
  • Re: ASP/Access query syntax question
    ... There are indeed places where the SQL Syntax of various databases can be ... NG, not by private emial, please), I'll help you with your query. ...
    (microsoft.public.frontpage.client)
  • Re: Escape character weirdness
    ... The problem of course is that you can't return brackets from the ... Sql Server uses brackets '' to escape. ... I tried a 'LIKE ' query, ... PreparedStatement p = c.prepareStatement("insert into #foo ...
    (microsoft.public.sqlserver.jdbcdriver)
  • RE: EXISTS reserved word in FROM clause
    ... we make a good pair because I know Access SQL ... from what you described the query syntax that I ... >The subquery should return the records: ...
    (microsoft.public.access.queries)
  • Re: Exclusive Form
    ... You're using the syntax for an UPDATE query, ... "'", dbFailOnError ... I created a new table called FormLock with one field named ...
    (microsoft.public.access.formscoding)