Become an expert in R — Interactive courses, Cheat Sheets, certificates and more!
Get Started for Free

dbSendStatement

Execute a data manipulation statement on a given database connection


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

Usage

dbSendStatement(conn, statement, ...)

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.

Value

dbSendStatement() returns an S4 object that inherits from DBIResult. The result set can be used with dbGetRowsAffected() to determine the number of rows affected by the query. Once you have finished using a result, make sure to clear it with dbClearResult(). An error is raised when issuing a statement over a closed or invalid connection, or if the statement is not a non-NA string. An error is also raised if the syntax of the query is invalid and all query parameters are given (by passing the params argument) or the immediate argument is set to TRUE.

Additional arguments

The following arguments are not part of the dbSendStatement() generic (to improve compatibility across backends) but are part of the DBI specification:

  • params (default: NULL)

  • immediate (default: NULL)

They must be provided as named arguments. See the "Specification" sections for details on their usage.

Specification

No warnings occur under normal conditions. When done, the DBIResult object must be cleared with a call to dbClearResult(). Failure to clear the result set leads to a warning when the connection is closed. If the backend supports only one open result set per connection, issuing a second query invalidates an already open result set and raises a warning. The newly opened result set is valid and must be cleared with dbClearResult().

The param argument allows passing query parameters, see dbBind() for details.

Specification for the immediate argument

The immediate argument supports distinguishing between "direct" and "prepared" APIs offered by many database drivers. Passing immediate = TRUE leads to immediate execution of the query or statement, via the "direct" API (if supported by the driver). The default NULL means that the backend should choose whatever API makes the most sense for the database, and (if relevant) tries the other API if the first attempt fails. A successful second attempt should result in a message that suggests passing the correct immediate argument. Examples for possible behaviors:

  1. DBI backend defaults to immediate = TRUE internally

    1. A query without parameters is passed: query is executed

    2. A query with parameters is passed:

      1. params not given: rejected immediately by the database because of a syntax error in the query, the backend tries immediate = FALSE (and gives a message)

      2. params given: query is executed using immediate = FALSE

  2. DBI backend defaults to immediate = FALSE internally

    1. A query without parameters is passed:

      1. simple query: query is executed

      2. "special" query (such as setting a config options): fails, the backend tries immediate = TRUE (and gives a message)

    2. A query with parameters is passed:

      1. params not given: waiting for parameters via dbBind()

      2. params given: query is executed

See Also

For queries: dbSendQuery() and dbGetQuery().

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 (?, ?)",
  params = 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)

DBI

R Database Interface

v1.1.1
LGPL (>= 2.1)
Authors
R Special Interest Group on Databases (R-SIG-DB) [aut], Hadley Wickham [aut], Kirill Müller [aut, cre] (<https://orcid.org/0000-0002-1416-3412>), R Consortium [fnd]
Initial release
2021-01-04

We don't support your browser anymore

Please choose more modern alternatives, such as Google Chrome or Mozilla Firefox.