Abstract

The DBI package defines the generic DataBase Interface for R. The connection to individual DBMS is provided by other packages that import DBI (so-called DBI backends). This document formalizes the behavior expected by the methods declared in DBI and implemented by the individual backends. To ensure maximum portability and exchangeability, and to reduce the effort for implementing a new DBI backend, the DBItest package defines a comprehensive set of test cases that test conformance to the DBI specification. This document is derived from comments in the test definitions of the DBItest package. Any extensions or updates to the tests will be reflected in this document.

DBI: R Database Interface

DBI defines an interface for communication between R and relational database management systems. All classes in this package are virtual and need to be extended by the various R/DBMS implementations (so-called DBI backends).

Examples

RSQLite::SQLite()

Determine the SQL data type of an object

This section describes the behavior of the following method:

dbDataType(dbObj, obj, ...)

Description

Returns an SQL string that describes the SQL data type to be used for an object. The default implementation of this generic determines the SQL type of an R object according to the SQL 92 specification, which may serve as a starting point for driver implementations. DBI also provides an implementation for data.frame which will return a character vector giving the type for each column in the dataframe.

Arguments

dbObj

A object inheriting from DBIDriver or DBIConnection

obj

An R object whose SQL type we want to determine.

Other arguments passed on to methods.

Details

The data types supported by databases are different than the data types in R, but the mapping between the primitive types is straightforward:

  • Any of the many fixed and varying length character types are mapped to character vectors

  • Fixed-precision (non-IEEE) numbers are mapped into either numeric or integer vectors.

Notice that many DBMS do not follow IEEE arithmetic, so there are potential problems with under/overflows and loss of precision.

Examples

dbDataType(ANSI(), 1:5)
dbDataType(ANSI(), 1)
dbDataType(ANSI(), TRUE)
dbDataType(ANSI(), Sys.Date())
dbDataType(ANSI(), Sys.time())
dbDataType(ANSI(), Sys.time() - as.POSIXct(Sys.Date()))
dbDataType(ANSI(), c("x", "abc"))
dbDataType(ANSI(), list(raw(10), raw(20)))
dbDataType(ANSI(), I(3))

dbDataType(ANSI(), iris)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbDataType(con, 1:5)
dbDataType(con, 1)
dbDataType(con, TRUE)
dbDataType(con, Sys.Date())
dbDataType(con, Sys.time())
dbDataType(con, Sys.time() - as.POSIXct(Sys.Date()))
dbDataType(con, c("x", "abc"))
dbDataType(con, list(raw(10), raw(20)))
dbDataType(con, I(3))

dbDataType(con, iris)

dbDisconnect(con)

Create a connection to a DBMS

This section describes the behavior of the following method:

dbConnect(drv, ...)

Description

Connect to a DBMS going through the appropriate authentication procedure. Some implementations may allow you to have multiple connections open, so you may invoke this function repeatedly assigning its output to different objects. The authentication mechanism is left unspecified, so check the documentation of individual drivers for details. Use dbCanConnect() to check if a connection can be established.

Arguments

drv

an object that inherits from DBIDriver, or an existing DBIConnection object (in order to clone an existing connection).

authentication arguments needed by the DBMS instance; these typically include user, password, host, port, dbname, etc. For details see the appropriate DBIDriver.

Examples

# SQLite only needs a path to the database. (Here, ":memory:" is a special
# path that creates an in-memory database.) Other database drivers
# will require more details (like user, password, host, port, etc.)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
con

dbListTables(con)

dbDisconnect(con)

Disconnect (close) a connection

This section describes the behavior of the following method:

dbDisconnect(conn, ...)

Description

This closes the connection, discards all pending work, and frees resources (e.g., memory, sockets).

Arguments

conn

A DBIConnection object, as returned by dbConnect().

Other parameters passed on to methods.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbDisconnect(con)

Execute a query on a given database connection

This section describes the behavior of the following method:

dbSendQuery(conn, statement, ...)

Description

