sql 2021-07-05 | Slack Archive

Hi, I have a question for ‘next.jdbc.sql/insert’.

Can I use ‘var’ here by any chance? The query I want to use looks like this.

INSERT INTO test (id)
VALUES (@var := 1)
ON DUPLICATE KEY
    UPDATE id=(@var := id + 1);

emccue13:07:57

this feels like a sql question - if the insert helper doesn't do enough for you, just use execute!

SJ Shin16:07:15

I tried to solve it with insert helper, but in the end, I used ‘execute!’ as you said.

SJ Shin16:07:08

Thanks for your reply.

Timofey Sitnikov11:07:42

Good Morning Clojurians, I am trying to understand how to use next jdbc transactions. In the example code:

(jdbc/with-transaction [tx my-datasource]
  (jdbc/execute! tx ...)
  (jdbc/execute! tx ...)) ; will commit, unless exception thrown

I getting stuck on the my-datasorce argument. I tried to look through documentation and could not find what to use as an argument. Anyone has an example of how to use transaction?

indy11:07:56

my-datasource should either be a connection or a datasource (or your connection pool). If you want a datasource then you should call (jdbc/get-datasource db-spec). Where db-spec is a hash-map with entries listed https://github.com/seancorfield/next-jdbc/blob/develop/doc/all-the-options.md#datasources-and-connections.

indy11:07:00

db-spec is the config that describes how to connect to the database, details like username, password, host and so on.

Timofey Sitnikov11:07:53

@UMPJRJU9E, ok than what is tx ?

Timofey Sitnikov11:07:51

I thought the tx was the datasource since it is required by the jdbc/execute!

indy12:07:42

tx is just a binding, like your let bindings, the with-transaction is a macro that will create a transaction object from the supplied datasource/connection and bind it to tx. And this tx can be passed to the statements which you want to run inside that transaction.

indy12:07:25

Found some examples https://grep.app/search?q=with-transaction%20%5Btx

Timofey Sitnikov12:07:27

@UMPJRJU9E, ok, wow, never know about the http://grep.app. This is awesome!!!

seancorfield16:07:05

@U012GN57FJ9 with-transaction is like with-open in Clojure: it introduces a new local binding with either a new connection drawn from the specified datasource, or by starting a transaction on the specified connection (which is why it is recommended to use a datasource there).

seancorfield16:07:13

Like with-open, it "cleans up" the transaction at the end and closes the connection if it opened one.

seancorfield16:07:24

I'll try to make that clearer in the docs.

Timofey Sitnikov10:07:44

@U04V70XH6, thank you. If you could add a simple example of what to give to transaction it would be perfect. One example can be worth 2000 words.

2021-07-05

Channels