Discussions

EJB programming & troubleshooting: How to use IN clause from a prepared statement

  1. How to use IN clause from a prepared statement (4 messages)

    This looks like it should be obvious, but I can't get it to work for the life of me. I want to execute a prepared statement that has an in clause that accepts an array of objects.

    For example:
    PreparedStatement pStmt = conn.prepareStatement("SELECT name FROM user WHERE user_id IN (?)");
    Integer users[] = new Integer[]{new Integer(1), new Integer(2)};
    pStmt.setObject(1, users, Types.INTEGER);
    pStmt.executeQuery();

    The error I get back is "Failed to convert object". I've begun looking at the Types.ARRAY objects, but that seems to be propriatary to the database vendor and is geared toward a different purpose. I've also tried
     pStmt.setObject(1,"1,2", Types.VARCHAR);
    but that got a similar error.

    I'm using Oracle 8i so if I had to go with a oracle specific solution I will, but I'd rather keep it cross platform.

    Again, this seems like it should have an obvious solution but I can't seem to find it.
  2. Hai

       Why don't you pur your values directly instead of using parameters.

    JDBC cannot support that. Because without knowing the number of parameters you are trying to put in in clause. How can it assume the parameters?

    Better just give direct query without putting parameters.

    Kiran
  3. Interesting. I've used prepared statements quite a while but I did not know you could insert an array. I'm going to try it myself.

    If nothing else works, I would build the last part of your sql statement (question marks) with a loop and fill the prepared statement with a loop as well. Not very elegant but it will work.
  4. you might as well just use a normal statement object.

    any normal speed increases a preparedstatement might give disappear anyway since you have a variable number of parameters.
  5. "any normal speed increases a preparedstatement might give disappear anyway since you have a variable number of parameters"


    Understood, but that's not the reason I use PreparedStatements. I like PSs because I don't have to wrap strings in quotes or do any other sort of validation of my parameters. Just set them and I'm done.