The dbSendQuery() method only submits and synchronously executes the SQL query to the database engine. It does not extract any records — for that you need to use the dbFetch() method, and then you must call dbClearResult() when you finish fetching the records you need. For interactive use, you should almost always prefer dbGetQuery().

Arguments

conn

A DBIConnection object, as returned by dbConnect().

statement

a character string containing SQL.

Other parameters passed on to methods.

Details

This method is for SELECT queries only. Some backends may support data manipulation queries through this method for compatibility reasons. However, callers are strongly encouraged to use dbSendStatement() for data manipulation statements.

The query is submitted to the database server and the DBMS executes it, possibly generating vast amounts of data. Where these data live is driver-specific: some drivers may choose to leave the output on the server and transfer them piecemeal to R, others may transfer all the data to the client – but not necessarily to the memory that R manages. See individual drivers’ dbSendQuery() documentation for details.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)
rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(rs)
dbClearResult(rs)

# Pass one set of values with the param argument:
rs <- dbSendQuery(
  con,
  "SELECT * FROM mtcars WHERE cyl = ?",
  param = list(4L)
)
dbFetch(rs)
dbClearResult(rs)

# Pass multiple sets of values with dbBind():
rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = ?")
dbBind(rs, list(6L))
dbFetch(rs)
dbBind(rs, list(8L))
dbFetch(rs)
dbClearResult(rs)

dbDisconnect(con)

Fetch records from a previously executed query

This section describes the behavior of the following methods:

dbFetch(res, n = -1, ...)

fetch(res, n = -1, ...)

Description

Fetch the next n elements (rows) from the result set and return them as a data.frame.

Arguments

res

An object inheriting from DBIResult, created by dbSendQuery().

n

maximum number of records to retrieve per fetch. Use n = -1 or n = Inf to retrieve all pending records. Some implementations may recognize other special values.

Other arguments passed on to methods.

Details

fetch() is provided for compatibility with older DBI clients - for all new code you are strongly encouraged to use dbFetch(). The default implementation for dbFetch() calls fetch() so that it is compatible with existing code. Modern backends should implement for dbFetch() only.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)

# Fetch all results
rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(rs)
dbClearResult(rs)

# Fetch in chunks
rs <- dbSendQuery(con, "SELECT * FROM mtcars")
while (!dbHasCompleted(rs)) {
  chunk <- dbFetch(rs, 10)
  print(nrow(chunk))
}

dbClearResult(rs)
dbDisconnect(con)

Clear a result set

This section describes the behavior of the following method:

dbClearResult(res, ...)

Description

Frees all resources (local and remote) associated with a result set. In some cases (e.g., very large result sets) this can be a critical step to avoid exhausting resources (memory, file descriptors, etc.)

Arguments

res

An object inheriting from DBIResult.

Other arguments passed on to methods.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

rs <- dbSendQuery(con, "SELECT 1")
print(dbFetch(rs))

dbClearResult(rs)
dbDisconnect(con)

Bind values to a parameterized/prepared statement

This section describes the behavior of the following method:

dbBind(res, params, ...)

Description

For parametrized or prepared statements, the dbSendQuery() and dbSendStatement() functions can be called with statements that contain placeholders for values. The dbBind() function binds these placeholders to actual values, and is intended to be called on the result set before calling dbFetch() or dbGetRowsAffected().

Arguments

res

An object inheriting from DBIResult.

params

A list of bindings, named or unnamed.

Other arguments passed on to methods.

Details

DBI supports parametrized (or prepared) queries and statements via the dbBind() generic. Parametrized queries are different from normal queries in that they allow an arbitrary number of placeholders, which are later substituted by actual values. Parametrized queries (and statements) serve two purposes:

  • The same query can be executed more than once with different values. The DBMS may cache intermediate information for the query, such as the execution plan, and execute it faster.

  • Separation of query syntax and parameters protects against SQL injection.

