Fork me on GitHub
#sql
<
2019-02-09
>
leontalbot21:02:27

Using Sean’s et al. clojure.java.jdbc, what is the best way to update multiple rows, and receive back number of updates (count) or better, all updated row ids ?

leontalbot21:02:16

yet, if one update fails, catch it, and keep other entries updated

seancorfield21:02:44

@leontalbot What do you mean by "update fails"? Throws an exception?

seancorfield21:02:10

If you issue a single update SQL statement that should update multiple rows, and the database finds one or more row updates fail, that whole statement will fail and nothing will be updated. Because it's a single SQL statement.

seancorfield21:02:32

That's nothing to do with libraries like clojure.java.jdbc -- it happens lower down.

seancorfield21:02:23

If you want to have some rows updated and some rows not, you'll need to issue multiple SQL statements.

leontalbot21:02:02

ok, say you have this

(defn update-item!
  [item-id item-attrs]
  (try
    (sql/update! db-spec
                 :items
                 item-attrs
                 ["id = ?" id]
                 {:return-keys true})
    (catch Exception e (.getMessage e))))

(reduce (fn [ids {id :id}]
          (conj ids (:id (update-item! id {:color "white" :price 10}))))
        []
        items)

leontalbot21:02:31

Is that bad?

leontalbot21:02:35

1. not safe 2. not idiomatic

leontalbot21:02:08

the where clause could be more complex in the example

leontalbot21:02:19

this reduce would get me the updated item ids

seancorfield21:02:29

You will get an exception trying to do (:id "Exception message")

seancorfield21:02:15

What do you mean by "not safe"?

leontalbot21:02:16

I should print error message and just return nil)

leontalbot21:02:46

a side effect in a reduce (?)

seancorfield21:02:10

reduce is eager. Side-effects are okay.

👍 5
seancorfield22:02:19

Is :id the primary key of the table?

seancorfield22:02:32

Trying to figure out why you are returning keys from an update.

seancorfield22:02:24

It doesn't look like :id would be changed by the update? So the result will be the same :id value you passed in, right?

seancorfield22:02:00

But you want to know which updates succeeded and which failed and you're after the :id for that, yes?

leontalbot22:02:43

to tell the truth, sometimes i may not have the id, but the where clause will match one

leontalbot22:02:58

so ideally i would return-keys and get the id from there

leontalbot22:02:15

(or do a select prior to the update)

seancorfield22:02:21

I would probably write that as (into [] (map #(update-item! % attributes)) items) especially if you're likely to need a more complex pipeline of operations.

seancorfield22:02:35

Then you'd have a vector of hash maps for success and strings for failures (I'd probably just return e from the catch BTW, rather than only the message, so callers have more information available to work with).

seancorfield22:02:50

So a vector of hash maps or exception objects.

seancorfield22:02:44

I think that's clearer these days than a reduce with conj onto [] -- even tho' it does the same under the hood.

leontalbot22:02:59

that makes sense! thank you @seancorfield 🙂