EVOLUTION-MANAGER
Edit File: dbSendStatement.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 a data manipulation statement on a given database...</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 dbSendStatement {DBI}"><tr><td>dbSendStatement {DBI}</td><td style="text-align: right;">R Documentation</td></tr></table> <h2>Execute a data manipulation statement on a given database connection</h2> <h3>Description</h3> <p>The <code>dbSendStatement()</code> method only submits and synchronously executes the SQL data manipulation statement (e.g., <code>UPDATE</code>, <code>DELETE</code>, <code style="white-space: pre;">INSERT INTO</code>, <code style="white-space: pre;">DROP TABLE</code>, ...) to the database engine. To query the number of affected rows, call <code><a href="dbGetRowsAffected.html">dbGetRowsAffected()</a></code> on the returned result object. You must also call <code><a href="dbClearResult.html">dbClearResult()</a></code> after that. For interactive use, you should almost always prefer <code><a href="dbExecute.html">dbExecute()</a></code>. </p> <h3>Usage</h3> <pre> dbSendStatement(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><code><a href="dbSendStatement.html">dbSendStatement()</a></code> comes with a default implementation that simply forwards to <code><a href="dbSendQuery.html">dbSendQuery()</a></code>, to support backends that only implement the latter. </p> <h3>Value</h3> <p><code>dbSendStatement()</code> returns an S4 object that inherits from <a href="DBIResult-class.html">DBIResult</a>. The result set can be used with <code><a href="dbGetRowsAffected.html">dbGetRowsAffected()</a></code> to determine the number of rows affected by the query. Once you have finished using a result, make sure to clear it with <code><a href="dbClearResult.html">dbClearResult()</a></code>. An error is raised when issuing a statement over a closed or invalid connection, or if the statement is not a non-<code>NA</code> string. An error is also raised if the syntax of the query is invalid and all query parameters are given (by passing the <code>params</code> argument) or the <code>immediate</code> argument is set to <code>TRUE</code>. </p> <h3>Additional arguments</h3> <p>The following arguments are not part of the <code>dbSendStatement()</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>No warnings occur under normal conditions. When done, the DBIResult object must be cleared with a call to <code><a href="dbClearResult.html">dbClearResult()</a></code>. 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 <code>dbClearResult()</code>. </p> <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="dbExecute.html">dbExecute</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="dbWriteTable.html">dbWriteTable</a>()</code> </p> <h3>Examples</h3> <pre> 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) </pre> <hr /><div style="text-align: center;">[Package <em>DBI</em> version 1.1.0 <a href="00Index.html">Index</a>]</div> </body></html>