PreparedStatement + "order by ?"

C

Chris

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");
Click to expand...

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");
Click to expand...
But I think it is really impossible this time ...

Any Idea ?
 
B

Barry

Chris said:
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 :
Click to expand...

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

karlheinz klingbeil

Chris schrub am Freitag, 17. Februar 2006 14:25
folgendes:
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 :
Click to expand...

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

Rhino

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


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.
Click to expand...
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.
 
R

Raymond DeCampo

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



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.
Click to expand...

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.
Click to expand...

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
 
R

Rhino

Raymond DeCampo said:
Rhino said:
Chris schrub am Freitag, 17. Februar 2006 14:25
folgendes:


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");

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.
Click to expand...

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.
Click to expand...

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.
Click to expand...
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.
Click to expand...
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.
 
C

Chris

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

Raymond DeCampo

Rhino said:
Rhino said:
message

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



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");

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.


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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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
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.
Click to expand...

Ray
 
E

EricF

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 ...
Click to expand...

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
 
A

Adam Maass

Chris said:
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 :


The query seems to properly compile and execute, but the ResultSet is
not ordered as it should be :(
Click to expand...

I'm surprised it worked at all. See below.
Moreover, I'd like to do something like :
But I think it is really impossible this time ...
Click to expand...

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
 
K

karlheinz klingbeil

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
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.
Click to expand...

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(
 
O

Owen Jacobson

I'm surprised it worked at all. See below.
Click to expand...

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

SELECT * FROM table ORDER BY 'name'

See it now?

-Owen
 
R

Raymond DeCampo

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

SELECT * FROM table ORDER BY 'name'

See it now?
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

PreparedStatement 10
MySQL - How to use SELECT UNION with different ORDER BY statements? 0
Connected SQLite to my java program but information are not submitted 2
Is statement.executeBacth() a transaction? 18
PreparedStatement problem 6
Positioning student result by ranking grade 1
File content in descending order 0
Accessing a protected variable of PreparedStatement 4

Members online

No members online now.
Total: 30 (members: 0, guests: 30)
Robots: 321

Forum statistics

Threads
473,755
Messages
2,569,537
Members
45,021
Latest member
AkilahJaim

Latest Threads

Top