PreparedStatement + "order by ?"

Thread view:

Chris - 17 Feb 2006 13:25 GMT

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 ?

Reply to this Message

Barry - 17 Feb 2006 14:36 GMT

> Hi
>
[quoted text clipped - 7 lines]
>>PreparedStatement p = new PreparedStatement("SELECT * FROM table ORDER BY ?");
>>p.setString(1,"name");

Nope, I think you need to do:

PreparedStatement p
= new PreparedStatement("SELECT * FROM table ORDER BY " + name);

Unfortunately, that makes it less useful to use a prepared statement.

Reply to this Message

karlheinz klingbeil - 17 Feb 2006 15:12 GMT

Chris schrub am Freitag, 17. Februar 2006 14:25
folgendes:

> Hi
>
[quoted text clipped - 7 lines]
>> PreparedStatement p = new PreparedStatement("SELECT
>> * FROM table ORDER BY ?"); p.setString(1,"name");

This is IMHO a known bug in MySQL JDBC Driver in
Version 3.1 and above, which makes it impossible to
use a PreparedStatement with a "variable" ORDER BY ?.
Ran into this one before, had to use a couple of
PreparedStatements like:
"SELECT * FROM table ORDER BY a";
"SELECT * FROM table ORDER BY b"

and then choose the right one.

Signature

greetz Karlheinz Klingbeil (lunqual)
http://www.lunqual.de http://www.42pixels.de
http://www.rezeptbuch-pro.de

Reply to this Message

Rhino - 17 Feb 2006 15:33 GMT

> Chris schrub am Freitag, 17. Februar 2006 14:25
> folgendes:
[quoted text clipped - 20 lines]
>
> and then choose the right one.

I'm not sure if you should call this behaviour a "bug": that implies that a
variable in an ORDER BY is _supposed_ to work, according to the JDBC
specification.

I'm not sure what the specification says on this subject and I don't know
where it is to consult it; I just tried Googling and didn't see the JDBC
spec.

However, when I tried putting a variable in an ORDER BY within my
PreparedStatment, DB2 refused to execute the statement too. The SQL code
was -418; the message was "A statement contains a use of a parameter marker
that is not valid". According to the Messages manual article about this
error: "Untyped parameter markers cannot be used: in a SELECT list, as the
sole argument of a datetime arithmetic operation, in some cases as the sole
argument of a scalar function, _as a sort key in an ORDER BY clause_". I was
using the new Universal Type 4 JDBC driver and DB2 Version 8.2.]

Therefore, I strongly suspect that this behaviour is _not_ a bug, it is the
way that JDBC is supposed to work.

Now, it's possible that variables _are_ permitted in the ORDER BYs under the
JDBC spec and that MySQL and DB2 simply choose not to support that part of
the spec; I don't know how to determine that with any certainty. But I'm
inclined to think that the spec itself is the culprit here, not a bug in
someone's implementation.

--
Rhino

Reply to this Message

Raymond DeCampo - 17 Feb 2006 16:01 GMT

>>Chris schrub am Freitag, 17. Februar 2006 14:25
>>folgendes:
[quoted text clipped - 46 lines]
> inclined to think that the spec itself is the culprit here, not a bug in
> someone's implementation.

The specification isn't explicit, but the context of the section on
PreparedStatements makes it clear that the ? placeholders are to be used
to set data, i.e. SQL literals, and not to allow you to vary database
schema objects like tables, columns, etc. This is natural and
understandable; the amount of pre-compilation a driver could do would be
extremely limited if the ? could stand for tables or columns.

In the example of the OP, the driver (either at the JDBC or database
level) will compile the prepared statement differently depending on
whether there is an index on the order by column. Allowing the order by
column to vary defeats this and dilutes the effectiveness of the
PreparedStatement.

The proper thing to do in this case is to dynamically generate the SQL,
as pointed out by other posters.

Ray

Signature

This signature intentionally left blank.

Reply to this Message

Rhino - 17 Feb 2006 16:16 GMT

>>>Chris schrub am Freitag, 17. Februar 2006 14:25
>>>folgendes:
[quoted text clipped - 54 lines]
> understandable; the amount of pre-compilation a driver could do would be
> extremely limited if the ? could stand for tables or columns.

Agreed; that's sound reasoning. Where did you find the spec? I must be using
the wrong search terms because I didn't find it with my search.

