PreparedStatement + "order by ?"

Discussion in 'Java' started by Chris, Feb 17, 2006.

  1. Chris

    Chris Guest

    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 ?
    Chris, Feb 17, 2006
    #1
    1. Advertising

  2. Chris

    Barry Guest

    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.
    Barry, Feb 17, 2006
    #2
    1. Advertising

  3. 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
    karlheinz klingbeil, Feb 17, 2006
    #3
  4. Chris

    Rhino Guest

    "karlheinz klingbeil" <> wrote in message
    news:dt4p4f$qro$...
    > 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
    Rhino, Feb 17, 2006
    #4
  5. Rhino wrote:
    > "karlheinz klingbeil" <> wrote in message
    > news:dt4p4f$qro$...
    >
    >>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.
    Raymond DeCampo, Feb 17, 2006
    #5
  6. Chris

    Rhino Guest

    "Raymond DeCampo" <> wrote in message
    news:XrmJf.6515$...
    > Rhino wrote:
    >> "karlheinz klingbeil" <> wrote in
    >> message news:dt4p4f$qro$...
    >>
    >>>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
    Rhino, Feb 17, 2006
    #6
  7. Chris

    Chris Guest

    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 ...
    Chris, Feb 17, 2006
    #7
  8. Rhino wrote:
    > "Raymond DeCampo" <> wrote in message
    > news:XrmJf.6515$...
    >
    >>Rhino wrote:
    >>
    >>>"karlheinz klingbeil" <> wrote in
    >>>message news:dt4p4f$qro$...
    >>>
    >>>
    >>>>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.
    Raymond DeCampo, Feb 17, 2006
    #8
  9. Chris

    Rhino Guest

    [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
    Rhino, Feb 17, 2006
    #9
  10. Chris

    EricF Guest

    In article <>, "Chris" <> 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
    EricF, Feb 18, 2006
    #10
  11. Chris

    Adam Maass Guest

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

    >
    > 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
    Adam Maass, Feb 18, 2006
    #11
  12. 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.


    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(

    --
    greetz Karlheinz Klingbeil (lunqual)
    http://www.lunqual.de http://www.42pixels.de
    http://www.rezeptbuch-pro.de
    karlheinz klingbeil, Feb 18, 2006
    #12
  13. On Fri, 17 Feb 2006 21:50:02 -0800, Adam Maass wrote:

    > "Chris" <> wrote:
    >
    >> 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.


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

    SELECT * FROM table ORDER BY 'name'

    See it now?

    -Owen
    Owen Jacobson, Feb 18, 2006
    #13
  14. Owen Jacobson wrote:
    > On Fri, 17 Feb 2006 21:50:02 -0800, Adam Maass wrote:
    >
    >
    >>"Chris" <> wrote:
    >>
    >>
    >>>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.

    >
    >
    > Why? You can order by expressions. The resulting query is
    >
    > SELECT * FROM table ORDER BY 'name'
    >
    > 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

    --
    This signature intentionally left blank.
    Raymond DeCampo, Feb 20, 2006
    #14
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Manoj S. P.
    Replies:
    2
    Views:
    4,395
    Manoj S. P.
    Jul 2, 2003
  2. Raj
    Replies:
    1
    Views:
    403
    Adam Maass
    Aug 25, 2003
  3. uNConVeNtiOnAL

    print out PreparedStatement

    uNConVeNtiOnAL, Sep 3, 2003, in forum: Java
    Replies:
    8
    Views:
    33,105
    Brian A Palmer
    Sep 10, 2003
  4. Max
    Replies:
    6
    Views:
    15,774
    Roedy Green
    Sep 11, 2003
  5. Timo Nentwig
    Replies:
    0
    Views:
    408
    Timo Nentwig
    Feb 2, 2004
Loading...

Share This Page