CODEWITHSUNDEEP
oracle-databasejdbcclojuresql-update
r00tt
r00tt

Reputation: 257

clojure.java.jdbc update where id in

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

Upvotes: 1

Views: 1086

Answers (4)

a.k
a.k

Reputation: 1203

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"]] {})

Upvotes: 1

Sean Corfield
Sean Corfield

Reputation: 6666

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.

Upvotes: 1

Timothy Pratley
Timothy Pratley

Reputation: 10662

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

Upvotes: 3

J. Dow
J. Dow

Reputation: 563

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

Upvotes: 1

Related Questions