Clojurians Log v2

Hi, is there a way to use seqs as sql parameters? I’m trying to

(jdbc/execute! ds ["SELECT COUNT(*) FROM transactions WHERE to_account_id IN (?)"  [1 2]])

Execution error (NumberFormatException) at java.lang.NumberFormatException/forInputString (NumberFormatException.java:65).
For input string: "aced00057372001d636c6f6a7572652e6c616e672e50657273697374656e74566563746f72926bb181a556ad33020005490003636e7449000573686966744c00055f6d65746174001d4c636c6f6a7572652f6c616e672f4950657273697374656e744d61703b4c0004726f6f747400244c636c6f6a7572652f6c616e672f50657273697374656e74566563746f72244e6f64653b5b00047461696c7400135b4c6a6176612f6c616e672f4f626a6563743b7872001e636c6f6a7572652e6c616e672e4150657273697374656e74566563746f7240c68ede59abeb9b0200024900055f686173684900075f6861736865717870000000000000000000000002000000057073720022636c6f6a7572652e6c616e672e50657273697374656e74566563746f72244e6f6465893e0128132d606a0200015b0005617272617971007e00037870757200135b4c6a6176612e6c616e672e4f626a6563743b90ce589f1073296c02000078700000002070707070707070707070707070707070707070707070707070707070707070707571007e0008000000027372000e6a6176612e6c616e672e4c6f6e673b8be490cc8f23df0200014a000576616c7565787200106a6176612e6c616e672e4e756d62657286ac951d0b94e08b020000787000000000000000017371007e000b0000000000000002"

@be9 a) depends on the database you're using and b) have you read the Tips & Tricks section of the next.jdbc docs?

For most databases, you need to unroll the collection and have a ? for every element:

(let [coll [1 2]]
  (jdbc/execute! ds (into [(str "SELECT COUNT(*) FROM transaction WHERE to_account IN {" (str/join "," (repeat (count coll) "?")) ")"] coll)))

Another option is to use HoneySQL to build your SQL -- it knows how to deal with IN and collections (and does essentially the above code to generate the SQL string).

user=> (require '[honeysql.core :as sql])
nil
user=> (sql/format {:select [(sql/call :count :*)] :from [:transactions] :where [:in :to_account [1 2]]})
["SELECT count(*) FROM transactions WHERE (to_account in (?, ?))" 1 2]
user=>

@seancorfield I use H2

found this ANY(?) trick for postgres, but I will unroll, thx

Yeah, I'm not sure if ANY(?) works with any other database than PG.

What happens if you "nest" with-transaction?

so if I have a function that does a "find or create" and so I know that part needs to be in a transaction

but the outside scope might be doing a transaction of its own and I don't know

(defn find-or-create! [db participating-page-ids]
  (jdbc/with-transaction [transaction db]
    (or (find-by-participants transaction participating-page-ids)
        (create! transaction participating-page-ids))))

(jdbc/with-transaction [transaction db]
  (find-or-create! transaction [1 2 3])
  (other-stuff! transaction))

basically this

i know that it doesn't seem to crash immediately

but i don't know for sure what the behavior is

ANN: the babashka sqlite3 pod https://github.com/babashka/pod-babashka-sqlite3 This is a self-contained pod for interacting with sqlite3. It works well together with HoneySQL.

@emccue The answer is different between clojure.java.jdbc and next.jdbc

In c.j.j, nested TX are quietly ignored so only the TX in effect is the outermost one. In next.jdbc, the default is to go ahead and actually try to stand up the nested TX anyway -- assuming you "know what you're doing" -- but there is a dynamic var you can bind to either ignore nested TX (like c.j.j) or prohibit nested TX (attempts will throw an exception).

See https://cljdoc.org/d/seancorfield/next.jdbc/1.1.613/api/next.jdbc.transaction#*nested-tx* (this behavior is otherwise undocumented I think).

(well, it is mentioned briefly in the change log for version 1.1.547)

when you say "you know what you are doing"

where would I go to learn what i should be doing

and what are the footguns here

because performance aside i would guess that a nested transaction would be maybe less performant but probably do the "correct" thing with regards to function boundaries

On most databases, if you attempt to set up two TX on the same connection, you'll get start TX1 ..(a).. start TX2 ..(b).. end TX2 (which will commit both (a) and (b)) ..©.. end TX1 (will commit © if it wasn't already auto-committed after the "end" of TX2).

That's why c.j.j silently ignored the start/end of TX2 in that case (but you would only then get all of (a), (b), and (c) committed or all rolled back).

It's because TX aren't really a "thing" on their own: they are just settings on a connection. By default, connections are auto-commit (every SQL operation is committed as it is performed). Setting up a TX puts the connection into manual commit mode and then "ending" that TX commits the changes (since the last commit) or rolls back the changes (to the last commit), and also changes the connection back to auto-commit. So any SQL operations done on that connection after a TX "ends" are auto-committed again.

That doesn't matter if your TX are fairly short-lived but it starts to matter if you start a TX and then call a bunch of Clojure functions that might also want to start a TX.

Where people tend to run into this is test fixtures: it can seem like a good idea for a test fixture to run the test inside a TX and explicitly roll it back at the end. And that will seem to work in "most" cases. But if the test itself tries to do TX stuff, you'll shoot yourself in the foot (probably). Also, DDL is often non-transactional (depends on the DB) so if your test does any DDL (as opposed to regular SQL) those operations won't be rolled back anyway and you can also get in a mess.

The TL;DR is really to use save points instead of TX if you think you might want more granular commit/rollback.

And then on some DBs, you actually can nested TX 😐

(I'm not fully aware of how they do that though... none of the DBs I have worked with allow nested TX, but it was something folks complained about with c.j.j)

Does that help @emccue?

Yeah, so basically I probably just want to set the old default for my app

If you were using c.j.j and "just happened" to have what look like nested TX and the behavior "worked" for you, then binding the *nested-tx* to :ignore should give you the same (wrong) behavior 🙂

For test fixtures, where you run the test inside a rollback-only TX, specifying :ignore will at least ensure none of the TX inside your test will do anything -- modulo the DDL caveat above on DBs where DDL is auto-committed regardless of the connection state.

(this is why I use a scratch DB for testing and don't try to run tests inside a rollback-only TX -- and it's also why I only use TX very, very occasionally, in very small blocks of code where I specifically want a narrow commit/rollback scope)