Clojurians Log v2
sql
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 😄
I guess with something like (run! #(next/execute! conn [%]) [..command-strs…]) I can get forward, but I’m pleased to hear other suggestions too!
@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.
Yes, but lots of them at once. Like 20 truncations 😊
If you have multiple commands, (run! #(jdbc/execute-one! conn [%]) ["list" "of" "commands"])) seems reasonable to me.
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 @seancorfield!
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)))(untested, but I think that should work)
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"]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.
Yeah, simple enough to do a such a wrapper. Thanks, will look into this tomorrow!
I think the (prep/statement) call should use with-open rather than let but haven't investigated that. It was just a quick example.