EVOLUTION-MANAGER
Edit File: dbSendQuery.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 query on a given database connection</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 dbSendQuery {DBI}"><tr><td>dbSendQuery {DBI}</td><td style="text-align: right;">R Documentation</td></tr></table> <h2>Execute a query on a given database connection</h2> <h3>Description</h3> <p>The <code>dbSendQuery()</code> method only submits and synchronously executes the SQL query to the database engine. It does <em>not</em> extract any records — for that you need to use the <code><a href="dbFetch.html">dbFetch()</a></code> method, and then you must call <code><a href="dbClearResult.html">dbClearResult()</a></code> when you finish fetching the records you need. For interactive use, you should almost always prefer <code><a href="dbGetQuery.html">dbGetQuery()</a></code>. </p> <h3>Usage</h3> <pre> dbSendQuery(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>This method is for <code>SELECT</code> queries only. Some backends may support data manipulation queries through this method for compatibility reasons. However, callers are strongly encouraged to use <code><a href="dbSendStatement.html">dbSendStatement()</a></code> for data manipulation statements. </p> <p>The query is submitted to the database server and the DBMS executes it, possibly generating vast amounts of data. Where these data live is driver-specific: some drivers may choose to leave the output on the server and transfer them piecemeal to R, others may transfer all the data to the client – but not necessarily to the memory that R manages. See individual drivers' <code>dbSendQuery()</code> documentation for details. </p> <h3>Value</h3> <p><code>dbSendQuery()</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="dbFetch.html">dbFetch()</a></code> to extract records. 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 query over a closed or invalid connection, or if the query 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>dbSendQuery()</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. </p> <p>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 updates: <code><a href="dbSendStatement.html">dbSendStatement()</a></code> and <code><a href="dbExecute.html">dbExecute()</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="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, "mtcars", mtcars) rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4") dbFetch(rs) dbClearResult(rs) # Pass one set of values with the param argument: rs <- dbSendQuery( con, "SELECT * FROM mtcars WHERE cyl = ?", params = list(4L) ) dbFetch(rs) dbClearResult(rs) # Pass multiple sets of values with dbBind(): rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = ?") dbBind(rs, list(6L)) dbFetch(rs) dbBind(rs, list(8L)) dbFetch(rs) dbClearResult(rs) 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>