Fork me on GitHub
#sql
<
2020-06-03
>
snurppa07:06:48

I’m refactoring from clojure.java.jdbc to next.jdbc and i had “truncate” utility using db-do-commands to issue sequence of TRUNCATE TABLE statements. db-do-command seems to use (.addBatch stmt cmd) and then execute them as batch. Now going through next.jdbc docs and eventually source, for now the closest I’ve found is p/execute-batch!, but it seems to assume the batch contains prepared statements with params.. I tried TRUNCATE TABLE ? and gave seq of tables, but it seems it’s not possible to use ? in place of table name. I don’t know if it’s that I’m noob, but looking from my problems perspective, the p/execute-batch! looks too opinionated because it uses set-parameters What would be the next.jdbc way to do this? Manually use addBatch to add the plain TRUNCATE strings and then do .executeBatch ? The migration guide mentions that execute! could replace most uses of db-do-commands, but I can’t get my head around about how refactor this (rather simple) case 😄

snurppa08:06:42

I guess with something like (run! #(next/execute! conn [%]) [..command-strs…]) I can get forward, but I’m pleased to hear other suggestions too!

seancorfield16:06:24

@snurppa Is this just a single TRUNCATE TABLE foo command? If so, just use execute-one! (or execute!) as the docs tell you in the Getting Started guide.

snurppa16:06:59

Yes, but lots of them at once. Like 20 truncations 😊

snurppa16:06:18

That's why I had db-do-commands before, as it batched them. But idk if it matters, I guess I could do those in simple doseq as well? Just looking for the idiomatic way to do it in next.jdbc as I'm learning it 😊 And thanks for your efforts with lib and everything in the community @U04V70XH6!

seancorfield16:06:09

If you have multiple commands, (run! #(jdbc/execute-one! conn [%]) ["list" "of" "commands"])) seems reasonable to me.

👍 4
seancorfield16:06:15

If you really want them executed in a batch, you'd need to use Statement, not PreparedStatement, so something like this would work:

(defn do-commands [conn & cmds]
  (let [stmt (prep/statement conn)]
    (run! #(.addBatch stmt %) cmds)
    (.executeBatch stmt)))
That's not too bad in terms of interop.

seancorfield16:06:23

(untested, but I think that should work)

seancorfield16:06:27

Yeah, that seems to work:

user=> (require '[next.jdbc.prepare :as prep])
nil
user=> (require '[next.jdbc :as jdbc])
nil
user=> (defn do-commands [conn & cmds]
  (let [stmt (prep/statement conn)]
    (run! #(.addBatch stmt %) cmds)
    (.executeBatch stmt)))
#'user/do-commands
user=> (with-open [conn (jdbc/get-connection db-spec)]
  (do-commands conn "TRUNCATE user" "TRUNCATE status" "TRUNCATE transaction"))
#object["[I" 0x7b48b933 "[I@7b48b933"]
That's a Java int array but it's just going to contain three zeros so its value isn't important ^ @snurppa

seancorfield16:06:38

I'll open a ticket to add that but I don't think do-db-commands was very widely used... so I may just add that code to the migration guide.

snurppa16:06:30

Yeah, simple enough to do a such a wrapper. Thanks, will look into this tomorrow!

seancorfield16:06:22

I think the (prep/statement) call should use with-open rather than let but haven't investigated that. It was just a quick example.