clojure.java.jdbc update where id in

Question

I am trying to do a simple update statement on Oracle DB using clojure.java.jdbc.

(sql/db-do-prepared db "update table_name set active = 1 where id in (?)" ["one" "two"])

but I am getting:

java.sql.SQLException: Invalid column index

I am sure that all datatypes are correct. It looks like that kind of constructions are not permitted with clojure.java.jdbc. Any feedback is highly appreciated

Answer 1

For some reasons, it is quite hard to this with a prepared statement for most databases: there are security implications and the syntax for the different databases varies in this point.

I'm not entirely sure if this is feasible with the clojure jdbc package. For Oracle you could try to fall back to the oracle java jdbc package, create an ARRAY type instance and pass this as a single parameter. Official doc

Like @Timothy Pratley wrote, the most common solution is appending as many placeholder as elements you have. Some other alternatives have been discussed here