dbExecute | R Documentation |
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 freed by dbClearResult()
.
For passing query parameters, see dbBind()
, in particular
the "The command execution flow" section.
dbExecute(conn, statement, ...)
conn |
A DBIConnection object, as returned by
|
statement |
a character string containing SQL. |
... |
Other parameters passed on to methods. |
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,
or a data manipulation query that also returns a result set
such as INSERT INTO ... RETURNING ...
, use dbGetQuery()
instead.
dbExecute()
always returns a
scalar
numeric
that specifies the number of rows affected
by the statement.
Subclasses should override this method only if they provide some sort of performance optimization.
An error is raised when issuing a statement over a closed
or invalid connection,
if the syntax of the statement is invalid,
or if the statement is not a non-NA
string.
The following arguments are not part of the dbExecute()
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.
The param
argument allows passing query parameters, see dbBind()
for details.
immediate
argumentThe 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
internally
A 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 tries
immediate = FALSE
(and gives a message)
params
given: query is executed using immediate = FALSE
DBI backend defaults to immediate = FALSE
internally
A 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 via dbBind()
params
given: query is executed
For queries: dbSendQuery()
and dbGetQuery()
.
Other DBIConnection generics:
DBIConnection-class
,
dbAppendTable()
,
dbAppendTableArrow()
,
dbCreateTable()
,
dbCreateTableArrow()
,
dbDataType()
,
dbDisconnect()
,
dbExistsTable()
,
dbGetException()
,
dbGetInfo()
,
dbGetQuery()
,
dbGetQueryArrow()
,
dbIsReadOnly()
,
dbIsValid()
,
dbListFields()
,
dbListObjects()
,
dbListResults()
,
dbListTables()
,
dbQuoteIdentifier()
,
dbReadTable()
,
dbReadTableArrow()
,
dbRemoveTable()
,
dbSendQuery()
,
dbSendQueryArrow()
,
dbSendStatement()
,
dbUnquoteIdentifier()
,
dbWriteTable()
,
dbWriteTableArrow()
Other command execution generics:
dbBind()
,
dbClearResult()
,
dbGetRowsAffected()
,
dbSendStatement()
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 (?, ?)",
params = list(4:7, 5:8)
)
dbReadTable(con, "cars") # there are now 10 rows
dbDisconnect(con)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.