sqlite-transaction: SQLite transaction management

In RSQLite: SQLite Interface for R

SQLite transaction management

Description

By default, SQLite is in auto-commit mode. dbBegin() starts a SQLite transaction and turns auto-commit off. dbCommit() and dbRollback() commit and rollback the transaction, respectively and turn auto-commit on. DBI::dbWithTransaction() is a convenient wrapper that makes sure that dbCommit() or dbRollback() is called. A helper function sqliteIsTransacting() is available to check the current transaction status of the connection.

Usage

## S4 method for signature 'SQLiteConnection'
dbBegin(conn, .name = NULL, ..., name = NULL)

## S4 method for signature 'SQLiteConnection'
dbCommit(conn, .name = NULL, ..., name = NULL)

## S4 method for signature 'SQLiteConnection'
dbRollback(conn, .name = NULL, ..., name = NULL)

sqliteIsTransacting(conn)

Arguments

conn	a SQLiteConnection object, produced by DBI::dbConnect()
.name	For backward compatibility, do not use.
...	Needed for compatibility with generic. Otherwise ignored.
name	Supply a name to use a named savepoint. This allows you to nest multiple transaction

See Also

The corresponding generic functions DBI::dbBegin(), DBI::dbCommit(), and DBI::dbRollback().

Examples

library(DBI)
con <- dbConnect(SQLite(), ":memory:")
dbWriteTable(con, "arrests", datasets::USArrests)
dbGetQuery(con, "select count(*) from arrests")

dbBegin(con)
rs <- dbSendStatement(con, "DELETE from arrests WHERE Murder > 1")
dbGetRowsAffected(rs)
dbClearResult(rs)

dbGetQuery(con, "select count(*) from arrests")

dbRollback(con)
dbGetQuery(con, "select count(*) from arrests")[1, ]

dbBegin(con)
rs <- dbSendStatement(con, "DELETE FROM arrests WHERE Murder > 5")
dbClearResult(rs)
dbCommit(con)
dbGetQuery(con, "SELECT count(*) FROM arrests")[1, ]

# Named savepoints can be nested --------------------------------------------
dbBegin(con, name = "a")
dbBegin(con, name = "b")
sqliteIsTransacting(con)
dbRollback(con, name = "b")
dbCommit(con, name = "a")

dbDisconnect(con)

RSQLite documentation built on May 29, 2024, 3:30 a.m.