> In the example of the OP, the driver (either at the JDBC or database
> level) will compile the prepared statement differently depending on
> whether there is an index on the order by column. Allowing the order by
> column to vary defeats this and dilutes the effectiveness of the
> PreparedStatement.

Agreed.

> The proper thing to do in this case is to dynamically generate the SQL, as
> pointed out by other posters.

Yes, I agree with that too. That's why I didn't disagree with Barry's
advice; that's exactly how I have solved the problem myself on several
occasions.

---
Rhino

Reply to this Message

Chris - 17 Feb 2006 16:51 GMT

thanks for your answers

I finally chose to repeat several times the same query, except the
column I want to order by. I will have several PreparedStatement
instances, but it's better than to recompile the query each time.
Hopefully I have a limited list of columns in my table ...

Reply to this Message

EricF - 18 Feb 2006 01:27 GMT

>thanks for your answers
>
>I finally chose to repeat several times the same query, except the
>column I want to order by. I will have several PreparedStatement
>instances, but it's better than to recompile the query each time.
>Hopefully I have a limited list of columns in my table ...

There have been a good number of replies and most (all?) have been quality.

FWIW, you don't need to specify the column name in order by. You can use an
ordinal number where the number points to the column in the select list, i.e.

select a, b, c from t order by 2

will order by b

Having said that, I don't know if that is a sql standard or an extension some
databases use.

It's handy if you are using a sql tool and lazy like me. ;-)

It's also a bit brittle - if someone changes the select list, the ordinal
number may not register as well as the column name.

Eric

Reply to this Message

Raymond DeCampo - 17 Feb 2006 16:53 GMT

>>>>Chris schrub am Freitag, 17. Februar 2006 14:25
>>>>folgendes:
[quoted text clipped - 57 lines]
> Agreed; that's sound reasoning. Where did you find the spec? I must be using
> the wrong search terms because I didn't find it with my search.

I had a copy already downloaded. But for Java specifications I always
start at java.sun.com. Here's the page with the J2EE 1.4 specifications
(scroll to the bottom):

http://java.sun.com/j2ee/1.4/docs/index.html

>>In the example of the OP, the driver (either at the JDBC or database
>>level) will compile the prepared statement differently depending on
[quoted text clipped - 10 lines]
> advice; that's exactly how I have solved the problem myself on several
> occasions.

Ray

Signature

This signature intentionally left blank.

Reply to this Message

Rhino - 17 Feb 2006 17:23 GMT

[snip]

>> Agreed; that's sound reasoning. Where did you find the spec? I must be
>> using the wrong search terms because I didn't find it with my search.
[quoted text clipped - 4 lines]
>
> http://java.sun.com/j2ee/1.4/docs/index.html

Thanks, that's good advice; I should have done the search within the Sun
site, not across the whole web.

--
Rhino

Reply to this Message

karlheinz klingbeil - 18 Feb 2006 13:28 GMT

Rhino schrub am Freitag, 17. Februar 2006 16:33
folgendes:

> Now, it's possible that variables _are_ permitted in
> the ORDER BYs under the JDBC spec and that MySQL and
[quoted text clipped - 3 lines]
> itself is the culprit here, not a bug in someone's
> implementation.

Well, at least the MySQL JDBC Driver 3.0.15 and older
DID work with ? for ORDER BY. Had a problem with it
because an app of mine made use of this and dropped
dead after an update of the driver 8(

Signature

greetz Karlheinz Klingbeil (lunqual)
http://www.lunqual.de http://www.42pixels.de
http://www.rezeptbuch-pro.de

Reply to this Message

Adam Maass - 18 Feb 2006 05:50 GMT

> Hi
>
[quoted text clipped - 11 lines]
> 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

Reply to this Message

Owen Jacobson - 18 Feb 2006 22:06 GMT

>> I tried to following code :
>>
[quoted text clipped - 6 lines]
>
> I'm surprised it worked at all. See below.

Why? You can order by expressions. The resulting query is

SELECT * FROM table ORDER BY 'name'

See it now?

-Owen

Reply to this Message

Raymond DeCampo - 20 Feb 2006 18:03 GMT

>
>
[quoted text clipped - 14 lines]
>
> See it now?

You can also select constant (or otherwise) expressions, but "select ?
from table_name" is not expected to be supported either.

This kind of thing defeats the purpose of a PreparedStatement, which is
to allow the driver to precompile an execution plan.

HTH,
Ray

Signature

This signature intentionally left blank.

Reply to this Message

PreparedStatement + "order by ?"

Free Magazines