The placeholder format is currently not specified by DBI; in the future, a uniform placeholder syntax may be supported. Consult the backend documentation for the supported formats. For automated testing, backend authors specify the placeholder syntax with the placeholder_pattern tweak. Known examples are:

  • ? (positional matching in order of appearance) in RMySQL and RSQLite

  • $1 (positional matching by index) in RPostgres and RSQLite

  • :name and $name (named matching) in RSQLite

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "iris", iris)

# Using the same query for different values
iris_result <- dbSendQuery(con, "SELECT * FROM iris WHERE [Petal.Width] > ?")
dbBind(iris_result, list(2.3))
dbFetch(iris_result)
dbBind(iris_result, list(3))
dbFetch(iris_result)
dbClearResult(iris_result)

# Executing the same statement with different values at once
iris_result <- dbSendStatement(con, "DELETE FROM iris WHERE [Species] = $species")
dbBind(iris_result, list(species = c("setosa", "versicolor", "unknown")))
dbGetRowsAffected(iris_result)
dbClearResult(iris_result)

nrow(dbReadTable(con, "iris"))

dbDisconnect(con)

Send query, retrieve results and then clear result set

This section describes the behavior of the following method:

dbGetQuery(conn, statement, ...)

Description

Returns the result of a query as a data frame. dbGetQuery() comes with a default implementation (which should work with most backends) that calls dbSendQuery(), then dbFetch(), ensuring that the result is always free-d by dbClearResult().

Arguments

conn

A DBIConnection object, as returned by dbConnect().

statement

a character string containing SQL.

Other parameters passed on to methods.

Details

This method is for SELECT queries only (incl. other SQL statements that return a SELECT-alike result, e. g. execution of a stored procedure).

To execute a stored procedure that does not return a result set, use dbExecute().

Some backends may support data manipulation statements through this method for compatibility reasons. However, callers are strongly advised to use dbExecute() for data manipulation statements.

Implementation notes

Subclasses should override this method only if they provide some sort of performance optimization.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)
dbGetQuery(con, "SELECT * FROM mtcars")
dbGetQuery(con, "SELECT * FROM mtcars", n = 6)

# Pass values using the param argument:
# (This query runs eight times, once for each different
# parameter. The resulting rows are combined into a single
# data frame.)
dbGetQuery(con, "SELECT COUNT(*) FROM mtcars WHERE cyl = ?", param = list(1:8))

dbDisconnect(con)

Execute a data manipulation statement on a given database connection

This section describes the behavior of the following method:

dbSendStatement(conn, statement, ...)

Description

The dbSendStatement() method only submits and synchronously executes the SQL data manipulation statement (e.g., UPDATE, DELETE, INSERT INTO, DROP TABLE, …) to the database engine. To query the number of affected rows, call dbGetRowsAffected() on the returned result object. You must also call dbClearResult() after that. For interactive use, you should almost always prefer dbExecute().

Arguments

conn

A DBIConnection object, as returned by dbConnect().

statement

a character string containing SQL.

Other parameters passed on to methods.

Details

dbSendStatement() comes with a default implementation that simply forwards to dbSendQuery(), to support backends that only implement the latter.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "cars", head(cars, 3))

rs <- dbSendStatement(
  con,
  "INSERT INTO cars (speed, dist) VALUES (1, 1), (2, 2), (3, 3)"
)
dbHasCompleted(rs)
dbGetRowsAffected(rs)
dbClearResult(rs)
dbReadTable(con, "cars")   # there are now 6 rows

# Pass one set of values directly using the param argument:
rs <- dbSendStatement(
  con,
  "INSERT INTO cars (speed, dist) VALUES (?, ?)",
  param = list(4L, 5L)
)
dbClearResult(rs)

# Pass multiple sets of values using dbBind():
rs <- dbSendStatement(
  con,
  "INSERT INTO cars (speed, dist) VALUES (?, ?)"
)
dbBind(rs, list(5:6, 6:7))
dbBind(rs, list(7L, 8L))
dbClearResult(rs)
dbReadTable(con, "cars")   # there are now 10 rows

dbDisconnect(con)

Execute an update statement, query number of rows affected, and then close result set

This section describes the behavior of the following method:

dbExecute(conn, statement, ...)

