db transactions

[aarvid]

I am not sure what is the best way to do database transactions. For now I have added this to the generated db.lisp file:

(defmacro with-transaction (&body body)
  `(cl-dbi:with-transaction *connection*
     ,@body))

and exported it from myproject.db.

[blasut]

Can you give an example of how you use this?

[aarvid]

This example inserts a new row in table_a and updates a row in table_b with a reference to the new row. Currval only works within a transaction.

(with-connection (db)
  (with-transaction 
    (execute
     (insert-into :table_a
       (set= :data data)))
    (execute
     (update :table_b
       (set= :id_table_a (:currval "table_a_id_seq"))
       (where (:= :id id-table-b))))))

caveat: I have only been using caveman for less than a month. I searched but found no examples of transactions with caveman or even cl-dbi.