Hi all, I was wondering if anyone has elegant solutions in(or come across) handling IN clauses in prepared statements. Specifically I have this type of query sql = "SELECT myname,mycity FROM mytable WHERE mycity IN (?,?)" In here my (?,?) list is determined at runtime. Thanks in advance. PJ
I doubt there's any point in using a prepared Statement for this. You would have to generate the sql statement (i.e. the number of commas in (,,,...)) at runtime as well, so you might as well use a normal statement. I don't know whether it's database specific or much about it at all, but there is a setArray() function on preparedStatement - I was thinking somehow it might be useful here?
Adam
I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
Why not just put a single comma in the prepared statement that will represent the entire list of items to be includind in the IN clause. Before you update the prepare generate a list as a using a StringBuffer and then use a toString() method on it when you pass it to the prepared statement for substitution.
the query executed usually translates to "select * from table where id in ( 'value1, value2, value3' )" which doesn't work the way you intended. The next logical attempt would be to try to set the ? to "value1', 'value2', 'value3" but the single quotes are usually escaped and the result is "select * from table where id in ( 'value1'', ''value2'', ''value3' )" which won't work as you intended either. The last remark about this is that PreparedStatements are used to optimize recurring SQL where only the values of variables change, not the number of values. You'd probably lose any value added performance of PreparedStatements on the database side even if you found a work around. Use a Statement, create the SQL string dynamically, run your own ' escape sequence, then execute the statement. I use the word 'usually' in the context that each implementation is different and you may find a way to 'fool' your driver, but probably losing any performance gains on the database side. Jamie [ July 12, 2002: Message edited by: Jamie Robertson ]
Originally posted by Adam Hardy: ... but there is a setArray() function on preparedStatement - I was thinking somehow it might be useful here?
Adam
The setArray() method is used for setting the PreparedStatement parameter to a value that maps to a column of type SQL ARRAY. Not what we want here [ July 12, 2002: Message edited by: Jamie Robertson ]
I agree. Here's the link: http://ej-technologies/jprofiler - if it wasn't for jprofiler, we would need to
run our stuff on 16 servers instead of 3.
0