Description

Executes a statement and returns the number of rows affected. dbExecute() comes with a default implementation (which should work with most backends) that calls dbSendStatement(), then dbGetRowsAffected(), ensuring that the result is always free-d by dbClearResult().

Arguments

conn

A DBIConnection object, as returned by dbConnect().

statement

a character string containing SQL.

Other parameters passed on to methods.

Details

You can also use dbExecute() to call a stored procedure that performs data manipulation or other actions that do not return a result set. To execute a stored procedure that returns a result set use dbGetQuery() instead.

Implementation notes

Subclasses should override this method only if they provide some sort of performance optimization.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "cars", head(cars, 3))
dbReadTable(con, "cars")   # there are 3 rows
dbExecute(
  con,
  "INSERT INTO cars (speed, dist) VALUES (1, 1), (2, 2), (3, 3)"
)
dbReadTable(con, "cars")   # there are now 6 rows

# Pass values using the param argument:
dbExecute(
  con,
  "INSERT INTO cars (speed, dist) VALUES (?, ?)",
  param = list(4:7, 5:8)
)
dbReadTable(con, "cars")   # there are now 10 rows

dbDisconnect(con)

Quote literal strings

This section describes the behavior of the following method:

dbQuoteString(conn, x, ...)

Description

Call this method to generate a string that is suitable for use in a query as a string literal, to make sure that you generate valid SQL and protect against SQL injection attacks.

Arguments

conn

A subclass of DBIConnection, representing an active connection to an DBMS.

x

A character vector to quote as string.

Other arguments passed on to methods.

Examples

# Quoting ensures that arbitrary input is safe for use in a query
name <- "Robert'); DROP TABLE Students;--"
dbQuoteString(ANSI(), name)

# NAs become NULL
dbQuoteString(ANSI(), c("x", NA))

# SQL vectors are always passed through as is
var_name <- SQL("select")
var_name
dbQuoteString(ANSI(), var_name)

# This mechanism is used to prevent double escaping
dbQuoteString(ANSI(), dbQuoteString(ANSI(), name))

Quote identifiers

This section describes the behavior of the following method:

dbQuoteIdentifier(conn, x, ...)

Description

Call this method to generate a string that is suitable for use in a query as a column or table name, to make sure that you generate valid SQL and protect against SQL injection attacks. The inverse operation is dbUnquoteIdentifier().

Arguments

conn

A subclass of DBIConnection, representing an active connection to an DBMS.

x

A character vector, SQL or Id object to quote as identifier.

Other arguments passed on to methods.

Examples

# Quoting ensures that arbitrary input is safe for use in a query
name <- "Robert'); DROP TABLE Students;--"
dbQuoteIdentifier(ANSI(), name)

# SQL vectors are always passed through as is
var_name <- SQL("select")
var_name

dbQuoteIdentifier(ANSI(), var_name)

# This mechanism is used to prevent double escaping
dbQuoteIdentifier(ANSI(), dbQuoteIdentifier(ANSI(), name))

Copy data frames from database tables

This section describes the behavior of the following method:

dbReadTable(conn, name, ...)

Description

Reads a database table to a data frame, optionally converting a column to row names and converting the column names to valid R identifiers.

Arguments

conn

A DBIConnection object, as returned by dbConnect().

name

A character string specifying the unquoted DBMS table name, or the result of a call to dbQuoteIdentifier().

Other parameters passed on to methods.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars[1:10, ])
dbReadTable(con, "mtcars")

dbDisconnect(con)

Copy data frames to database tables

This section describes the behavior of the following method:

dbWriteTable(conn, name, value, ...)

Description

Writes, overwrites or appends a data frame to a database table, optionally converting row names to a column and specifying SQL data types for fields. New code should prefer dbCreateTable() and dbAppendTable().

Arguments

conn

A DBIConnection object, as returned by dbConnect().

name

A character string specifying the unquoted DBMS table name, or the result of a call to dbQuoteIdentifier().

value

a data.frame (or coercible to data.frame).

