Fork me on GitHub
#sql
<
2018-08-11
>
seancorfield00:08:19

If you don't use with-db-transaction, then each individual SQL statement is automatically wrapped in its own transaction (and therefore succeeds or fails on its own).

fabrao00:08:56

@seancorfield how to use it with Hikari-CP?

seancorfield00:08:37

Your db spec is just a map containing :datasource for that use case.

fabrao00:08:57

(j/with-db-connection [conn {:datasource (:db-pool sistema)}]
    (j/with-db-transaction [tx conn]
      (j/db-set-rollback-only! tx)
      (j/insert! tx :ven_pedido_vendedor {:cod_vendedor "1" :cod_cliente "000000" :cod_loja "01"})
      ))
?

fabrao00:08:00

Sorry, I didn´t know how to catch datasource

seancorfield00:08:28

You seem to be doing it in that code fragment...

seancorfield00:08:55

I assume (:db-pool sistema) is your HikariCP connectionpool object?

hiredman00:08:55

(j/db-set-rollback-only! tx) means your transaction will always rollback

seancorfield00:08:13

Yeah, I was about to point that out -- that insert! will always be rolled back.

fabrao00:08:33

ok, I´ll fix it

fabrao00:08:24

the test worked

seancorfield00:08:33

The connection pool is irrelevant for with-db-transaction and with-db-connection tho' -- they don't care, they just check if the db spec they are passed contains an active connection and if it doesn't they call get-connection on it and add that in.

seancorfield00:08:00

{:datasource (some-pool)} is just a regular db spec, just like {:dbtype "..." :dbname "..." ...}

fabrao00:08:14

oh, got it

fabrao00:08:49

thank you for your help

seancorfield00:08:52

the get/close operations are still called on both -- with the former, that gets a connection from the pool and returns it; with the latter, it opens a new connection and closes it.

fabrao00:08:23

I have to change my functions to receive the transaction

seancorfield00:08:39

The connection pool semantics are completely orthogonal to how java.jdbc works.

fabrao00:08:57

If I need manually rollback, I can

seancorfield00:08:33

But, yeah, if you want to execute a whole bunch of functions within the same transaction, you'll need with-db-transaction and then pass the t-conn binding into them (as a new db spec).

fabrao00:08:25

and besides, your trick about some-> worked too

fabrao00:08:50

I´m using this transaction inside some->

seancorfield00:08:14

Transactions are also somewhat orthogonal to everything else in java.jdbc -- if you pass a db spec with an open connection and an active transaction, that's what will be used; if you pass a db spec with an open connection and no transaction, each op will be done in a fresh transaction; if you pass a db spec with no connection, each op will be done in a fresh connection and in a fresh transaction.

seancorfield00:08:46

So the calling code controls how the operations behave.

seancorfield00:08:34

(but bear in mind java.jdbc doesn't nest transactions -- if you try to use transactions inside an active transaction, those nested transaction boundaries are ignored and everything happens inside a single, top-level transaction)

fabrao00:08:46

my problem is that I use with-db-connection in each crud operation

seancorfield00:08:11

You only need with-db-connection if you are planning to run multiple operations on a single connection.

fabrao00:08:19

so, I have one connect from poll in each sql operation

fabrao00:08:46

yeah, I have a connection poll

seancorfield00:08:19

Otherwise people will think you're polling the database -- making multiple queries to see if the state has changed 🙂

fabrao00:08:31

-> s/poll/pool/ -> LOL, regex master

seancorfield00:08:35

You shouldn't nest with-db-transaction / with-db-connection calls -- so having them inside low-level operations is a bad idea.

fabrao00:08:25

so, I intend to use rollback because some businnes rules can fail, and I don´t want to delete the inserts before

seancorfield00:08:03

I should probably make it safe to nest with-db-connection calls... I'll open a JIRA ticket.

fabrao00:08:28

in that case of using some->

(some-> <data> rule1 rule2 rule3)
, what is the best way to do this?

fabrao00:08:24

send one transaction to each rule, correct?

seancorfield00:08:01

https://dev.clojure.org/jira/browse/JDBC-171 to allow nested with-db-connection calls to be safe...

seancorfield03:08:07

The fix for this will be in 0.7.8 🙂

fabrao00:08:08

I´m thinking use partial to avoid using transaction in all funcation as parameter

seancorfield00:08:42

@fabrao what is <data>? how do your functions get passed the db-spec to operate on?

fabrao00:08:15

I use mount.core

seancorfield00:08:33

Ah, so you're relying on mutable global state?

fabrao00:08:05

Well, I thougth is was save doing this, correct?

seancorfield00:08:08

And each of your ruleN functions reaches out to that global state to get a db connection?

fabrao00:08:28

If I will use some-> I have to change the rule for using (defn rule1 [tx par1 par2] ...)

fabrao00:08:57

and doing with-db-transaction outside

fabrao00:08:03

I got the idea, I´ll change my crud functions to receive transaction

fabrao00:08:34

in one flow, one transaction

seancorfield00:08:31

Use some->> and then the "data" parameter will be the last argument and you can just do

(j/with-db-transaction [t-conn db-spec]
  (some->> <data>
    (rule1 t-conn)
    (rule2 t-conn param1)
    (rule3 t-conn)))

fabrao00:08:55

yes, that I thought

seancorfield00:08:31

(or else, to use some->, your "data" could always be first but then your rules will have signatures like [data tx par1 par2] which seems weird)

seancorfield00:08:05

Just be consistent 🙂

fabrao00:08:50

yes, I´ll change it to some->>