Execute a data manipulation statement on a given database connection
Source:R/dbSendStatement.R
dbSendStatement.Rd
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()
.
Methods in other packages
This documentation page describes the generics. Refer to the documentation pages linked below for the documentation for the methods that are implemented in various backend packages.
AzureKusto::dbSendStatement("AzureKustoConnection", "character")
DatabaseConnector::dbSendStatement("DatabaseConnectorConnection", "character")
sparklyr::dbSendStatement("spark_connection", "ANY")
Arguments
- conn
A DBIConnection object, as returned by
dbConnect()
.- statement
a character string containing SQL.
- ...
Other parameters passed on to methods.
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()
.
Details
dbSendStatement()
comes with a default implementation that simply
forwards to dbSendQuery()
, to support backends that only
implement the latter.
The command execution flow
This section gives a complete overview over the flow
for the execution of SQL statements that have side effects
such as stored procedures, inserting or deleting data,
or setting database or connection options.
Most of this flow, except repeated calling of dbBindArrow()
,
is implemented by dbExecute()
, which should be sufficient
for non-parameterized queries.
This flow requires an active connection established by dbConnect()
.
See also vignette("dbi-advanced")
for a walkthrough.
Use
dbSendStatement()
to create a result set object of class DBIResult. For some queries you need to passimmediate = TRUE
.Optionally, bind query parameters with
dbBind()
ordbBindArrow()
. This is required only if the query contains placeholders such as?
or$1
, depending on the database backend.Optionally, use
dbGetRowsAffected()
to retrieve the number of rows affected by the query.Repeat the last two steps as necessary.
Use
dbClearResult()
to clean up the result set object. This step is mandatory even if no rows have been fetched or if an error has occurred during the processing. It is good practice to useon.exit()
orwithr::defer()
to ensure that this step is always executed.
Failure modes
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:
DBI backend defaults to
immediate = TRUE
internallyA query without parameters is passed: query is executed
A query with parameters is passed:
params
not given: rejected immediately by the database because of a syntax error in the query, the backend triesimmediate = FALSE
(and gives a message)params
given: query is executed usingimmediate = FALSE
DBI backend defaults to
immediate = FALSE
internallyA query without parameters is passed:
simple query: query is executed
"special" query (such as setting a config options): fails, the backend tries
immediate = TRUE
(and gives a message)
A query with parameters is passed:
params
not given: waiting for parameters viadbBind()
params
given: query is executed
See also
For queries: dbSendQuery()
and dbGetQuery()
.
Other DBIConnection generics:
DBIConnection-class
,
dbAppendTable()
,
dbAppendTableArrow()
,
dbCreateTable()
,
dbCreateTableArrow()
,
dbDataType()
,
dbDisconnect()
,
dbExecute()
,
dbExistsTable()
,
dbGetException()
,
dbGetInfo()
,
dbGetQuery()
,
dbGetQueryArrow()
,
dbIsReadOnly()
,
dbIsValid()
,
dbListFields()
,
dbListObjects()
,
dbListResults()
,
dbListTables()
,
dbQuoteIdentifier()
,
dbReadTable()
,
dbReadTableArrow()
,
dbRemoveTable()
,
dbSendQuery()
,
dbSendQueryArrow()
,
dbUnquoteIdentifier()
,
dbWriteTable()
,
dbWriteTableArrow()
Other command execution generics:
dbBind()
,
dbClearResult()
,
dbExecute()
,
dbGetRowsAffected()
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)
#> [1] TRUE
dbGetRowsAffected(rs)
#> [1] 3
dbClearResult(rs)
dbReadTable(con, "cars") # there are now 6 rows
#> speed dist
#> 1 4 2
#> 2 4 10
#> 3 7 4
#> 4 1 1
#> 5 2 2
#> 6 3 3
# 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
#> speed dist
#> 1 4 2
#> 2 4 10
#> 3 7 4
#> 4 1 1
#> 5 2 2
#> 6 3 3
#> 7 4 5
#> 8 5 6
#> 9 6 7
#> 10 7 8
dbDisconnect(con)