Other parameters passed on to methods.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars[1:5, ])
dbReadTable(con, "mtcars")

dbWriteTable(con, "mtcars", mtcars[6:10, ], append = TRUE)
dbReadTable(con, "mtcars")

dbWriteTable(con, "mtcars", mtcars[1:10, ], overwrite = TRUE)
dbReadTable(con, "mtcars")

# No row names
dbWriteTable(con, "mtcars", mtcars[1:10, ], overwrite = TRUE, row.names = FALSE)
dbReadTable(con, "mtcars")

List remote tables

This section describes the behavior of the following method:

dbListTables(conn, ...)

Description

Returns the unquoted names of remote tables accessible through this connection. This should include views and temporary objects, but not all database backends (in particular RMariaDB and RMySQL) support this.

Arguments

conn

A DBIConnection object, as returned by dbConnect().

Other parameters passed on to methods.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbListTables(con)
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)

dbDisconnect(con)

Does a table exist?

This section describes the behavior of the following method:

dbExistsTable(conn, name, ...)

Description

Returns if a table given by name exists in the database.

Arguments

conn

A DBIConnection object, as returned by dbConnect().

name

A character string specifying a DBMS table name.

Other parameters passed on to methods.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbExistsTable(con, "iris")
dbWriteTable(con, "iris", iris)
dbExistsTable(con, "iris")

dbDisconnect(con)

Remove a table from the database

This section describes the behavior of the following method:

dbRemoveTable(conn, name, ...)

Description

Remove a remote table (e.g., created by dbWriteTable()) from the database.

Arguments

conn

A DBIConnection object, as returned by dbConnect().

name

A character string specifying a DBMS table name.

Other parameters passed on to methods.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbExistsTable(con, "iris")
dbWriteTable(con, "iris", iris)
dbExistsTable(con, "iris")
dbRemoveTable(con, "iris")
dbExistsTable(con, "iris")

dbDisconnect(con)

List field names of a remote table

This section describes the behavior of the following method:

dbListFields(conn, name, ...)

Description

List field names of a remote table

Arguments

conn

A DBIConnection object, as returned by dbConnect().

name

a character string with the name of the remote table.

Other parameters passed on to methods.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)
dbListFields(con, "mtcars")

dbDisconnect(con)

Is this DBMS object still valid?

This section describes the behavior of the following method:

dbIsValid(dbObj, ...)

Description

This generic tests whether a database object is still valid (i.e. it hasn’t been disconnected or cleared).

Arguments

dbObj

An object inheriting from DBIObject, i.e. DBIDriver, DBIConnection, or a DBIResult

Other arguments to methods.

Examples

dbIsValid(RSQLite::SQLite())

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbIsValid(con)

rs <- dbSendQuery(con, "SELECT 1")
dbIsValid(rs)

dbClearResult(rs)
dbIsValid(rs)

dbDisconnect(con)
dbIsValid(con)

Completion status

This section describes the behavior of the following method:

dbHasCompleted(res, ...)

Description

This method returns if the operation has completed. A SELECT query is completed if all rows have been fetched. A data manipulation statement is always completed.

Arguments

res

An object inheriting from DBIResult.

Other arguments passed on to methods.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)
rs <- dbSendQuery(con, "SELECT * FROM mtcars")

dbHasCompleted(rs)
ret1 <- dbFetch(rs, 10)
dbHasCompleted(rs)
ret2 <- dbFetch(rs)
dbHasCompleted(rs)

dbClearResult(rs)
dbDisconnect(con)

Get the statement associated with a result set

This section describes the behavior of the following method:

dbGetStatement(res, ...)

Description

Returns the statement that was passed to dbSendQuery() or dbSendStatement().

Arguments

res

An object inheriting from DBIResult.

Other arguments passed on to methods.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)
rs <- dbSendQuery(con, "SELECT * FROM mtcars")
dbGetStatement(rs)

dbClearResult(rs)
dbDisconnect(con)

The number of rows fetched so far

This section describes the behavior of the following method:

dbGetRowCount(res, ...)

Description

