Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    4

    Unhappy Unanswered: need urgent help with PreparedStatement

    i use db2 as database, jdbc 2.0 driver and jsp.
    i have a prepared statement which looks like this :

    query = "select id, name from t_department where id in (?) order by name";

    i have a StringBuffer (selectedIDs) which consists of comma sepperated integers like :
    216, 220, 230, 323, 432

    it is not a string so the statement below gives an error :

    pst.setString(1, selectedIDs.toString());

    using a Statement instead would solve the problem :
    query = "select id, name from t_department where id in ("+selectedIDs.toString()+") order by name";

    but wouldn't that compile a new statement every time, because the id-list (the comma sepperated values) changes with every request.

    how can i solve this ?
    any help will be appreciated

    Mehmet Gunacti
    mserkan@gmx.net

  2. #2
    Join Date
    Jan 2004
    Posts
    10

    Re: need urgent help with PreparedStatement

    Originally posted by msggmx
    i use db2 as database, jdbc 2.0 driver and jsp.
    i have a prepared statement which looks like this :

    query = "select id, name from t_department where id in (?) order by name";

    i have a StringBuffer (selectedIDs) which consists of comma sepperated integers like :
    216, 220, 230, 323, 432

    it is not a string so the statement below gives an error :

    pst.setString(1, selectedIDs.toString());

    using a Statement instead would solve the problem :
    query = "select id, name from t_department where id in ("+selectedIDs.toString()+") order by name";

    but wouldn't that compile a new statement every time, because the id-list (the comma sepperated values) changes with every request.

    how can i solve this ?
    any help will be appreciated

    Mehmet Gunacti
    mserkan@gmx.net


    Hi,

    Can you post clearly, what sort of error you are getting.
    And the portion of the code where you are getting error.
    I can't asure you that i can solve your's.But I can try,bcoz i have worked with the similar kind of problem long back.

    With regards,
    sangeethk

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: need urgent help with PreparedStatement

    Originally posted by msggmx

    how can i solve this ?
    You don't necessarily need to solve it. The SQL compilation overhead is not that significant in many cases. However, if you insist on using PreparedStatement you could do this:

    If you know (or can limit) how many values can go into your IN () clause you could use as many parameter markers as you need (replacing some markers with the same values if there are less values than the maximum).

    Say, you expect the maximum of 5 ID values to be provided. You the would create a statement like this:

    SELECT ... WHERE ID IN (?, ?, ?, ?, ?) ...

    If all 5 values are provided for a certain invokation of the query you would call setInt() for each of them.

    If only 1 value is provided you still call setInt 5 times but use the same value every time.

  4. #4
    Join Date
    Jan 2004
    Posts
    3
    If we dont know the maximum number of ids thts is the maximum limit ........then wat can be the solution .....

  5. #5
    Join Date
    Jan 2004
    Posts
    11
    what n__i suggested (using multiple ?) is the only way out of it.
    i agree that using Statement will not cause too much of an overhead.

  6. #6
    Join Date
    Nov 2003
    Location
    Bangalore,India
    Posts
    51

    Re: need urgent help with PreparedStatement

    Dont do anything. Your code is fine... You can't have control over your parameters.
    Cheers
    Keep things simple as the world is already complex

  7. #7
    Join Date
    Oct 2002
    Posts
    37

    Smile about PreparedStatement

    i had sent this posting one year ago, and can't remember how i solved it, possibly using Statement.
    thanks to all of you.
    Mehmet

Posting Permissions

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