EVOLUTION-MANAGER
Edit File: dbExecute.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><title>R: Execute an update statement, query number of rows affected,...</title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <link rel="stylesheet" type="text/css" href="R.css" /> </head><body> <table width="100%" summary="page for dbExecute {DBI}"><tr><td>dbExecute {DBI}</td><td style="text-align: right;">R Documentation</td></tr></table> <h2>Execute an update statement, query number of rows affected, and then close result set</h2> <h3>Description</h3> <p>Executes a statement and returns the number of rows affected. <code>dbExecute()</code> comes with a default implementation (which should work with most backends) that calls <code><a href="dbSendStatement.html">dbSendStatement()</a></code>, then <code><a href="dbGetRowsAffected.html">dbGetRowsAffected()</a></code>, ensuring that the result is always free-d by <code><a href="dbClearResult.html">dbClearResult()</a></code>. </p> <h3>Usage</h3> <pre> dbExecute(conn, statement, ...) </pre> <h3>Arguments</h3> <table summary="R argblock"> <tr valign="top"><td><code>conn</code></td> <td> <p>A <a href="DBIConnection-class.html">DBIConnection</a> object, as returned by <code><a href="dbConnect.html">dbConnect()</a></code>.</p> </td></tr> <tr valign="top"><td><code>statement</code></td> <td> <p>a character string containing SQL.</p> </td></tr> <tr valign="top"><td><code>...</code></td> <td> <p>Other parameters passed on to methods.</p> </td></tr> </table> <h3>Details</h3> <p>You can also use <code>dbExecute()</code> 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 <code><a href="dbGetQuery.html">dbGetQuery()</a></code> instead. </p> <h3>Value</h3> <p><code>dbExecute()</code> always returns a scalar numeric that specifies the number of rows affected by the statement. 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-<code>NA</code> string. </p> <h3>Implementation notes</h3> <p>Subclasses should override this method only if they provide some sort of performance optimization. </p> <h3>Additional arguments</h3> <p>The following arguments are not part of the <code>dbExecute()</code> generic (to improve compatibility across backends) but are part of the DBI specification: </p> <ul> <li> <p><code>params</code> (default: <code>NULL</code>) </p> </li> <li> <p><code>immediate</code> (default: <code>NULL</code>) </p> </li></ul> <p>They must be provided as named arguments. See the "Specification" sections for details on their usage. </p> <h3>Specification</h3> <p>The <code>param</code> argument allows passing query parameters, see <code><a href="dbBind.html">dbBind()</a></code> for details. </p> <h3>Specification for the <code>immediate</code> argument</h3> <p>The <code>immediate</code> argument supports distinguishing between "direct" and "prepared" APIs offered by many database drivers. Passing <code>immediate = TRUE</code> leads to immediate execution of the query or statement, via the "direct" API (if supported by the driver). The default <code>NULL</code> 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 <code>immediate</code> argument. Examples for possible behaviors: </p> <ol> <li><p> DBI backend defaults to <code>immediate = TRUE</code> internally </p> <ol> <li><p> A query without parameters is passed: query is executed </p> </li> <li><p> A query with parameters is passed: </p> <ol> <li> <p><code>params</code> not given: rejected immediately by the database because of a syntax error in the query, the backend tries <code>immediate = FALSE</code> (and gives a message) </p> </li> <li> <p><code>params</code> given: query is executed using <code>immediate = FALSE</code> </p> </li></ol> </li></ol> </li> <li><p> DBI backend defaults to <code>immediate = FALSE</code> internally </p> <ol> <li><p> A query without parameters is passed: </p> <ol> <li><p> simple query: query is executed </p> </li> <li><p> "special" query (such as setting a config options): fails, the backend tries <code>immediate = TRUE</code> (and gives a message) </p> </li></ol> </li> <li><p> A query with parameters is passed: </p> <ol> <li> <p><code>params</code> not given: waiting for parameters via <code><a href="dbBind.html">dbBind()</a></code> </p> </li> <li> <p><code>params</code> given: query is executed </p> </li></ol> </li></ol> </li></ol> <h3>See Also</h3> <p>For queries: <code><a href="dbSendQuery.html">dbSendQuery()</a></code> and <code><a href="dbGetQuery.html">dbGetQuery()</a></code>. </p> <p>Other DBIConnection generics: <code><a href="DBIConnection-class.html">DBIConnection-class</a></code>, <code><a href="dbAppendTable.html">dbAppendTable</a>()</code>, <code><a href="dbCreateTable.html">dbCreateTable</a>()</code>, <code><a href="dbDataType.html">dbDataType</a>()</code>, <code><a href="dbDisconnect.html">dbDisconnect</a>()</code>, <code><a href="dbExistsTable.html">dbExistsTable</a>()</code>, <code><a href="dbGetException.html">dbGetException</a>()</code>, <code><a href="dbGetInfo.html">dbGetInfo</a>()</code>, <code><a href="dbGetQuery.html">dbGetQuery</a>()</code>, <code><a href="dbIsReadOnly.html">dbIsReadOnly</a>()</code>, <code><a href="dbIsValid.html">dbIsValid</a>()</code>, <code><a href="dbListFields.html">dbListFields</a>()</code>, <code><a href="dbListObjects.html">dbListObjects</a>()</code>, <code><a href="dbListResults.html">dbListResults</a>()</code>, <code><a href="dbListTables.html">dbListTables</a>()</code>, <code><a href="dbReadTable.html">dbReadTable</a>()</code>, <code><a href="dbRemoveTable.html">dbRemoveTable</a>()</code>, <code><a href="dbSendQuery.html">dbSendQuery</a>()</code>, <code><a href="dbSendStatement.html">dbSendStatement</a>()</code>, <code><a href="dbWriteTable.html">dbWriteTable</a>()</code> </p> <h3>Examples</h3> <pre> 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) </pre> <hr /><div style="text-align: center;">[Package <em>DBI</em> version 1.1.0 <a href="00Index.html">Index</a>]</div> </body></html>