dbWithTransaction {DBI} | R Documentation |
Given that transactions are implemented, this function
allows you to pass in code that is run in a transaction.
The default method of dbWithTransaction
calls dbBegin
before executing the code,
and dbCommit
after successful completion,
or dbRollback
in case of an error.
The advantage is
that you don't have to remember to do dbBegin
and dbCommit
or
dbRollback
– that is all taken care of.
The special function dbBreak
allows an early exit with rollback,
it can be called only inside dbWithTransaction
.
dbWithTransaction(conn, code) dbBreak()
conn |
A |
code |
An arbitrary block of R code |
The result of the evaluation of code
The transaction in code
on the connection conn
is committed
or rolled back. The code
chunk may also modify the local R
environment.
con <- dbConnect(RSQLite::SQLite(), ":memory:") dbWriteTable(con, "cars", head(cars, 3)) dbReadTable(con, "cars") # there are 3 rows ## successful transaction dbWithTransaction(con, { dbExecute(con, "INSERT INTO cars (speed, dist) VALUES (1, 1);") dbExecute(con, "INSERT INTO cars (speed, dist) VALUES (2, 2);") dbExecute(con, "INSERT INTO cars (speed, dist) VALUES (3, 3);") }) dbReadTable(con, "cars") # there are now 6 rows ## failed transaction -- note the missing comma tryCatch( dbWithTransaction(con, { dbExecute(con, "INSERT INTO cars (speed, dist) VALUES (1, 1);") dbExecute(con, "INSERT INTO cars (speed dist) VALUES (2, 2);") dbExecute(con, "INSERT INTO cars (speed, dist) VALUES (3, 3);") }), error = identity ) dbReadTable(con, "cars") # still 6 rows ## early exit, silently dbWithTransaction(con, { dbExecute(con, "INSERT INTO cars (speed, dist) VALUES (1, 1);") dbExecute(con, "INSERT INTO cars (speed, dist) VALUES (2, 2);") if (nrow(dbReadTable(con, "cars")) > 7) dbBreak() dbExecute(con, "INSERT INTO cars (speed, dist) VALUES (3, 3);") }) dbReadTable(con, "cars") # still 6 rows dbDisconnect(con)