Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > PreparedStatement + "order by ?"

Reply
Thread Tools

PreparedStatement + "order by ?"

 
 
Chris
Guest
Posts: n/a
 
      02-17-2006
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 With Quote
 
 
 
 
Barry
Guest
Posts: n/a
 
      02-17-2006
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");


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 With Quote
 
 
 
 
karlheinz klingbeil
Guest
Posts: n/a
 
      02-17-2006
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.


--
greetz Karlheinz Klingbeil (lunqual)
http://www.lunqual.de http://www.42pixels.de
http://www.rezeptbuch-pro.de
 
Reply With Quote
 
Rhino
Guest
Posts: n/a
 
      02-17-2006

"karlheinz klingbeil" <(E-Mail Removed)> wrote in message
news:dt4p4f$qro$(E-Mail Removed)...
> 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.

--
Rhino


 
Reply With Quote
 
Raymond DeCampo
Guest
Posts: n/a
 
      02-17-2006
Rhino wrote:
> "karlheinz klingbeil" <(E-Mail Removed)> wrote in message
> news:dt4p4f$qro$(E-Mail Removed)...
>
>>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.
>


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

--
This signature intentionally left blank.
 
Reply With Quote
 
Rhino
Guest
Posts: n/a
 
      02-17-2006

"Raymond DeCampo" <(E-Mail Removed)> wrote in message
news:XrmJf.6515$(E-Mail Removed)...
> Rhino wrote:
>> "karlheinz klingbeil" <(E-Mail Removed)> wrote in
>> message news:dt4p4f$qro$(E-Mail Removed)...
>>
>>>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.
>>

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

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 With Quote
 
Chris
Guest
Posts: n/a
 
      02-17-2006
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 With Quote
 
Raymond DeCampo
Guest
Posts: n/a
 
      02-17-2006
Rhino wrote:
> "Raymond DeCampo" <(E-Mail Removed)> wrote in message
> news:XrmJf.6515$(E-Mail Removed)...
>
>>Rhino wrote:
>>
>>>"karlheinz klingbeil" <(E-Mail Removed)> wrote in
>>>message news:dt4p4f$qro$(E-Mail Removed)...
>>>
>>>
>>>>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.
>>>

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

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


Ray

--
This signature intentionally left blank.
 
Reply With Quote
 
Rhino
Guest
Posts: n/a
 
      02-17-2006

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

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


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

--
Rhino


 
Reply With Quote
 
EricF
Guest
Posts: n/a
 
      02-18-2006
In article <(E-Mail Removed) .com>, "Chris" <(E-Mail Removed)> wrote:
>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 With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
PreparedStatement: variable amount of placeholders Timo Nentwig Java 0 02-02-2004 01:56 PM
Display a PreparedStatement content Max Java 6 09-11-2003 09:09 PM
print out PreparedStatement uNConVeNtiOnAL Java 8 09-10-2003 04:49 PM
Discuss: PreparedStatement and Connection Pooling Raj Java 1 08-25-2003 10:34 PM
Execution time problem with Insert Query using PreparedStatement Manoj S. P. Java 2 07-02-2003 03:52 PM



Advertisments