Returns the total number of rows actually fetched with calls to dbFetch() for this result set.

Arguments

res

An object inheriting from DBIResult.

Other arguments passed on to methods.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)
rs <- dbSendQuery(con, "SELECT * FROM mtcars")

dbGetRowCount(rs)
ret1 <- dbFetch(rs, 10)
dbGetRowCount(rs)
ret2 <- dbFetch(rs)
dbGetRowCount(rs)
nrow(ret1) + nrow(ret2)

dbClearResult(rs)
dbDisconnect(con)

The number of rows affected

This section describes the behavior of the following method:

dbGetRowsAffected(res, ...)

Description

This method returns the number of rows that were added, deleted, or updated by a data manipulation statement.

Arguments

res

An object inheriting from DBIResult.

Other arguments passed on to methods.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)
rs <- dbSendStatement(con, "DELETE FROM mtcars")
dbGetRowsAffected(rs)
nrow(mtcars)

dbClearResult(rs)
dbDisconnect(con)

Information about result types

This section describes the behavior of the following method:

dbColumnInfo(res, ...)

Description

Produces a data.frame that describes the output of a query. The data.frame should have as many rows as there are output fields in the result set, and each column in the data.frame describes an aspect of the result set field (field name, type, etc.)

Arguments

res

An object inheriting from DBIResult.

Other arguments passed on to methods.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

rs <- dbSendQuery(con, "SELECT 1 AS a, 2 AS b")
dbColumnInfo(rs)
dbFetch(rs)

dbClearResult(rs)
dbDisconnect(con)

Begin/commit/rollback SQL transactions

This section describes the behavior of the following methods:

dbBegin(conn, ...)

dbCommit(conn, ...)

dbRollback(conn, ...)

Description

A transaction encapsulates several SQL statements in an atomic unit. It is initiated with dbBegin() and either made persistent with dbCommit() or undone with dbRollback(). In any case, the DBMS guarantees that either all or none of the statements have a permanent effect. This helps ensuring consistency of write operations to multiple tables.

Arguments

conn

A DBIConnection object, as returned by dbConnect().

Other parameters passed on to methods.

Details

Not all database engines implement transaction management, in which case these methods should not be implemented for the specific DBIConnection subclass.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "cash", data.frame(amount = 100))
dbWriteTable(con, "account", data.frame(amount = 2000))

# All operations are carried out as logical unit:
dbBegin(con)
withdrawal <- 300
dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))
dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))
dbCommit(con)

dbReadTable(con, "cash")
dbReadTable(con, "account")

# Rolling back after detecting negative value on account:
dbBegin(con)
withdrawal <- 5000
dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))
dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))
if (dbReadTable(con, "account")$amount >= 0) {
  dbCommit(con)
} else {
  dbRollback(con)
}

dbReadTable(con, "cash")
dbReadTable(con, "account")

dbDisconnect(con)

Self-contained SQL transactions

This section describes the behavior of the following methods:

dbWithTransaction(conn, code, ...)

dbBreak()

Description

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().

Arguments

conn

A DBIConnection object, as returned by dbConnect().

code

An arbitrary block of R code.

Other parameters passed on to methods.

Details

DBI implements dbWithTransaction(), backends should need to override this generic only if they implement specialized handling.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "cash", data.frame(amount = 100))
dbWriteTable(con, "account", data.frame(amount = 2000))

# All operations are carried out as logical unit:
dbWithTransaction(
  con,
  {
    withdrawal <- 300
    dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))
    dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))
  }
)

# The code is executed as if in the curent environment:
withdrawal

# The changes are committed to the database after successful execution:
dbReadTable(con, "cash")
dbReadTable(con, "account")

# Rolling back with dbBreak():
dbWithTransaction(
  con,
  {
    withdrawal <- 5000
    dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))
    dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))
    if (dbReadTable(con, "account")$amount < 0) {
      dbBreak()
    }
  }
)

# These changes were not committed to the database:
dbReadTable(con, "cash")
dbReadTable(con, "account")

dbDisconnect(con)