Fork me on GitHub
#sql
<
2021-03-08
>
Aviv Kotek16:03:52

usingĀ `next.jdbc`, how would it be possible to do batch-upsert (with column insertion), something like this: (this would work using java.jdbc)

(jdbc/execute! db ["INSERT INTO T(col1, col2) 
                    VALUES (?, ?)
                    ON DUPLICATE KEY UPDATE
                    col2=values(col2)"
                   [["c1" "c2"]
                    ["c3" "c4"]]....more here] {:multi? true})
I've seen the sql.builder/for-insert-multi unit but this still means I need some strings work

seancorfield16:03:48

@aviv next.jdbc/execute-batch! is what you're looking for I think. And you'd pass in that same string that you did for c.j.j. To build that string, HoneySQL is what you probably want (but ON DUPLICATE KEY is MySQL I believe and not supported directly yet, but there is an open issue for it).

Aviv Kotek17:03:38

observing the prepare_test.clj I see this applied only when sending a prepared-statement, is there any way to avoid that?

seancorfield17:03:10

Not at the moment, but you can easily create a PreparedStatement with next.jdbc/prepare so it's just a two-step process.

Aviv Kotek17:03:25

yep, thanks!

seancorfield17:03:49

Now that execute-batch! is in the next.jdbc namespace, I may well implement it for sourceable/connectable as well, but I can't do it directly via the protocols because extending them at this point would be a breaking change I suspect. I may ask opinions about that in #clojure...

Aviv Kotek17:03:44

great, btw- awesome work!! docs, tests..etc