Results 1 to 7 of 7
Like Tree1Likes
  • 1 Post By eRaaaa

Thread: prepared statements and order by

  1. #1
    newbie123 is offline Member
    Join Date
    Nov 2009
    Posts
    90
    Rep Power
    0

    Default prepared statements and order by

    Is it possible to form a prepared statement with an ORDER BY clause?

    For example, the following code snippet

    String searchStr = "SELECT * FROM " + tableName + " ORDER BY ?"; // tableName created in method
    searchPS = conn.prepareStatement( searchStr );
    searchPS.setString( 1, order ); // order passed into method
    searchRS = searchPS.executeQuery();

    generates the following error

    There is a ? parameter in the select list. This is not allowed.

  2. #2
    eRaaaa is offline Senior Member
    Join Date
    Oct 2010
    Location
    Germany
    Posts
    787
    Rep Power
    5

    Default Re: prepared statements and order by

    Quote Originally Posted by newbie123 View Post
    This is not allowed.
    Right! It is also not clear to me why you need that? order by is limited solely to constant values​​, determined by your table and not by the user

  3. #3
    newbie123 is offline Member
    Join Date
    Nov 2009
    Posts
    90
    Rep Power
    0

    Default Re: prepared statements and order by

    This sucks. I have a large table of data that the user might want to search in a variety of ways.

  4. #4
    eRaaaa is offline Senior Member
    Join Date
    Oct 2010
    Location
    Germany
    Posts
    787
    Rep Power
    5

    Default Re: prepared statements and order by

    And the problem is? Make a list or something like that, e.g.:

    Ordering by (please choose):
    - column 1
    - column 2
    - column 3
    ....

    then you can simply concatenating the sql string without worrying, because the string is from you, not a input of a user and it`s constant!?! Or why do you care about this?
    Tolls likes this.

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,798
    Rep Power
    19

    Default Re: prepared statements and order by

    Quote Originally Posted by newbie123 View Post
    This sucks. I have a large table of data that the user might want to search in a variety of ways.
    Searching != Ordering.
    Searching is the WHERE clause.
    Ordering is the ORDER BY clause.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  6. #6
    newbie123 is offline Member
    Join Date
    Nov 2009
    Posts
    90
    Rep Power
    0

    Default Re: prepared statements and order by

    I already had this as a workaround; I just don't think it is as clean as passing a parameter to the prepared statement.

    Thanks for your confirmation and comments.

  7. #7
    kire9dk is offline Member
    Join Date
    Sep 2009
    Posts
    15
    Rep Power
    0

    Default Re: prepared statements and order by

    Quote Originally Posted by newbie123 View Post
    I already had this as a workaround; I just don't think it is as clean as passing a parameter to the prepared statement.

    Thanks for your confirmation and comments.
    If you can't use prepared statement you may use something called String.format("Your name is %s", name);
    this way you can "prepare" statements. %s stands for string, other formatting letters you can found here Formatter (Java Platform SE 7 )

    Java Code:
    public static void main(String[] args) {
    		String name = "THE NAME";
    		String s = String.format("Your name is %s", name);
    		System.out.println(s);
    	}
    Last edited by kire9dk; 07-30-2012 at 09:16 PM. Reason: add code

Similar Threads

  1. Be Prepared
    By maknib in forum New To Java
    Replies: 2
    Last Post: 11-26-2010, 04:42 AM
  2. Replies: 6
    Last Post: 02-20-2010, 03:27 AM
  3. opening jar files on pc prepared by mac
    By willemjav in forum Advanced Java
    Replies: 42
    Last Post: 09-27-2008, 05:53 PM
  4. Prblem in Prepared Statement
    By haneeshrawther in forum JDBC
    Replies: 2
    Last Post: 04-25-2008, 09:49 AM
  5. Using Prepared Statement
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-06-2008, 09:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •