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

Timothy Pratley · Answer 1 · 2016-04-29T00:32:38.130

3

You need in (?, ?) ... that is you need the same number of question marks as things to look for.... (str/join ", " (repeat n "?")) and you need the params to not be a vector:

(sql/execute! db
  ["update table_name set active = 1 where id in (?, ?)"
   "one" "two"])

Note that you can put the sql and arguments together in a vector

edited Apr 29 '16 at 00:32

answered Apr 28 '16 at 20:41

Timothy Pratley

10,586
3
34
63

I tried that before asking question :) I am getting: `ava.sql.SQLException: SQL statement to execute cannot be empty or null` – r00tt Apr 28 '16 at 20:47
This is the correct answer. You might post your code again to deal with the particular exception you are seeing. Also, there are many libraries that will help you with this kind of issue: [HugSQL](http://www.hugsql.org), [Honey SQL](https://github.com/jkk/honeysql) – Curtis Summers Apr 29 '16 at 13:47

score 1 · Accepted Answer · edited May 23 '17 at 10:28

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

score 1 · Answer 3 · answered May 29 '18 at 16:55

In the general case of wanting to run SQL that has an in clause in the where condition, you'll want something like this:

(sql/query db-spec (into [(str "SELECT ... WHERE col IN ("
                               (str/join "," (repeat (count values) "?"))
                               ")")]
                         values))

String concatenation is ugly sometimes. As Curtis Summers noted in a comment on Timothy Pratley's answer, using something like HoneySQL can make this a lot less unpleasant.

a.k · Answer 4 · 2021-11-17T18:11:43.937

as Curtis mentioned, achieving this with HoneySQL+clojure.java.jdbc:

(require '[clojure.java.jdbc :as j])

(def example-q
  (-> (select :*)
      (from [:table :t])
      (where [:in :param :?param])))

(sql/format example-q {:params {:param ["a" "b" "c"]}})
=> ["SELECT * FROM table AS t WHERE param IN (?, ?, ?)" "a" "b" "c"]
;; then running:
(j/query db (sql/format example-q {:params {:param ["a" "b" "c"]}}))

with next.jdbc, i'd use execute-batch:

(require '[next.jdbc :as jdbc])

(def update-q
  "UPDATE t
   SET col=0
   WHERE id
   IN (?)")

(jdbc/execute-batch! ds update-q [["1"] ["2"]] {})

clojure.java.jdbc update where id in

4 Answers4