File: spec.html

package info (click to toggle)
dbi 1.2.3-1
links: PTS, VCS
area: main
in suites: forky, sid, trixie
size: 3,004 kB
sloc: makefile: 2
file content (3743 lines) | stat: -rw-r--r-- 196,470 bytes
<!DOCTYPE html>

<html>

<head>

<meta charset="utf-8" />
<meta name="generator" content="pandoc" />
<meta http-equiv="X-UA-Compatible" content="IE=EDGE" />

<meta name="viewport" content="width=device-width, initial-scale=1" />

<meta name="author" content="Kirill Müller" />


<title>DBI specification</title>

<script>// Pandoc 2.9 adds attributes on both header and div. We remove the former (to
// be compatible with the behavior of Pandoc < 2.8).
document.addEventListener('DOMContentLoaded', function(e) {
  var hs = document.querySelectorAll("div.section[class*='level'] > :first-child");
  var i, h, a;
  for (i = 0; i < hs.length; i++) {
    h = hs[i];
    if (!/^h[1-6]$/i.test(h.tagName)) continue;  // it should be a header h1-h6
    a = h.attributes;
    while (a.length > 0) h.removeAttribute(a[0].name);
  }
});
</script>

<style type="text/css">
code{white-space: pre-wrap;}
span.smallcaps{font-variant: small-caps;}
span.underline{text-decoration: underline;}
div.column{display: inline-block; vertical-align: top; width: 50%;}
div.hanging-indent{margin-left: 1.5em; text-indent: -1.5em;}
ul.task-list{list-style: none;}
</style>



<style type="text/css">
code {
white-space: pre;
}
.sourceCode {
overflow: visible;
}
</style>
<style type="text/css" data-origin="pandoc">
pre > code.sourceCode { white-space: pre; position: relative; }
pre > code.sourceCode > span { line-height: 1.25; }
pre > code.sourceCode > span:empty { height: 1.2em; }
.sourceCode { overflow: visible; }
code.sourceCode > span { color: inherit; text-decoration: inherit; }
div.sourceCode { margin: 1em 0; }
pre.sourceCode { margin: 0; }
@media screen {
div.sourceCode { overflow: auto; }
}
@media print {
pre > code.sourceCode { white-space: pre-wrap; }
pre > code.sourceCode > span { text-indent: -5em; padding-left: 5em; }
}
pre.numberSource code
{ counter-reset: source-line 0; }
pre.numberSource code > span
{ position: relative; left: -4em; counter-increment: source-line; }
pre.numberSource code > span > a:first-child::before
{ content: counter(source-line);
position: relative; left: -1em; text-align: right; vertical-align: baseline;
border: none; display: inline-block;
-webkit-touch-callout: none; -webkit-user-select: none;
-khtml-user-select: none; -moz-user-select: none;
-ms-user-select: none; user-select: none;
padding: 0 4px; width: 4em;
color: #aaaaaa;
}
pre.numberSource { margin-left: 3em; border-left: 1px solid #aaaaaa; padding-left: 4px; }
div.sourceCode
{ }
@media screen {
pre > code.sourceCode > span > a:first-child::before { text-decoration: underline; }
}
code span.al { color: #ff0000; font-weight: bold; } 
code span.an { color: #60a0b0; font-weight: bold; font-style: italic; } 
code span.at { color: #7d9029; } 
code span.bn { color: #40a070; } 
code span.bu { color: #008000; } 
code span.cf { color: #007020; font-weight: bold; } 
code span.ch { color: #4070a0; } 
code span.cn { color: #880000; } 
code span.co { color: #60a0b0; font-style: italic; } 
code span.cv { color: #60a0b0; font-weight: bold; font-style: italic; } 
code span.do { color: #ba2121; font-style: italic; } 
code span.dt { color: #902000; } 
code span.dv { color: #40a070; } 
code span.er { color: #ff0000; font-weight: bold; } 
code span.ex { } 
code span.fl { color: #40a070; } 
code span.fu { color: #06287e; } 
code span.im { color: #008000; font-weight: bold; } 
code span.in { color: #60a0b0; font-weight: bold; font-style: italic; } 
code span.kw { color: #007020; font-weight: bold; } 
code span.op { color: #666666; } 
code span.ot { color: #007020; } 
code span.pp { color: #bc7a00; } 
code span.sc { color: #4070a0; } 
code span.ss { color: #bb6688; } 
code span.st { color: #4070a0; } 
code span.va { color: #19177c; } 
code span.vs { color: #4070a0; } 
code span.wa { color: #60a0b0; font-weight: bold; font-style: italic; } 
</style>
<script>
// apply pandoc div.sourceCode style to pre.sourceCode instead
(function() {
  var sheets = document.styleSheets;
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].ownerNode.dataset["origin"] !== "pandoc") continue;
    try { var rules = sheets[i].cssRules; } catch (e) { continue; }
    var j = 0;
    while (j < rules.length) {
      var rule = rules[j];
      // check if there is a div.sourceCode rule
      if (rule.type !== rule.STYLE_RULE || rule.selectorText !== "div.sourceCode") {
        j++;
        continue;
      }
      var style = rule.style.cssText;
      // check if color or background-color is set
      if (rule.style.color === '' && rule.style.backgroundColor === '') {
        j++;
        continue;
      }
      // replace div.sourceCode by a pre.sourceCode rule
      sheets[i].deleteRule(j);
      sheets[i].insertRule('pre.sourceCode{' + style + '}', j);
    }
  }
})();
</script>

<style type="text/css">
p.abstract{
text-align: center;
font-weight: bold;
}
div.abstract{
margin: auto;
width: 90%;
}
</style>



<style type="text/css">body {
background-color: #fff;
margin: 1em auto;
max-width: 700px;
overflow: visible;
padding-left: 2em;
padding-right: 2em;
font-family: "Open Sans", "Helvetica Neue", Helvetica, Arial, sans-serif;
font-size: 14px;
line-height: 1.35;
}
#TOC {
clear: both;
margin: 0 0 10px 10px;
padding: 4px;
width: 400px;
border: 1px solid #CCCCCC;
border-radius: 5px;
background-color: #f6f6f6;
font-size: 13px;
line-height: 1.3;
}
#TOC .toctitle {
font-weight: bold;
font-size: 15px;
margin-left: 5px;
}
#TOC ul {
padding-left: 40px;
margin-left: -1.5em;
margin-top: 5px;
margin-bottom: 5px;
}
#TOC ul ul {
margin-left: -2em;
}
#TOC li {
line-height: 16px;
}
table {
margin: 1em auto;
border-width: 1px;
border-color: #DDDDDD;
border-style: outset;
border-collapse: collapse;
}
table th {
border-width: 2px;
padding: 5px;
border-style: inset;
}
table td {
border-width: 1px;
border-style: inset;
line-height: 18px;
padding: 5px 5px;
}
table, table th, table td {
border-left-style: none;
border-right-style: none;
}
table thead, table tr.even {
background-color: #f7f7f7;
}
p {
margin: 0.5em 0;
}
blockquote {
background-color: #f6f6f6;
padding: 0.25em 0.75em;
}
hr {
border-style: solid;
border: none;
border-top: 1px solid #777;
margin: 28px 0;
}
dl {
margin-left: 0;
}
dl dd {
margin-bottom: 13px;
margin-left: 13px;
}
dl dt {
font-weight: bold;
}
ul {
margin-top: 0;
}
ul li {
list-style: circle outside;
}
ul ul {
margin-bottom: 0;
}
pre, code {
background-color: #f7f7f7;
border-radius: 3px;
color: #333;
white-space: pre-wrap; 
}
pre {
border-radius: 3px;
margin: 5px 0px 10px 0px;
padding: 10px;
}
pre:not([class]) {
background-color: #f7f7f7;
}
code {
font-family: Consolas, Monaco, 'Courier New', monospace;
font-size: 85%;
}
p > code, li > code {
padding: 2px 0px;
}
div.figure {
text-align: center;
}
img {
background-color: #FFFFFF;
padding: 2px;
border: 1px solid #DDDDDD;
border-radius: 3px;
border: 1px solid #CCCCCC;
margin: 0 5px;
}
h1 {
margin-top: 0;
font-size: 35px;
line-height: 40px;
}
h2 {
border-bottom: 4px solid #f7f7f7;
padding-top: 10px;
padding-bottom: 2px;
font-size: 145%;
}
h3 {
border-bottom: 2px solid #f7f7f7;
padding-top: 10px;
font-size: 120%;
}
h4 {
border-bottom: 1px solid #f7f7f7;
margin-left: 8px;
font-size: 105%;
}
h5, h6 {
border-bottom: 1px solid #ccc;
font-size: 105%;
}
a {
color: #0033dd;
text-decoration: none;
}
a:hover {
color: #6666ff; }
a:visited {
color: #800080; }
a:visited:hover {
color: #BB00BB; }
a[href^="http:"] {
text-decoration: underline; }
a[href^="https:"] {
text-decoration: underline; }

code > span.kw { color: #555; font-weight: bold; } 
code > span.dt { color: #902000; } 
code > span.dv { color: #40a070; } 
code > span.bn { color: #d14; } 
code > span.fl { color: #d14; } 
code > span.ch { color: #d14; } 
code > span.st { color: #d14; } 
code > span.co { color: #888888; font-style: italic; } 
code > span.ot { color: #007020; } 
code > span.al { color: #ff0000; font-weight: bold; } 
code > span.fu { color: #900; font-weight: bold; } 
code > span.er { color: #a61717; background-color: #e3d2d2; } 
</style>




</head>

<body>




<h1 class="title toc-ignore">DBI specification</h1>
<h4 class="author">Kirill Müller</h4>
<div class="abstract">
<p class="abstract">Abstract</p>
<p>The DBI package defines the generic DataBase Interface for R. The
connection to individual DBMS is provided by other packages that import
DBI (so-called <em>DBI backends</em>). This document formalizes the
behavior expected by the methods declared in DBI and implemented by the
individual backends. To ensure maximum portability and exchangeability,
and to reduce the effort for implementing a new DBI backend, the DBItest
package defines a comprehensive set of test cases that test conformance
to the DBI specification. This document is derived from comments in the
test definitions of the DBItest package. Any extensions or updates to
the tests will be reflected in this document.</p>
</div>



<div id="dbi-r-database-interface" class="section level2">
<h2>DBI: R Database Interface</h2>
<p>DBI defines an interface for communication between R and relational
database management systems. All classes in this package are virtual and
need to be extended by the various R/DBMS implementations (so-called
<em>DBI backends</em>).</p>
<div id="definition" class="section level3">
<h3>Definition</h3>
<p>A DBI backend is an R package which imports the <span class="pkg">DBI</span> and <span class="pkg">methods</span> packages.
For better or worse, the names of many existing backends start with ‘R’,
e.g., <span class="pkg">RSQLite</span>, <span class="pkg">RMySQL</span>,
<span class="pkg">RSQLServer</span>; it is up to the backend author to
adopt this convention or not.</p>
</div>
<div id="dbi-classes-and-methods" class="section level3">
<h3>DBI classes and methods</h3>
<p>A backend defines three classes, which are subclasses of DBIDriver,
DBIConnection, and DBIResult. The backend provides implementation for
all methods of these base classes that are defined but not implemented
by DBI. All methods defined in <span class="pkg">DBI</span> are
reexported (so that the package can be used without having to attach
<span class="pkg">DBI</span>), and have an ellipsis <code>...</code> in
their formals for extensibility.</p>
</div>
<div id="construction-of-the-dbidriver-object" class="section level3">
<h3>Construction of the DBIDriver object</h3>
<p>The backend must support creation of an instance of its DBIDriver
subclass with a <dfn>constructor function</dfn>. By default, its name is
the package name without the leading ‘R’ (if it exists), e.g.,
<code>SQLite</code> for the <span class="pkg">RSQLite</span> package.
However, backend authors may choose a different name. The constructor
must be exported, and it must be a function that is callable without
arguments. DBI recommends to define a constructor with an empty argument
list.</p>
</div>
<div id="examples" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb1"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb1-1"><a href="#cb1-1" tabindex="-1"></a>RSQLite<span class="sc">::</span><span class="fu">SQLite</span>()</span></code></pre></div>
</div>
</div>
<div id="determine-the-sql-data-type-of-an-object" class="section level2">
<h2>Determine the SQL data type of an object</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb2"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb2-1"><a href="#cb2-1" tabindex="-1"></a><span class="fu">dbDataType</span>(dbObj, obj, ...)</span></code></pre></div>
<div id="description" class="section level3">
<h3>Description</h3>
<p>Returns an SQL string that describes the SQL data type to be used for
an object. The default implementation of this generic determines the SQL
type of an R object according to the SQL 92 specification, which may
serve as a starting point for driver implementations. DBI also provides
an implementation for data.frame which will return a character vector
giving the type for each column in the dataframe.</p>
</div>
<div id="arguments" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>dbObj</code></td>
<td>A object inheriting from DBIDriver or DBIConnection</td>
</tr>
<tr class="even">
<td><code>obj</code></td>
<td>An R object whose SQL type we want to determine.</td>
</tr>
<tr class="odd">
<td><code>...</code></td>
<td>Other arguments passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="details" class="section level3">
<h3>Details</h3>
<p>The data types supported by databases are different than the data
types in R, but the mapping between the primitive types is
straightforward:</p>
<ul>
<li><p>Any of the many fixed and varying length character types are
mapped to character vectors</p></li>
<li><p>Fixed-precision (non-IEEE) numbers are mapped into either numeric
or integer vectors.</p></li>
</ul>
<p>Notice that many DBMS do not follow IEEE arithmetic, so there are
potential problems with under/overflows and loss of precision.</p>
</div>
<div id="value" class="section level3">
<h3>Value</h3>
<p><code>dbDataType()</code> returns the SQL type that corresponds to
the <code>obj</code> argument as a non-empty character string. For data
frames, a character vector with one element per column is returned.</p>
</div>
<div id="failure-modes" class="section level3">
<h3>Failure modes</h3>
<p>An error is raised for invalid values for the <code>obj</code>
argument such as a <code>NULL</code> value.</p>
</div>
<div id="specification" class="section level3">
<h3>Specification</h3>
<p>The backend can override the <code>dbDataType()</code> generic for
its driver class.</p>
<p>This generic expects an arbitrary object as second argument. To query
the values returned by the default implementation, run
<code>example(dbDataType, package = &quot;DBI&quot;)</code>. If the backend needs
to override this generic, it must accept all basic R data types as its
second argument, namely logical, integer, numeric, character, dates (see
Dates), date-time (see DateTimeClasses), and difftime. If the database
supports blobs, this method also must accept lists of raw vectors, and
<a href="blob::blob" class="uri">blob::blob</a> objects. As-is objects
(i.e., wrapped by <code>I()</code>) must be supported and return the
same results as their unwrapped counterparts. The SQL data type for
factor and ordered is the same as for character. The behavior for other
object types is not specified.</p>
<p>All data types returned by <code>dbDataType()</code> are usable in an
SQL statement of the form <code>&quot;CREATE TABLE test (a ...)&quot;</code>.</p>
</div>
<div id="examples-1" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb3"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb3-1"><a href="#cb3-1" tabindex="-1"></a><span class="fu">dbDataType</span>(<span class="fu">ANSI</span>(), <span class="dv">1</span><span class="sc">:</span><span class="dv">5</span>)</span>
<span id="cb3-2"><a href="#cb3-2" tabindex="-1"></a><span class="fu">dbDataType</span>(<span class="fu">ANSI</span>(), <span class="dv">1</span>)</span>
<span id="cb3-3"><a href="#cb3-3" tabindex="-1"></a><span class="fu">dbDataType</span>(<span class="fu">ANSI</span>(), <span class="cn">TRUE</span>)</span>
<span id="cb3-4"><a href="#cb3-4" tabindex="-1"></a><span class="fu">dbDataType</span>(<span class="fu">ANSI</span>(), <span class="fu">Sys.Date</span>())</span>
<span id="cb3-5"><a href="#cb3-5" tabindex="-1"></a><span class="fu">dbDataType</span>(<span class="fu">ANSI</span>(), <span class="fu">Sys.time</span>())</span>
<span id="cb3-6"><a href="#cb3-6" tabindex="-1"></a><span class="fu">dbDataType</span>(<span class="fu">ANSI</span>(), <span class="fu">Sys.time</span>() <span class="sc">-</span> <span class="fu">as.POSIXct</span>(<span class="fu">Sys.Date</span>()))</span>
<span id="cb3-7"><a href="#cb3-7" tabindex="-1"></a><span class="fu">dbDataType</span>(<span class="fu">ANSI</span>(), <span class="fu">c</span>(<span class="st">&quot;x&quot;</span>, <span class="st">&quot;abc&quot;</span>))</span>
<span id="cb3-8"><a href="#cb3-8" tabindex="-1"></a><span class="fu">dbDataType</span>(<span class="fu">ANSI</span>(), <span class="fu">list</span>(<span class="fu">raw</span>(<span class="dv">10</span>), <span class="fu">raw</span>(<span class="dv">20</span>)))</span>
<span id="cb3-9"><a href="#cb3-9" tabindex="-1"></a><span class="fu">dbDataType</span>(<span class="fu">ANSI</span>(), <span class="fu">I</span>(<span class="dv">3</span>))</span>
<span id="cb3-10"><a href="#cb3-10" tabindex="-1"></a></span>
<span id="cb3-11"><a href="#cb3-11" tabindex="-1"></a><span class="fu">dbDataType</span>(<span class="fu">ANSI</span>(), iris)</span>
<span id="cb3-12"><a href="#cb3-12" tabindex="-1"></a></span>
<span id="cb3-13"><a href="#cb3-13" tabindex="-1"></a></span>
<span id="cb3-14"><a href="#cb3-14" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb3-15"><a href="#cb3-15" tabindex="-1"></a></span>
<span id="cb3-16"><a href="#cb3-16" tabindex="-1"></a><span class="fu">dbDataType</span>(con, <span class="dv">1</span><span class="sc">:</span><span class="dv">5</span>)</span>
<span id="cb3-17"><a href="#cb3-17" tabindex="-1"></a><span class="fu">dbDataType</span>(con, <span class="dv">1</span>)</span>
<span id="cb3-18"><a href="#cb3-18" tabindex="-1"></a><span class="fu">dbDataType</span>(con, <span class="cn">TRUE</span>)</span>
<span id="cb3-19"><a href="#cb3-19" tabindex="-1"></a><span class="fu">dbDataType</span>(con, <span class="fu">Sys.Date</span>())</span>
<span id="cb3-20"><a href="#cb3-20" tabindex="-1"></a><span class="fu">dbDataType</span>(con, <span class="fu">Sys.time</span>())</span>
<span id="cb3-21"><a href="#cb3-21" tabindex="-1"></a><span class="fu">dbDataType</span>(con, <span class="fu">Sys.time</span>() <span class="sc">-</span> <span class="fu">as.POSIXct</span>(<span class="fu">Sys.Date</span>()))</span>
<span id="cb3-22"><a href="#cb3-22" tabindex="-1"></a><span class="fu">dbDataType</span>(con, <span class="fu">c</span>(<span class="st">&quot;x&quot;</span>, <span class="st">&quot;abc&quot;</span>))</span>
<span id="cb3-23"><a href="#cb3-23" tabindex="-1"></a><span class="fu">dbDataType</span>(con, <span class="fu">list</span>(<span class="fu">raw</span>(<span class="dv">10</span>), <span class="fu">raw</span>(<span class="dv">20</span>)))</span>
<span id="cb3-24"><a href="#cb3-24" tabindex="-1"></a><span class="fu">dbDataType</span>(con, <span class="fu">I</span>(<span class="dv">3</span>))</span>
<span id="cb3-25"><a href="#cb3-25" tabindex="-1"></a></span>
<span id="cb3-26"><a href="#cb3-26" tabindex="-1"></a><span class="fu">dbDataType</span>(con, iris)</span>
<span id="cb3-27"><a href="#cb3-27" tabindex="-1"></a></span>
<span id="cb3-28"><a href="#cb3-28" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="create-a-connection-to-a-dbms" class="section level2">
<h2>Create a connection to a DBMS</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb4"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb4-1"><a href="#cb4-1" tabindex="-1"></a><span class="fu">dbConnect</span>(drv, ...)</span></code></pre></div>
<div id="description-1" class="section level3">
<h3>Description</h3>
<p>Connect to a DBMS going through the appropriate authentication
procedure. Some implementations may allow you to have multiple
connections open, so you may invoke this function repeatedly assigning
its output to different objects. The authentication mechanism is left
unspecified, so check the documentation of individual drivers for
details. Use <code>dbCanConnect()</code> to check if a connection can be
established.</p>
</div>
<div id="arguments-1" class="section level3">
<h3>Arguments</h3>
<table>
<colgroup>
<col width="3%" />
<col width="96%" />
</colgroup>
<tbody>
<tr class="odd">
<td><code>drv</code></td>
<td>an object that inherits from DBIDriver, or an existing DBIConnection
object (in order to clone an existing connection).</td>
</tr>
<tr class="even">
<td><code>...</code></td>
<td>authentication arguments needed by the DBMS instance; these
typically include <code>user</code>, <code>password</code>,
<code>host</code>, <code>port</code>, <code>dbname</code>, etc. For
details see the appropriate <code>DBIDriver</code>.</td>
</tr>
</tbody>
</table>
</div>
<div id="value-1" class="section level3">
<h3>Value</h3>
<p><code>dbConnect()</code> returns an S4 object that inherits from
DBIConnection. This object is used to communicate with the database
engine.</p>
<p>A <code>format()</code> method is defined for the connection object.
It returns a string that consists of a single line of text.</p>
</div>
<div id="specification-1" class="section level3">
<h3>Specification</h3>
<p>DBI recommends using the following argument names for authentication
parameters, with <code>NULL</code> default:</p>
<ul>
<li><p><code>user</code> for the user name (default: current
user)</p></li>
<li><p><code>password</code> for the password</p></li>
<li><p><code>host</code> for the host name (default: local
connection)</p></li>
<li><p><code>port</code> for the port number (default: local
connection)</p></li>
<li><p><code>dbname</code> for the name of the database on the host, or
the database file name</p></li>
</ul>
<p>The defaults should provide reasonable behavior, in particular a
local connection for <code>host = NULL</code>. For some DBMS (e.g.,
PostgreSQL), this is different to a TCP/IP connection to
<code>localhost</code>.</p>
<p>In addition, DBI supports the <code>bigint</code> argument that
governs how 64-bit integer data is returned. The following values are
supported:</p>
<ul>
<li><p><code>&quot;integer&quot;</code>: always return as <code>integer</code>,
silently overflow</p></li>
<li><p><code>&quot;numeric&quot;</code>: always return as <code>numeric</code>,
silently round</p></li>
<li><p><code>&quot;character&quot;</code>: always return the decimal
representation as <code>character</code></p></li>
<li><p><code>&quot;integer64&quot;</code>: return as a data type that can be
coerced using <code>as.integer()</code> (with warning on overflow),
<code>as.numeric()</code> and <code>as.character()</code></p></li>
</ul>
</div>
<div id="examples-2" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb5"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb5-1"><a href="#cb5-1" tabindex="-1"></a><span class="co"># SQLite only needs a path to the database. (Here, &quot;:memory:&quot; is a special</span></span>
<span id="cb5-2"><a href="#cb5-2" tabindex="-1"></a><span class="co"># path that creates an in-memory database.) Other database drivers</span></span>
<span id="cb5-3"><a href="#cb5-3" tabindex="-1"></a><span class="co"># will require more details (like user, password, host, port, etc.)</span></span>
<span id="cb5-4"><a href="#cb5-4" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb5-5"><a href="#cb5-5" tabindex="-1"></a>con</span>
<span id="cb5-6"><a href="#cb5-6" tabindex="-1"></a></span>
<span id="cb5-7"><a href="#cb5-7" tabindex="-1"></a><span class="fu">dbListTables</span>(con)</span>
<span id="cb5-8"><a href="#cb5-8" tabindex="-1"></a></span>
<span id="cb5-9"><a href="#cb5-9" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span>
<span id="cb5-10"><a href="#cb5-10" tabindex="-1"></a></span>
<span id="cb5-11"><a href="#cb5-11" tabindex="-1"></a><span class="co"># Bad, for subtle reasons:</span></span>
<span id="cb5-12"><a href="#cb5-12" tabindex="-1"></a><span class="co"># This code fails when RSQLite isn&#39;t loaded yet,</span></span>
<span id="cb5-13"><a href="#cb5-13" tabindex="-1"></a><span class="co"># because dbConnect() doesn&#39;t know yet about RSQLite.</span></span>
<span id="cb5-14"><a href="#cb5-14" tabindex="-1"></a><span class="fu">dbListTables</span>(con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>))</span></code></pre></div>
</div>
</div>
<div id="disconnect-close-a-connection" class="section level2">
<h2>Disconnect (close) a connection</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb6"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb6-1"><a href="#cb6-1" tabindex="-1"></a><span class="fu">dbDisconnect</span>(conn, ...)</span></code></pre></div>
<div id="description-2" class="section level3">
<h3>Description</h3>
<p>This closes the connection, discards all pending work, and frees
resources (e.g., memory, sockets).</p>
</div>
<div id="arguments-2" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>conn</code></td>
<td>A DBIConnection object, as returned by
<code>dbConnect()</code>.</td>
</tr>
<tr class="even">
<td><code>...</code></td>
<td>Other parameters passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="value-2" class="section level3">
<h3>Value</h3>
<p><code>dbDisconnect()</code> returns <code>TRUE</code>, invisibly.</p>
</div>
<div id="failure-modes-1" class="section level3">
<h3>Failure modes</h3>
<p>A warning is issued on garbage collection when a connection has been
released without calling <code>dbDisconnect()</code>, but this cannot be
tested automatically. At least one warning is issued immediately when
calling <code>dbDisconnect()</code> on an already disconnected or
invalid connection.</p>
</div>
<div id="examples-3" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb7"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb7-1"><a href="#cb7-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb7-2"><a href="#cb7-2" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="execute-a-query-on-a-given-database-connection" class="section level2">
<h2>Execute a query on a given database connection</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb8"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb8-1"><a href="#cb8-1" tabindex="-1"></a><span class="fu">dbSendQuery</span>(conn, statement, ...)</span></code></pre></div>
<div id="description-3" class="section level3">
<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>dbFetch()</code> method, and then you must call
<code>dbClearResult()</code> when you finish fetching the records you
need. For interactive use, you should almost always prefer
<code>dbGetQuery()</code>. Use <code>dbSendQueryArrow()</code> or
<code>dbGetQueryArrow()</code> instead to retrieve the results as an
Arrow object.</p>
</div>
<div id="arguments-3" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>conn</code></td>
<td>A DBIConnection object, as returned by
<code>dbConnect()</code>.</td>
</tr>
<tr class="even">
<td><code>statement</code></td>
<td>a character string containing SQL.</td>
</tr>
<tr class="odd">
<td><code>...</code></td>
<td>Other parameters passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="additional-arguments" class="section level3">
<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>
</div>
<div id="specification-2" class="section level3">
<h3>Specification</h3>
<p>No warnings occur under normal conditions. When done, the DBIResult
object must be cleared with a call to <code>dbClearResult()</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>dbBind()</code> for details.</p>
</div>
<div id="specification-for-the-immediate-argument" class="section level3">
<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 style="list-style-type: decimal">
<li><p>DBI backend defaults to <code>immediate = TRUE</code>
internally</p>
<ol style="list-style-type: decimal">
<li><p>A query without parameters is passed: query is executed</p></li>
<li><p>A query with parameters is passed:</p>
<ol style="list-style-type: decimal">
<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 style="list-style-type: decimal">
<li><p>A query without parameters is passed:</p>
<ol style="list-style-type: decimal">
<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 style="list-style-type: decimal">
<li><p><code>params</code> not given: waiting for parameters via
<code>dbBind()</code></p></li>
<li><p><code>params</code> given: query is executed</p></li>
</ol></li>
</ol></li>
</ol>
</div>
<div id="details-1" class="section level3">
<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>dbSendStatement()</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>
</div>
<div id="value-3" class="section level3">
<h3>Value</h3>
<p><code>dbSendQuery()</code> returns an S4 object that inherits from
DBIResult. The result set can be used with <code>dbFetch()</code> to
extract records. Once you have finished using a result, make sure to
clear it with <code>dbClearResult()</code>.</p>
</div>
<div id="the-data-retrieval-flow" class="section level3">
<h3>The data retrieval flow</h3>
<p>This section gives a complete overview over the flow for the
execution of queries that return tabular data as data frames.</p>
<p>Most of this flow, except repeated calling of <code>dbBind()</code>
or <code>dbBindArrow()</code>, is implemented by
<code>dbGetQuery()</code>, which should be sufficient unless you want to
access the results in a paged way or you have a parameterized query that
you want to reuse. This flow requires an active connection established
by <code>dbConnect()</code>. See also
<code>vignette(&quot;dbi-advanced&quot;)</code> for a walkthrough.</p>
<ol style="list-style-type: decimal">
<li><p>Use <code>dbSendQuery()</code> to create a result set object of
class DBIResult.</p></li>
<li><p>Optionally, bind query parameters with <code>dbBind()</code> or
<code>dbBindArrow()</code>. This is required only if the query contains
placeholders such as <code>⁠?⁠</code> or <code>⁠\$1⁠</code>, depending on
the database backend.</p></li>
<li><p>Optionally, use <code>dbColumnInfo()</code> to retrieve the
structure of the result set without retrieving actual data.</p></li>
<li><p>Use <code>dbFetch()</code> to get the entire result set, a page
of results, or the remaining rows. Fetching zero rows is also possible
to retrieve the structure of the result set as a data frame. This step
can be called multiple times. Only forward paging is supported, you need
to cache previous pages if you need to navigate backwards.</p></li>
<li><p>Use <code>dbHasCompleted()</code> to tell when you’re done. This
method returns <code>TRUE</code> if no more rows are available for
fetching.</p></li>
<li><p>Repeat the last four steps as necessary.</p></li>
<li><p>Use <code>dbClearResult()</code> 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 use
<code>on.exit()</code> or <code>withr::defer()</code> to ensure that
this step is always executed.</p></li>
</ol>
</div>
<div id="failure-modes-2" class="section level3">
<h3>Failure modes</h3>
<p>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>
</div>
<div id="examples-4" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb9"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb9-1"><a href="#cb9-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb9-2"><a href="#cb9-2" tabindex="-1"></a></span>
<span id="cb9-3"><a href="#cb9-3" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;mtcars&quot;</span>, mtcars)</span>
<span id="cb9-4"><a href="#cb9-4" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendQuery</span>(con, <span class="st">&quot;SELECT * FROM mtcars WHERE cyl = 4&quot;</span>)</span>
<span id="cb9-5"><a href="#cb9-5" tabindex="-1"></a><span class="fu">dbFetch</span>(rs)</span>
<span id="cb9-6"><a href="#cb9-6" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb9-7"><a href="#cb9-7" tabindex="-1"></a></span>
<span id="cb9-8"><a href="#cb9-8" tabindex="-1"></a><span class="co"># Pass one set of values with the param argument:</span></span>
<span id="cb9-9"><a href="#cb9-9" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendQuery</span>(</span>
<span id="cb9-10"><a href="#cb9-10" tabindex="-1"></a>  con,</span>
<span id="cb9-11"><a href="#cb9-11" tabindex="-1"></a>  <span class="st">&quot;SELECT * FROM mtcars WHERE cyl = ?&quot;</span>,</span>
<span id="cb9-12"><a href="#cb9-12" tabindex="-1"></a>  <span class="at">params =</span> <span class="fu">list</span>(<span class="dv">4</span><span class="dt">L</span>)</span>
<span id="cb9-13"><a href="#cb9-13" tabindex="-1"></a>)</span>
<span id="cb9-14"><a href="#cb9-14" tabindex="-1"></a><span class="fu">dbFetch</span>(rs)</span>
<span id="cb9-15"><a href="#cb9-15" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb9-16"><a href="#cb9-16" tabindex="-1"></a></span>
<span id="cb9-17"><a href="#cb9-17" tabindex="-1"></a><span class="co"># Pass multiple sets of values with dbBind():</span></span>
<span id="cb9-18"><a href="#cb9-18" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendQuery</span>(con, <span class="st">&quot;SELECT * FROM mtcars WHERE cyl = ?&quot;</span>)</span>
<span id="cb9-19"><a href="#cb9-19" tabindex="-1"></a><span class="fu">dbBind</span>(rs, <span class="fu">list</span>(<span class="dv">6</span><span class="dt">L</span>))</span>
<span id="cb9-20"><a href="#cb9-20" tabindex="-1"></a><span class="fu">dbFetch</span>(rs)</span>
<span id="cb9-21"><a href="#cb9-21" tabindex="-1"></a><span class="fu">dbBind</span>(rs, <span class="fu">list</span>(<span class="dv">8</span><span class="dt">L</span>))</span>
<span id="cb9-22"><a href="#cb9-22" tabindex="-1"></a><span class="fu">dbFetch</span>(rs)</span>
<span id="cb9-23"><a href="#cb9-23" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb9-24"><a href="#cb9-24" tabindex="-1"></a></span>
<span id="cb9-25"><a href="#cb9-25" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="fetch-records-from-a-previously-executed-query" class="section level2">
<h2>Fetch records from a previously executed query</h2>
<p>This section describes the behavior of the following methods:</p>
<div class="sourceCode" id="cb10"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb10-1"><a href="#cb10-1" tabindex="-1"></a><span class="fu">dbFetch</span>(res, <span class="at">n =</span> <span class="sc">-</span><span class="dv">1</span>, ...)</span>
<span id="cb10-2"><a href="#cb10-2" tabindex="-1"></a></span>
<span id="cb10-3"><a href="#cb10-3" tabindex="-1"></a><span class="fu">fetch</span>(res, <span class="at">n =</span> <span class="sc">-</span><span class="dv">1</span>, ...)</span></code></pre></div>
<div id="description-4" class="section level3">
<h3>Description</h3>
<p>Fetch the next <code>n</code> elements (rows) from the result set and
return them as a data.frame.</p>
</div>
<div id="arguments-4" class="section level3">
<h3>Arguments</h3>
<table>
<colgroup>
<col width="4%" />
<col width="95%" />
</colgroup>
<tbody>
<tr class="odd">
<td><code>res</code></td>
<td>An object inheriting from DBIResult, created by
<code>dbSendQuery()</code>.</td>
</tr>
<tr class="even">
<td><code>n</code></td>
<td>maximum number of records to retrieve per fetch. Use
<code>n = -1</code> or <code>n = Inf</code> to retrieve all pending
records. Some implementations may recognize other special values.</td>
</tr>
<tr class="odd">
<td><code>...</code></td>
<td>Other arguments passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="details-2" class="section level3">
<h3>Details</h3>
<p><code>fetch()</code> is provided for compatibility with older DBI
clients - for all new code you are strongly encouraged to use
<code>dbFetch()</code>. The default implementation for
<code>dbFetch()</code> calls <code>fetch()</code> so that it is
compatible with existing code. Modern backends should implement for
<code>dbFetch()</code> only.</p>
</div>
<div id="value-4" class="section level3">
<h3>Value</h3>
<p><code>dbFetch()</code> always returns a data.frame with as many rows
as records were fetched and as many columns as fields in the result set,
even if the result is a single value or has one or zero rows. Passing
<code>n = NA</code> is supported and returns an arbitrary number of rows
(at least one) as specified by the driver, but at most the remaining
rows in the result set.</p>
</div>
<div id="the-data-retrieval-flow-1" class="section level3">
<h3>The data retrieval flow</h3>
<p>This section gives a complete overview over the flow for the
execution of queries that return tabular data as data frames.</p>
<p>Most of this flow, except repeated calling of <code>dbBind()</code>
or <code>dbBindArrow()</code>, is implemented by
<code>dbGetQuery()</code>, which should be sufficient unless you want to
access the results in a paged way or you have a parameterized query that
you want to reuse. This flow requires an active connection established
by <code>dbConnect()</code>. See also
<code>vignette(&quot;dbi-advanced&quot;)</code> for a walkthrough.</p>
<ol style="list-style-type: decimal">
<li><p>Use <code>dbSendQuery()</code> to create a result set object of
class DBIResult.</p></li>
<li><p>Optionally, bind query parameters with <code>dbBind()</code> or
<code>dbBindArrow()</code>. This is required only if the query contains
placeholders such as <code>⁠?⁠</code> or <code>⁠\$1⁠</code>, depending on
the database backend.</p></li>
<li><p>Optionally, use <code>dbColumnInfo()</code> to retrieve the
structure of the result set without retrieving actual data.</p></li>
<li><p>Use <code>dbFetch()</code> to get the entire result set, a page
of results, or the remaining rows. Fetching zero rows is also possible
to retrieve the structure of the result set as a data frame. This step
can be called multiple times. Only forward paging is supported, you need
to cache previous pages if you need to navigate backwards.</p></li>
<li><p>Use <code>dbHasCompleted()</code> to tell when you’re done. This
method returns <code>TRUE</code> if no more rows are available for
fetching.</p></li>
<li><p>Repeat the last four steps as necessary.</p></li>
<li><p>Use <code>dbClearResult()</code> 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 use
<code>on.exit()</code> or <code>withr::defer()</code> to ensure that
this step is always executed.</p></li>
</ol>
</div>
<div id="failure-modes-3" class="section level3">
<h3>Failure modes</h3>
<p>An attempt to fetch from a closed result set raises an error. If the
<code>n</code> argument is not an atomic whole number greater or equal
to -1 or Inf, an error is raised, but a subsequent call to
<code>dbFetch()</code> with proper <code>n</code> argument succeeds.</p>
<p>Calling <code>dbFetch()</code> on a result set from a data
manipulation query created by <code>dbSendStatement()</code> can be
fetched and return an empty data frame, with a warning.</p>
</div>
<div id="specification-3" class="section level3">
<h3>Specification</h3>
<p>Fetching multi-row queries with one or more columns by default
returns the entire result. Multi-row queries can also be fetched
progressively by passing a whole number (integer or numeric) as the
<code>n</code> argument. A value of Inf for the <code>n</code> argument
is supported and also returns the full result. If more rows than
available are fetched, the result is returned in full without warning.
If fewer rows than requested are returned, further fetches will return a
data frame with zero rows. If zero rows are fetched, the columns of the
data frame are still fully typed. Fetching fewer rows than available is
permitted, no warning is issued when clearing the result set.</p>
<p>A column named <code>row_names</code> is treated like any other
column.</p>
<p>The column types of the returned data frame depend on the data
returned:</p>
<ul>
<li><p>integer (or coercible to an integer) for integer values between
-2^31 and 2^31 - 1, with NA for SQL <code>NULL</code> values</p></li>
<li><p>numeric for numbers with a fractional component, with NA for SQL
<code>NULL</code> values</p></li>
<li><p>logical for Boolean values (some backends may return an integer);
with NA for SQL <code>NULL</code> values</p></li>
<li><p>character for text, with NA for SQL <code>NULL</code>
values</p></li>
<li><p>lists of raw for blobs with NULL entries for SQL NULL
values</p></li>
<li><p>coercible using <code>as.Date()</code> for dates, with NA for SQL
<code>NULL</code> values (also applies to the return value of the SQL
function <code>current_date</code>)</p></li>
<li><p>coercible using <code>hms::as_hms()</code> for times, with NA for
SQL <code>NULL</code> values (also applies to the return value of the
SQL function <code>current_time</code>)</p></li>
<li><p>coercible using <code>as.POSIXct()</code> for timestamps, with NA
for SQL <code>NULL</code> values (also applies to the return value of
the SQL function <code>current_timestamp</code>)</p></li>
</ul>
<p>If dates and timestamps are supported by the backend, the following R
types are used:</p>
<ul>
<li><p>Date for dates (also applies to the return value of the SQL
function <code>current_date</code>)</p></li>
<li><p>POSIXct for timestamps (also applies to the return value of the
SQL function <code>current_timestamp</code>)</p></li>
</ul>
<p>R has no built-in type with lossless support for the full range of
64-bit or larger integers. If 64-bit integers are returned from a query,
the following rules apply:</p>
<ul>
<li><p>Values are returned in a container with support for the full
range of valid 64-bit values (such as the <code>integer64</code> class
of the <span class="pkg">bit64</span> package)</p></li>
<li><p>Coercion to numeric always returns a number that is as close as
possible to the true value</p></li>
<li><p>Loss of precision when converting to numeric gives a
warning</p></li>
<li><p>Conversion to character always returns a lossless decimal
representation of the data</p></li>
</ul>
</div>
<div id="examples-5" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb11"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb11-1"><a href="#cb11-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb11-2"><a href="#cb11-2" tabindex="-1"></a></span>
<span id="cb11-3"><a href="#cb11-3" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;mtcars&quot;</span>, mtcars)</span>
<span id="cb11-4"><a href="#cb11-4" tabindex="-1"></a></span>
<span id="cb11-5"><a href="#cb11-5" tabindex="-1"></a><span class="co"># Fetch all results</span></span>
<span id="cb11-6"><a href="#cb11-6" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendQuery</span>(con, <span class="st">&quot;SELECT * FROM mtcars WHERE cyl = 4&quot;</span>)</span>
<span id="cb11-7"><a href="#cb11-7" tabindex="-1"></a><span class="fu">dbFetch</span>(rs)</span>
<span id="cb11-8"><a href="#cb11-8" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb11-9"><a href="#cb11-9" tabindex="-1"></a></span>
<span id="cb11-10"><a href="#cb11-10" tabindex="-1"></a><span class="co"># Fetch in chunks</span></span>
<span id="cb11-11"><a href="#cb11-11" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendQuery</span>(con, <span class="st">&quot;SELECT * FROM mtcars&quot;</span>)</span>
<span id="cb11-12"><a href="#cb11-12" tabindex="-1"></a><span class="cf">while</span> (<span class="sc">!</span><span class="fu">dbHasCompleted</span>(rs)) {</span>
<span id="cb11-13"><a href="#cb11-13" tabindex="-1"></a>  chunk <span class="ot">&lt;-</span> <span class="fu">dbFetch</span>(rs, <span class="dv">10</span>)</span>
<span id="cb11-14"><a href="#cb11-14" tabindex="-1"></a>  <span class="fu">print</span>(<span class="fu">nrow</span>(chunk))</span>
<span id="cb11-15"><a href="#cb11-15" tabindex="-1"></a>}</span>
<span id="cb11-16"><a href="#cb11-16" tabindex="-1"></a></span>
<span id="cb11-17"><a href="#cb11-17" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb11-18"><a href="#cb11-18" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="clear-a-result-set" class="section level2">
<h2>Clear a result set</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb12"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb12-1"><a href="#cb12-1" tabindex="-1"></a><span class="fu">dbClearResult</span>(res, ...)</span></code></pre></div>
<div id="description-5" class="section level3">
<h3>Description</h3>
<p>Frees all resources (local and remote) associated with a result set.
This step is mandatory for all objects obtained by calling
<code>dbSendQuery()</code> or <code>dbSendStatement()</code>.</p>
</div>
<div id="arguments-5" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>res</code></td>
<td>An object inheriting from DBIResult.</td>
</tr>
<tr class="even">
<td><code>...</code></td>
<td>Other arguments passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="value-5" class="section level3">
<h3>Value</h3>
<p><code>dbClearResult()</code> returns <code>TRUE</code>, invisibly,
for result sets obtained from <code>dbSendQuery()</code>,
<code>dbSendStatement()</code>, or <code>dbSendQueryArrow()</code>,</p>
</div>
<div id="the-data-retrieval-flow-2" class="section level3">
<h3>The data retrieval flow</h3>
<p>This section gives a complete overview over the flow for the
execution of queries that return tabular data as data frames.</p>
<p>Most of this flow, except repeated calling of <code>dbBind()</code>
or <code>dbBindArrow()</code>, is implemented by
<code>dbGetQuery()</code>, which should be sufficient unless you want to
access the results in a paged way or you have a parameterized query that
you want to reuse. This flow requires an active connection established
by <code>dbConnect()</code>. See also
<code>vignette(&quot;dbi-advanced&quot;)</code> for a walkthrough.</p>
<ol style="list-style-type: decimal">
<li><p>Use <code>dbSendQuery()</code> to create a result set object of
class DBIResult.</p></li>
<li><p>Optionally, bind query parameters with <code>dbBind()</code> or
<code>dbBindArrow()</code>. This is required only if the query contains
placeholders such as <code>⁠?⁠</code> or <code>⁠\$1⁠</code>, depending on
the database backend.</p></li>
<li><p>Optionally, use <code>dbColumnInfo()</code> to retrieve the
structure of the result set without retrieving actual data.</p></li>
<li><p>Use <code>dbFetch()</code> to get the entire result set, a page
of results, or the remaining rows. Fetching zero rows is also possible
to retrieve the structure of the result set as a data frame. This step
can be called multiple times. Only forward paging is supported, you need
to cache previous pages if you need to navigate backwards.</p></li>
<li><p>Use <code>dbHasCompleted()</code> to tell when you’re done. This
method returns <code>TRUE</code> if no more rows are available for
fetching.</p></li>
<li><p>Repeat the last four steps as necessary.</p></li>
<li><p>Use <code>dbClearResult()</code> 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 use
<code>on.exit()</code> or <code>withr::defer()</code> to ensure that
this step is always executed.</p></li>
</ol>
</div>
<div id="the-command-execution-flow" class="section level3">
<h3>The command execution flow</h3>
<p>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
<code>dbBindArrow()</code>, is implemented by <code>dbExecute()</code>,
which should be sufficient for non-parameterized queries. This flow
requires an active connection established by <code>dbConnect()</code>.
See also <code>vignette(&quot;dbi-advanced&quot;)</code> for a walkthrough.</p>
<ol style="list-style-type: decimal">
<li><p>Use <code>dbSendStatement()</code> to create a result set object
of class DBIResult. For some queries you need to pass
<code>immediate = TRUE</code>.</p></li>
<li><p>Optionally, bind query parameters with<code>dbBind()</code> or
<code>dbBindArrow()</code>. This is required only if the query contains
placeholders such as <code>⁠?⁠</code> or <code>⁠\$1⁠</code>, depending on
the database backend.</p></li>
<li><p>Optionally, use <code>dbGetRowsAffected()</code> to retrieve the
number of rows affected by the query.</p></li>
<li><p>Repeat the last two steps as necessary.</p></li>
<li><p>Use <code>dbClearResult()</code> 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 use
<code>on.exit()</code> or <code>withr::defer()</code> to ensure that
this step is always executed.</p></li>
</ol>
</div>
<div id="failure-modes-4" class="section level3">
<h3>Failure modes</h3>
<p>An attempt to close an already closed result set issues a warning for
<code>dbSendQuery()</code>, <code>dbSendStatement()</code>, and
<code>dbSendQueryArrow()</code>,</p>
</div>
<div id="specification-4" class="section level3">
<h3>Specification</h3>
<p><code>dbClearResult()</code> frees all resources associated with
retrieving the result of a query or update operation. The DBI backend
can expect a call to <code>dbClearResult()</code> for each
<code>dbSendQuery()</code> or <code>dbSendStatement()</code> call.</p>
</div>
<div id="examples-6" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb13"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb13-1"><a href="#cb13-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb13-2"><a href="#cb13-2" tabindex="-1"></a></span>
<span id="cb13-3"><a href="#cb13-3" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendQuery</span>(con, <span class="st">&quot;SELECT 1&quot;</span>)</span>
<span id="cb13-4"><a href="#cb13-4" tabindex="-1"></a><span class="fu">print</span>(<span class="fu">dbFetch</span>(rs))</span>
<span id="cb13-5"><a href="#cb13-5" tabindex="-1"></a></span>
<span id="cb13-6"><a href="#cb13-6" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb13-7"><a href="#cb13-7" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="bind-values-to-a-parameterizedprepared-statement" class="section level2">
<h2>Bind values to a parameterized/prepared statement</h2>
<p>This section describes the behavior of the following methods:</p>
<div class="sourceCode" id="cb14"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb14-1"><a href="#cb14-1" tabindex="-1"></a><span class="fu">dbBind</span>(res, params, ...)</span>
<span id="cb14-2"><a href="#cb14-2" tabindex="-1"></a></span>
<span id="cb14-3"><a href="#cb14-3" tabindex="-1"></a><span class="fu">dbBindArrow</span>(res, params, ...)</span></code></pre></div>
<div id="description-6" class="section level3">
<h3>Description</h3>
<p>For parametrized or prepared statements, the
<code>dbSendQuery()</code>, <code>dbSendQueryArrow()</code>, and
<code>dbSendStatement()</code> functions can be called with statements
that contain placeholders for values. The <code>dbBind()</code> and
<code>dbBindArrow()</code> functions bind these placeholders to actual
values, and are intended to be called on the result set before calling
<code>dbFetch()</code> or <code>dbFetchArrow()</code>. The values are
passed to <code>dbBind()</code> as lists or data frames, and to
<code>dbBindArrow()</code> as a stream created by
<code>nanoarrow::as_nanoarrow_array_stream()</code>.</p>
<p><a href="https://lifecycle.r-lib.org/articles/stages.html#experimental"><svg id="svg_93af85992c106be3935b" alt="Experimental" width="138" height="20" role="img" aria-label="lifecycle: experimental" viewBox="0 0 138 20">
    <title>lifecycle: experimental</title>
    <linearGradient id="svg_93af85992c106be3935b_s" x2="0" y2="100%">
        <stop offset="0" stop-color="#bbb" stop-opacity=".1"></stop>
        <stop offset="1" stop-opacity=".1"></stop>
    </linearGradient>
    <clipPath id="svg_93af85992c106be3935b_r">
        <rect width="138" height="20" rx="3" fill="#fff" />
    </clipPath>
    <g clip-path="url(#r)">
        <rect width="55" height="20" fill="#555" />
        <rect x="55" width="83" height="20" fill="#fe7d37" />
        <rect width="138" height="20" fill="url(#s)" />
    </g>
    <g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110">
        <text aria-hidden="true" x="285" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="450">lifecycle</text>
        <text x="285" y="140" transform="scale(.1)" fill="#fff" textLength="450">lifecycle</text>
        <text aria-hidden="true" x="955" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="730">experimental</text>
        <text x="955" y="140" transform="scale(.1)" fill="#fff" textLength="730">experimental</text>
    </g>
</svg></a></p>
<p><code>dbBindArrow()</code> is experimental, as are the other
<code>⁠*Arrow⁠</code> functions. <code>dbSendQuery()</code> is compatible
with <code>dbBindArrow()</code>, and <code>dbSendQueryArrow()</code> is
compatible with <code>dbBind()</code>.</p>
</div>
<div id="arguments-6" class="section level3">
<h3>Arguments</h3>
<table>
<colgroup>
<col width="4%" />
<col width="95%" />
</colgroup>
<tbody>
<tr class="odd">
<td><code>res</code></td>
<td>An object inheriting from DBIResult.</td>
</tr>
<tr class="even">
<td><code>params</code></td>
<td>For <code>dbBind()</code>, a list of values, named or unnamed, or a
data frame, with one element/column per query parameter. For
<code>dbBindArrow()</code>, values as a nanoarrow stream, with one
column per query parameter.</td>
</tr>
<tr class="odd">
<td><code>...</code></td>
<td>Other arguments passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="details-3" class="section level3">
<h3>Details</h3>
<p><span class="pkg">DBI</span> supports parametrized (or prepared)
queries and statements via the <code>dbBind()</code> and
<code>dbBindArrow()</code> generics. Parametrized queries are different
from normal queries in that they allow an arbitrary number of
placeholders, which are later substituted by actual values. Parametrized
queries (and statements) serve two purposes:</p>
<ul>
<li><p>The same query can be executed more than once with different
values. The DBMS may cache intermediate information for the query, such
as the execution plan, and execute it faster.</p></li>
<li><p>Separation of query syntax and parameters protects against SQL
injection.</p></li>
</ul>
<p>The placeholder format is currently not specified by <span class="pkg">DBI</span>; in the future, a uniform placeholder syntax may
be supported. Consult the backend documentation for the supported
formats. For automated testing, backend authors specify the placeholder
syntax with the <code>placeholder_pattern</code> tweak. Known examples
are:</p>
<ul>
<li><p><code>⁠?⁠</code> (positional matching in order of appearance) in
<span class="pkg">RMariaDB</span> and <span class="pkg">RSQLite</span></p></li>
<li><p><code>⁠\$1⁠</code> (positional matching by index) in <span class="pkg">RPostgres</span> and <span class="pkg">RSQLite</span></p></li>
<li><p><code>⁠:name⁠</code> and <code>⁠\$name⁠</code> (named matching) in
<span class="pkg">RSQLite</span></p></li>
</ul>
</div>
<div id="value-6" class="section level3">
<h3>Value</h3>
<p><code>dbBind()</code> returns the result set, invisibly, for queries
issued by <code>dbSendQuery()</code> or <code>dbSendQueryArrow()</code>
and also for data manipulation statements issued by
<code>dbSendStatement()</code>.</p>
</div>
<div id="the-data-retrieval-flow-3" class="section level3">
<h3>The data retrieval flow</h3>
<p>This section gives a complete overview over the flow for the
execution of queries that return tabular data as data frames.</p>
<p>Most of this flow, except repeated calling of <code>dbBind()</code>
or <code>dbBindArrow()</code>, is implemented by
<code>dbGetQuery()</code>, which should be sufficient unless you want to
access the results in a paged way or you have a parameterized query that
you want to reuse. This flow requires an active connection established
by <code>dbConnect()</code>. See also
<code>vignette(&quot;dbi-advanced&quot;)</code> for a walkthrough.</p>
<ol style="list-style-type: decimal">
<li><p>Use <code>dbSendQuery()</code> to create a result set object of
class DBIResult.</p></li>
<li><p>Optionally, bind query parameters with <code>dbBind()</code> or
<code>dbBindArrow()</code>. This is required only if the query contains
placeholders such as <code>⁠?⁠</code> or <code>⁠\$1⁠</code>, depending on
the database backend.</p></li>
<li><p>Optionally, use <code>dbColumnInfo()</code> to retrieve the
structure of the result set without retrieving actual data.</p></li>
<li><p>Use <code>dbFetch()</code> to get the entire result set, a page
of results, or the remaining rows. Fetching zero rows is also possible
to retrieve the structure of the result set as a data frame. This step
can be called multiple times. Only forward paging is supported, you need
to cache previous pages if you need to navigate backwards.</p></li>
<li><p>Use <code>dbHasCompleted()</code> to tell when you’re done. This
method returns <code>TRUE</code> if no more rows are available for
fetching.</p></li>
<li><p>Repeat the last four steps as necessary.</p></li>
<li><p>Use <code>dbClearResult()</code> 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 use
<code>on.exit()</code> or <code>withr::defer()</code> to ensure that
this step is always executed.</p></li>
</ol>
</div>
<div id="the-data-retrieval-flow-for-arrow-streams" class="section level3">
<h3>The data retrieval flow for Arrow streams</h3>
<p>This section gives a complete overview over the flow for the
execution of queries that return tabular data as an Arrow stream.</p>
<p>Most of this flow, except repeated calling of
<code>dbBindArrow()</code> or <code>dbBind()</code>, is implemented by
<code>dbGetQueryArrow()</code>, which should be sufficient unless you
have a parameterized query that you want to reuse. This flow requires an
active connection established by <code>dbConnect()</code>. See also
<code>vignette(&quot;dbi-advanced&quot;)</code> for a walkthrough.</p>
<ol style="list-style-type: decimal">
<li><p>Use <code>dbSendQueryArrow()</code> to create a result set object
of class DBIResultArrow.</p></li>
<li><p>Optionally, bind query parameters with <code>dbBindArrow()</code>
or <code>dbBind()</code>. This is required only if the query contains
placeholders such as <code>⁠?⁠</code> or <code>⁠\$1⁠</code>, depending on
the database backend.</p></li>
<li><p>Use <code>dbFetchArrow()</code> to get a data stream.</p></li>
<li><p>Repeat the last two steps as necessary.</p></li>
<li><p>Use <code>dbClearResult()</code> 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 use
<code>on.exit()</code> or <code>withr::defer()</code> to ensure that
this step is always executed.</p></li>
</ol>
</div>
<div id="the-command-execution-flow-1" class="section level3">
<h3>The command execution flow</h3>
<p>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
<code>dbBindArrow()</code>, is implemented by <code>dbExecute()</code>,
which should be sufficient for non-parameterized queries. This flow
requires an active connection established by <code>dbConnect()</code>.
See also <code>vignette(&quot;dbi-advanced&quot;)</code> for a walkthrough.</p>
<ol style="list-style-type: decimal">
<li><p>Use <code>dbSendStatement()</code> to create a result set object
of class DBIResult. For some queries you need to pass
<code>immediate = TRUE</code>.</p></li>
<li><p>Optionally, bind query parameters with<code>dbBind()</code> or
<code>dbBindArrow()</code>. This is required only if the query contains
placeholders such as <code>⁠?⁠</code> or <code>⁠\$1⁠</code>, depending on
the database backend.</p></li>
<li><p>Optionally, use <code>dbGetRowsAffected()</code> to retrieve the
number of rows affected by the query.</p></li>
<li><p>Repeat the last two steps as necessary.</p></li>
<li><p>Use <code>dbClearResult()</code> 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 use
<code>on.exit()</code> or <code>withr::defer()</code> to ensure that
this step is always executed.</p></li>
</ol>
</div>
<div id="failure-modes-5" class="section level3">
<h3>Failure modes</h3>
<p>Calling <code>dbBind()</code> for a query without parameters raises
an error.</p>
<p>Binding too many or not enough values, or parameters with wrong names
or unequal length, also raises an error. If the placeholders in the
query are named, all parameter values must have names (which must not be
empty or <code>NA</code>), and vice versa, otherwise an error is raised.
The behavior for mixing placeholders of different types (in particular
mixing positional and named placeholders) is not specified.</p>
<p>Calling <code>dbBind()</code> on a result set already cleared by
<code>dbClearResult()</code> also raises an error.</p>
</div>
<div id="specification-5" class="section level3">
<h3>Specification</h3>
<p><span class="pkg">DBI</span> clients execute parametrized statements
as follows:</p>
<ol style="list-style-type: decimal">
<li><p>Call <code>dbSendQuery()</code>, <code>dbSendQueryArrow()</code>
or <code>dbSendStatement()</code> with a query or statement that
contains placeholders, store the returned DBIResult object in a
variable. Mixing placeholders (in particular, named and unnamed ones) is
not recommended. It is good practice to register a call to
<code>dbClearResult()</code> via <code>on.exit()</code> right after
calling <code>dbSendQuery()</code> or <code>dbSendStatement()</code>
(see the last enumeration item). Until <code>dbBind()</code> or
<code>dbBindArrow()</code> have been called, the returned result set
object has the following behavior:</p>
<ul>
<li><p><code>dbFetch()</code> raises an error (for
<code>dbSendQuery()</code> and <code>dbSendQueryArrow()</code>)</p></li>
<li><p><code>dbGetRowCount()</code> returns zero (for
<code>dbSendQuery()</code> and <code>dbSendQueryArrow()</code>)</p></li>
<li><p><code>dbGetRowsAffected()</code> returns an integer
<code>NA</code> (for <code>dbSendStatement()</code>)</p></li>
<li><p><code>dbIsValid()</code> returns <code>TRUE</code></p></li>
<li><p><code>dbHasCompleted()</code> returns <code>FALSE</code></p></li>
</ul></li>
<li><p>Call <code>dbBind()</code> or <code>dbBindArrow()</code>:</p>
<ul>
<li><p>For <code>dbBind()</code>, the <code>params</code> argument must
be a list where all elements have the same lengths and contain values
supported by the backend. A data.frame is internally stored as such a
list.</p></li>
<li><p>For <code>dbBindArrow()</code>, the <code>params</code> argument
must be a nanoarrow array stream, with one column per query
parameter.</p></li>
</ul></li>
<li><p>Retrieve the data or the number of affected rows from the
<code>DBIResult</code> object.</p>
<ul>
<li><p>For queries issued by <code>dbSendQuery()</code> or
<code>dbSendQueryArrow()</code>, call <code>dbFetch()</code>.</p></li>
<li><p>For statements issued by <code>dbSendStatements()</code>, call
<code>dbGetRowsAffected()</code>. (Execution begins immediately after
the <code>dbBind()</code> call, the statement is processed entirely
before the function returns.)</p></li>
</ul></li>
<li><p>Repeat 2. and 3. as necessary.</p></li>
<li><p>Close the result set via <code>dbClearResult()</code>.</p></li>
</ol>
<p>The elements of the <code>params</code> argument do not need to be
scalars, vectors of arbitrary length (including length 0) are supported.
For queries, calling <code>dbFetch()</code> binding such parameters
returns concatenated results, equivalent to binding and fetching for
each set of values and connecting via <code>rbind()</code>. For data
manipulation statements, <code>dbGetRowsAffected()</code> returns the
total number of rows affected if binding non-scalar parameters.
<code>dbBind()</code> also accepts repeated calls on the same result set
for both queries and data manipulation statements, even if no results
are fetched between calls to <code>dbBind()</code>, for both queries and
data manipulation statements.</p>
<p>If the placeholders in the query are named, their order in the
<code>params</code> argument is not important.</p>
<p>At least the following data types are accepted on input (including
NA):</p>
<ul>
<li><p>integer</p></li>
<li><p>numeric</p></li>
<li><p>logical for Boolean values</p></li>
<li><p>character (also with special characters such as spaces, newlines,
quotes, and backslashes)</p></li>
<li><p>factor (bound as character, with warning)</p></li>
<li><p>Date (also when stored internally as integer)</p></li>
<li><p>POSIXct timestamps</p></li>
<li><p>POSIXlt timestamps</p></li>
<li><p>difftime values (also with units other than seconds and with the
value stored as integer)</p></li>
<li><p>lists of raw for blobs (with <code>NULL</code> entries for SQL
NULL values)</p></li>
<li><p>objects of type <a href="blob::blob" class="uri">blob::blob</a></p></li>
</ul>
</div>
<div id="examples-7" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb15"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb15-1"><a href="#cb15-1" tabindex="-1"></a><span class="co"># Data frame flow:</span></span>
<span id="cb15-2"><a href="#cb15-2" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb15-3"><a href="#cb15-3" tabindex="-1"></a></span>
<span id="cb15-4"><a href="#cb15-4" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;iris&quot;</span>, iris)</span>
<span id="cb15-5"><a href="#cb15-5" tabindex="-1"></a></span>
<span id="cb15-6"><a href="#cb15-6" tabindex="-1"></a><span class="co"># Using the same query for different values</span></span>
<span id="cb15-7"><a href="#cb15-7" tabindex="-1"></a>iris_result <span class="ot">&lt;-</span> <span class="fu">dbSendQuery</span>(con, <span class="st">&quot;SELECT * FROM iris WHERE [Petal.Width] &gt; ?&quot;</span>)</span>
<span id="cb15-8"><a href="#cb15-8" tabindex="-1"></a><span class="fu">dbBind</span>(iris_result, <span class="fu">list</span>(<span class="fl">2.3</span>))</span>
<span id="cb15-9"><a href="#cb15-9" tabindex="-1"></a><span class="fu">dbFetch</span>(iris_result)</span>
<span id="cb15-10"><a href="#cb15-10" tabindex="-1"></a><span class="fu">dbBind</span>(iris_result, <span class="fu">list</span>(<span class="dv">3</span>))</span>
<span id="cb15-11"><a href="#cb15-11" tabindex="-1"></a><span class="fu">dbFetch</span>(iris_result)</span>
<span id="cb15-12"><a href="#cb15-12" tabindex="-1"></a><span class="fu">dbClearResult</span>(iris_result)</span>
<span id="cb15-13"><a href="#cb15-13" tabindex="-1"></a></span>
<span id="cb15-14"><a href="#cb15-14" tabindex="-1"></a><span class="co"># Executing the same statement with different values at once</span></span>
<span id="cb15-15"><a href="#cb15-15" tabindex="-1"></a>iris_result <span class="ot">&lt;-</span> <span class="fu">dbSendStatement</span>(con, <span class="st">&quot;DELETE FROM iris WHERE [Species] = \$species&quot;</span>)</span>
<span id="cb15-16"><a href="#cb15-16" tabindex="-1"></a><span class="fu">dbBind</span>(iris_result, <span class="fu">list</span>(<span class="at">species =</span> <span class="fu">c</span>(<span class="st">&quot;setosa&quot;</span>, <span class="st">&quot;versicolor&quot;</span>, <span class="st">&quot;unknown&quot;</span>)))</span>
<span id="cb15-17"><a href="#cb15-17" tabindex="-1"></a><span class="fu">dbGetRowsAffected</span>(iris_result)</span>
<span id="cb15-18"><a href="#cb15-18" tabindex="-1"></a><span class="fu">dbClearResult</span>(iris_result)</span>
<span id="cb15-19"><a href="#cb15-19" tabindex="-1"></a></span>
<span id="cb15-20"><a href="#cb15-20" tabindex="-1"></a><span class="fu">nrow</span>(<span class="fu">dbReadTable</span>(con, <span class="st">&quot;iris&quot;</span>))</span>
<span id="cb15-21"><a href="#cb15-21" tabindex="-1"></a></span>
<span id="cb15-22"><a href="#cb15-22" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span>
<span id="cb15-23"><a href="#cb15-23" tabindex="-1"></a></span>
<span id="cb15-24"><a href="#cb15-24" tabindex="-1"></a></span>
<span id="cb15-25"><a href="#cb15-25" tabindex="-1"></a></span>
<span id="cb15-26"><a href="#cb15-26" tabindex="-1"></a><span class="co"># Arrow flow:</span></span>
<span id="cb15-27"><a href="#cb15-27" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb15-28"><a href="#cb15-28" tabindex="-1"></a></span>
<span id="cb15-29"><a href="#cb15-29" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;iris&quot;</span>, iris)</span>
<span id="cb15-30"><a href="#cb15-30" tabindex="-1"></a></span>
<span id="cb15-31"><a href="#cb15-31" tabindex="-1"></a><span class="co"># Using the same query for different values</span></span>
<span id="cb15-32"><a href="#cb15-32" tabindex="-1"></a>iris_result <span class="ot">&lt;-</span> <span class="fu">dbSendQueryArrow</span>(con, <span class="st">&quot;SELECT * FROM iris WHERE [Petal.Width] &gt; ?&quot;</span>)</span>
<span id="cb15-33"><a href="#cb15-33" tabindex="-1"></a><span class="fu">dbBindArrow</span>(</span>
<span id="cb15-34"><a href="#cb15-34" tabindex="-1"></a>  iris_result,</span>
<span id="cb15-35"><a href="#cb15-35" tabindex="-1"></a>  nanoarrow<span class="sc">::</span><span class="fu">as_nanoarrow_array_stream</span>(<span class="fu">data.frame</span>(<span class="fl">2.3</span>, <span class="at">fix.empty.names =</span> <span class="cn">FALSE</span>))</span>
<span id="cb15-36"><a href="#cb15-36" tabindex="-1"></a>)</span>
<span id="cb15-37"><a href="#cb15-37" tabindex="-1"></a><span class="fu">as.data.frame</span>(<span class="fu">dbFetchArrow</span>(iris_result))</span>
<span id="cb15-38"><a href="#cb15-38" tabindex="-1"></a><span class="fu">dbBindArrow</span>(</span>
<span id="cb15-39"><a href="#cb15-39" tabindex="-1"></a>  iris_result,</span>
<span id="cb15-40"><a href="#cb15-40" tabindex="-1"></a>  nanoarrow<span class="sc">::</span><span class="fu">as_nanoarrow_array_stream</span>(<span class="fu">data.frame</span>(<span class="dv">3</span>, <span class="at">fix.empty.names =</span> <span class="cn">FALSE</span>))</span>
<span id="cb15-41"><a href="#cb15-41" tabindex="-1"></a>)</span>
<span id="cb15-42"><a href="#cb15-42" tabindex="-1"></a><span class="fu">as.data.frame</span>(<span class="fu">dbFetchArrow</span>(iris_result))</span>
<span id="cb15-43"><a href="#cb15-43" tabindex="-1"></a><span class="fu">dbClearResult</span>(iris_result)</span>
<span id="cb15-44"><a href="#cb15-44" tabindex="-1"></a></span>
<span id="cb15-45"><a href="#cb15-45" tabindex="-1"></a><span class="co"># Executing the same statement with different values at once</span></span>
<span id="cb15-46"><a href="#cb15-46" tabindex="-1"></a>iris_result <span class="ot">&lt;-</span> <span class="fu">dbSendStatement</span>(con, <span class="st">&quot;DELETE FROM iris WHERE [Species] = \$species&quot;</span>)</span>
<span id="cb15-47"><a href="#cb15-47" tabindex="-1"></a><span class="fu">dbBindArrow</span>(iris_result, nanoarrow<span class="sc">::</span><span class="fu">as_nanoarrow_array_stream</span>(<span class="fu">data.frame</span>(</span>
<span id="cb15-48"><a href="#cb15-48" tabindex="-1"></a>  <span class="at">species =</span> <span class="fu">c</span>(<span class="st">&quot;setosa&quot;</span>, <span class="st">&quot;versicolor&quot;</span>, <span class="st">&quot;unknown&quot;</span>)</span>
<span id="cb15-49"><a href="#cb15-49" tabindex="-1"></a>)))</span>
<span id="cb15-50"><a href="#cb15-50" tabindex="-1"></a><span class="fu">dbGetRowsAffected</span>(iris_result)</span>
<span id="cb15-51"><a href="#cb15-51" tabindex="-1"></a><span class="fu">dbClearResult</span>(iris_result)</span>
<span id="cb15-52"><a href="#cb15-52" tabindex="-1"></a></span>
<span id="cb15-53"><a href="#cb15-53" tabindex="-1"></a><span class="fu">nrow</span>(<span class="fu">dbReadTable</span>(con, <span class="st">&quot;iris&quot;</span>))</span>
<span id="cb15-54"><a href="#cb15-54" tabindex="-1"></a></span>
<span id="cb15-55"><a href="#cb15-55" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="retrieve-results-from-a-query" class="section level2">
<h2>Retrieve results from a query</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb16"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb16-1"><a href="#cb16-1" tabindex="-1"></a><span class="fu">dbGetQuery</span>(conn, statement, ...)</span></code></pre></div>
<div id="description-7" class="section level3">
<h3>Description</h3>
<p>Returns the result of a query as a data frame.
<code>dbGetQuery()</code> comes with a default implementation (which
should work with most backends) that calls <code>dbSendQuery()</code>,
then <code>dbFetch()</code>, ensuring that the result is always freed by
<code>dbClearResult()</code>. For retrieving chunked/paged results or
for passing query parameters, see <code>dbSendQuery()</code>, in
particular the “The data retrieval flow” section. For retrieving results
as an Arrow object, see <code>dbGetQueryArrow()</code>.</p>
</div>
<div id="arguments-7" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>conn</code></td>
<td>A DBIConnection object, as returned by
<code>dbConnect()</code>.</td>
</tr>
<tr class="even">
<td><code>statement</code></td>
<td>a character string containing SQL.</td>
</tr>
<tr class="odd">
<td><code>...</code></td>
<td>Other parameters passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="additional-arguments-1" class="section level3">
<h3>Additional arguments</h3>
<p>The following arguments are not part of the <code>dbGetQuery()</code>
generic (to improve compatibility across backends) but are part of the
DBI specification:</p>
<ul>
<li><p><code>n</code> (default: -1)</p></li>
<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” and
“Value” sections for details on their usage.</p>
</div>
<div id="specification-6" class="section level3">
<h3>Specification</h3>
<p>A column named <code>row_names</code> is treated like any other
column.</p>
<p>The <code>n</code> argument specifies the number of rows to be
fetched. If omitted, fetching multi-row queries with one or more columns
returns the entire result. A value of Inf for the <code>n</code>
argument is supported and also returns the full result. If more rows
than available are fetched (by passing a too large value for
<code>n</code>), the result is returned in full without warning. If zero
rows are requested, the columns of the data frame are still fully typed.
Fetching fewer rows than available is permitted, no warning is
issued.</p>
<p>The <code>param</code> argument allows passing query parameters, see
<code>dbBind()</code> for details.</p>
</div>
<div id="specification-for-the-immediate-argument-1" class="section level3">
<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 style="list-style-type: decimal">
<li><p>DBI backend defaults to <code>immediate = TRUE</code>
internally</p>
<ol style="list-style-type: decimal">
<li><p>A query without parameters is passed: query is executed</p></li>
<li><p>A query with parameters is passed:</p>
<ol style="list-style-type: decimal">
<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 style="list-style-type: decimal">
<li><p>A query without parameters is passed:</p>
<ol style="list-style-type: decimal">
<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 style="list-style-type: decimal">
<li><p><code>params</code> not given: waiting for parameters via
<code>dbBind()</code></p></li>
<li><p><code>params</code> given: query is executed</p></li>
</ol></li>
</ol></li>
</ol>
</div>
<div id="details-4" class="section level3">
<h3>Details</h3>
<p>This method is for <code>SELECT</code> queries only (incl. other SQL
statements that return a <code>SELECT</code>-alike result, e.g.,
execution of a stored procedure or data manipulation queries like
<code>⁠INSERT INTO ... RETURNING ...⁠</code>). To execute a stored
procedure that does not return a result set, use
<code>dbExecute()</code>.</p>
<p>Some backends may support data manipulation statements through this
method for compatibility reasons. However, callers are strongly advised
to use <code>dbExecute()</code> for data manipulation statements.</p>
</div>
<div id="value-7" class="section level3">
<h3>Value</h3>
<p><code>dbGetQuery()</code> always returns a data.frame, with as many
rows as records were fetched and as many columns as fields in the result
set, even if the result is a single value or has one or zero rows.</p>
</div>
<div id="implementation-notes" class="section level3">
<h3>Implementation notes</h3>
<p>Subclasses should override this method only if they provide some sort
of performance optimization.</p>
</div>
<div id="failure-modes-6" class="section level3">
<h3>Failure modes</h3>
<p>An error is raised when issuing a query over a closed or invalid
connection, if the syntax of the query is invalid, or if the query is
not a non-<code>NA</code> string. If the <code>n</code> argument is not
an atomic whole number greater or equal to -1 or Inf, an error is
raised, but a subsequent call to <code>dbGetQuery()</code> with proper
<code>n</code> argument succeeds.</p>
</div>
<div id="examples-8" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb17"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb17-1"><a href="#cb17-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb17-2"><a href="#cb17-2" tabindex="-1"></a></span>
<span id="cb17-3"><a href="#cb17-3" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;mtcars&quot;</span>, mtcars)</span>
<span id="cb17-4"><a href="#cb17-4" tabindex="-1"></a><span class="fu">dbGetQuery</span>(con, <span class="st">&quot;SELECT * FROM mtcars&quot;</span>)</span>
<span id="cb17-5"><a href="#cb17-5" tabindex="-1"></a><span class="fu">dbGetQuery</span>(con, <span class="st">&quot;SELECT * FROM mtcars&quot;</span>, <span class="at">n =</span> <span class="dv">6</span>)</span>
<span id="cb17-6"><a href="#cb17-6" tabindex="-1"></a></span>
<span id="cb17-7"><a href="#cb17-7" tabindex="-1"></a><span class="co"># Pass values using the param argument:</span></span>
<span id="cb17-8"><a href="#cb17-8" tabindex="-1"></a><span class="co"># (This query runs eight times, once for each different</span></span>
<span id="cb17-9"><a href="#cb17-9" tabindex="-1"></a><span class="co"># parameter. The resulting rows are combined into a single</span></span>
<span id="cb17-10"><a href="#cb17-10" tabindex="-1"></a><span class="co"># data frame.)</span></span>
<span id="cb17-11"><a href="#cb17-11" tabindex="-1"></a><span class="fu">dbGetQuery</span>(</span>
<span id="cb17-12"><a href="#cb17-12" tabindex="-1"></a>  con,</span>
<span id="cb17-13"><a href="#cb17-13" tabindex="-1"></a>  <span class="st">&quot;SELECT COUNT(*) FROM mtcars WHERE cyl = ?&quot;</span>,</span>
<span id="cb17-14"><a href="#cb17-14" tabindex="-1"></a>  <span class="at">params =</span> <span class="fu">list</span>(<span class="dv">1</span><span class="sc">:</span><span class="dv">8</span>)</span>
<span id="cb17-15"><a href="#cb17-15" tabindex="-1"></a>)</span>
<span id="cb17-16"><a href="#cb17-16" tabindex="-1"></a></span>
<span id="cb17-17"><a href="#cb17-17" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="execute-a-data-manipulation-statement-on-a-given-database-connection" class="section level2">
<h2>Execute a data manipulation statement on a given database
connection</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb18"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb18-1"><a href="#cb18-1" tabindex="-1"></a><span class="fu">dbSendStatement</span>(conn, statement, ...)</span></code></pre></div>
<div id="description-8" class="section level3">
<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>⁠INSERT INTO⁠</code>,
<code>⁠DROP TABLE⁠</code>, …) to the database engine. To query the number
of affected rows, call <code>dbGetRowsAffected()</code> on the returned
result object. You must also call <code>dbClearResult()</code> after
that. For interactive use, you should almost always prefer
<code>dbExecute()</code>.</p>
</div>
<div id="arguments-8" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>conn</code></td>
<td>A DBIConnection object, as returned by
<code>dbConnect()</code>.</td>
</tr>
<tr class="even">
<td><code>statement</code></td>
<td>a character string containing SQL.</td>
</tr>
<tr class="odd">
<td><code>...</code></td>
<td>Other parameters passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="additional-arguments-2" class="section level3">
<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>
</div>
<div id="specification-7" class="section level3">
<h3>Specification</h3>
<p>No warnings occur under normal conditions. When done, the DBIResult
object must be cleared with a call to <code>dbClearResult()</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>dbBind()</code> for details.</p>
</div>
<div id="specification-for-the-immediate-argument-2" class="section level3">
<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 style="list-style-type: decimal">
<li><p>DBI backend defaults to <code>immediate = TRUE</code>
internally</p>
<ol style="list-style-type: decimal">
<li><p>A query without parameters is passed: query is executed</p></li>
<li><p>A query with parameters is passed:</p>
<ol style="list-style-type: decimal">
<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 style="list-style-type: decimal">
<li><p>A query without parameters is passed:</p>
<ol style="list-style-type: decimal">
<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 style="list-style-type: decimal">
<li><p><code>params</code> not given: waiting for parameters via
<code>dbBind()</code></p></li>
<li><p><code>params</code> given: query is executed</p></li>
</ol></li>
</ol></li>
</ol>
</div>
<div id="details-5" class="section level3">
<h3>Details</h3>
<p><code>dbSendStatement()</code> comes with a default implementation
that simply forwards to <code>dbSendQuery()</code>, to support backends
that only implement the latter.</p>
</div>
<div id="value-8" class="section level3">
<h3>Value</h3>
<p><code>dbSendStatement()</code> returns an S4 object that inherits
from DBIResult. The result set can be used with
<code>dbGetRowsAffected()</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>dbClearResult()</code>.</p>
</div>
<div id="the-command-execution-flow-2" class="section level3">
<h3>The command execution flow</h3>
<p>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
<code>dbBindArrow()</code>, is implemented by <code>dbExecute()</code>,
which should be sufficient for non-parameterized queries. This flow
requires an active connection established by <code>dbConnect()</code>.
See also <code>vignette(&quot;dbi-advanced&quot;)</code> for a walkthrough.</p>
<ol style="list-style-type: decimal">
<li><p>Use <code>dbSendStatement()</code> to create a result set object
of class DBIResult. For some queries you need to pass
<code>immediate = TRUE</code>.</p></li>
<li><p>Optionally, bind query parameters with<code>dbBind()</code> or
<code>dbBindArrow()</code>. This is required only if the query contains
placeholders such as <code>⁠?⁠</code> or <code>⁠\$1⁠</code>, depending on
the database backend.</p></li>
<li><p>Optionally, use <code>dbGetRowsAffected()</code> to retrieve the
number of rows affected by the query.</p></li>
<li><p>Repeat the last two steps as necessary.</p></li>
<li><p>Use <code>dbClearResult()</code> 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 use
<code>on.exit()</code> or <code>withr::defer()</code> to ensure that
this step is always executed.</p></li>
</ol>
</div>
<div id="failure-modes-7" class="section level3">
<h3>Failure modes</h3>
<p>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>
</div>
<div id="examples-9" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb19"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb19-1"><a href="#cb19-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb19-2"><a href="#cb19-2" tabindex="-1"></a></span>
<span id="cb19-3"><a href="#cb19-3" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;cars&quot;</span>, <span class="fu">head</span>(cars, <span class="dv">3</span>))</span>
<span id="cb19-4"><a href="#cb19-4" tabindex="-1"></a></span>
<span id="cb19-5"><a href="#cb19-5" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendStatement</span>(</span>
<span id="cb19-6"><a href="#cb19-6" tabindex="-1"></a>  con,</span>
<span id="cb19-7"><a href="#cb19-7" tabindex="-1"></a>  <span class="st">&quot;INSERT INTO cars (speed, dist) VALUES (1, 1), (2, 2), (3, 3)&quot;</span></span>
<span id="cb19-8"><a href="#cb19-8" tabindex="-1"></a>)</span>
<span id="cb19-9"><a href="#cb19-9" tabindex="-1"></a><span class="fu">dbHasCompleted</span>(rs)</span>
<span id="cb19-10"><a href="#cb19-10" tabindex="-1"></a><span class="fu">dbGetRowsAffected</span>(rs)</span>
<span id="cb19-11"><a href="#cb19-11" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb19-12"><a href="#cb19-12" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;cars&quot;</span>)   <span class="co"># there are now 6 rows</span></span>
<span id="cb19-13"><a href="#cb19-13" tabindex="-1"></a></span>
<span id="cb19-14"><a href="#cb19-14" tabindex="-1"></a><span class="co"># Pass one set of values directly using the param argument:</span></span>
<span id="cb19-15"><a href="#cb19-15" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendStatement</span>(</span>
<span id="cb19-16"><a href="#cb19-16" tabindex="-1"></a>  con,</span>
<span id="cb19-17"><a href="#cb19-17" tabindex="-1"></a>  <span class="st">&quot;INSERT INTO cars (speed, dist) VALUES (?, ?)&quot;</span>,</span>
<span id="cb19-18"><a href="#cb19-18" tabindex="-1"></a>  <span class="at">params =</span> <span class="fu">list</span>(<span class="dv">4</span><span class="dt">L</span>, <span class="dv">5</span><span class="dt">L</span>)</span>
<span id="cb19-19"><a href="#cb19-19" tabindex="-1"></a>)</span>
<span id="cb19-20"><a href="#cb19-20" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb19-21"><a href="#cb19-21" tabindex="-1"></a></span>
<span id="cb19-22"><a href="#cb19-22" tabindex="-1"></a><span class="co"># Pass multiple sets of values using dbBind():</span></span>
<span id="cb19-23"><a href="#cb19-23" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendStatement</span>(</span>
<span id="cb19-24"><a href="#cb19-24" tabindex="-1"></a>  con,</span>
<span id="cb19-25"><a href="#cb19-25" tabindex="-1"></a>  <span class="st">&quot;INSERT INTO cars (speed, dist) VALUES (?, ?)&quot;</span></span>
<span id="cb19-26"><a href="#cb19-26" tabindex="-1"></a>)</span>
<span id="cb19-27"><a href="#cb19-27" tabindex="-1"></a><span class="fu">dbBind</span>(rs, <span class="fu">list</span>(<span class="dv">5</span><span class="sc">:</span><span class="dv">6</span>, <span class="dv">6</span><span class="sc">:</span><span class="dv">7</span>))</span>
<span id="cb19-28"><a href="#cb19-28" tabindex="-1"></a><span class="fu">dbBind</span>(rs, <span class="fu">list</span>(<span class="dv">7</span><span class="dt">L</span>, <span class="dv">8</span><span class="dt">L</span>))</span>
<span id="cb19-29"><a href="#cb19-29" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb19-30"><a href="#cb19-30" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;cars&quot;</span>)   <span class="co"># there are now 10 rows</span></span>
<span id="cb19-31"><a href="#cb19-31" tabindex="-1"></a></span>
<span id="cb19-32"><a href="#cb19-32" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="change-database-state" class="section level2">
<h2>Change database state</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb20"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb20-1"><a href="#cb20-1" tabindex="-1"></a><span class="fu">dbExecute</span>(conn, statement, ...)</span></code></pre></div>
<div id="description-9" class="section level3">
<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>dbSendStatement()</code>, then <code>dbGetRowsAffected()</code>,
ensuring that the result is always freed by
<code>dbClearResult()</code>. For passing query parameters, see
<code>dbBind()</code>, in particular the “The command execution flow”
section.</p>
</div>
<div id="arguments-9" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>conn</code></td>
<td>A DBIConnection object, as returned by
<code>dbConnect()</code>.</td>
</tr>
<tr class="even">
<td><code>statement</code></td>
<td>a character string containing SQL.</td>
</tr>
<tr class="odd">
<td><code>...</code></td>
<td>Other parameters passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="additional-arguments-3" class="section level3">
<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>
</div>
<div id="specification-8" class="section level3">
<h3>Specification</h3>
<p>The <code>param</code> argument allows passing query parameters, see
<code>dbBind()</code> for details.</p>
</div>
<div id="specification-for-the-immediate-argument-3" class="section level3">
<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 style="list-style-type: decimal">
<li><p>DBI backend defaults to <code>immediate = TRUE</code>
internally</p>
<ol style="list-style-type: decimal">
<li><p>A query without parameters is passed: query is executed</p></li>
<li><p>A query with parameters is passed:</p>
<ol style="list-style-type: decimal">
<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 style="list-style-type: decimal">
<li><p>A query without parameters is passed:</p>
<ol style="list-style-type: decimal">
<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 style="list-style-type: decimal">
<li><p><code>params</code> not given: waiting for parameters via
<code>dbBind()</code></p></li>
<li><p><code>params</code> given: query is executed</p></li>
</ol></li>
</ol></li>
</ol>
</div>
<div id="details-6" class="section level3">
<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, or
a data manipulation query that also returns a result set such as
<code>⁠INSERT INTO ... RETURNING ...⁠</code>, use
<code>dbGetQuery()</code> instead.</p>
</div>
<div id="value-9" class="section level3">
<h3>Value</h3>
<p><code>dbExecute()</code> always returns a scalar numeric that
specifies the number of rows affected by the statement.</p>
</div>
<div id="implementation-notes-1" class="section level3">
<h3>Implementation notes</h3>
<p>Subclasses should override this method only if they provide some sort
of performance optimization.</p>
</div>
<div id="failure-modes-8" class="section level3">
<h3>Failure modes</h3>
<p>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>
</div>
<div id="examples-10" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb21"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb21-1"><a href="#cb21-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb21-2"><a href="#cb21-2" tabindex="-1"></a></span>
<span id="cb21-3"><a href="#cb21-3" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;cars&quot;</span>, <span class="fu">head</span>(cars, <span class="dv">3</span>))</span>
<span id="cb21-4"><a href="#cb21-4" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;cars&quot;</span>)   <span class="co"># there are 3 rows</span></span>
<span id="cb21-5"><a href="#cb21-5" tabindex="-1"></a><span class="fu">dbExecute</span>(</span>
<span id="cb21-6"><a href="#cb21-6" tabindex="-1"></a>  con,</span>
<span id="cb21-7"><a href="#cb21-7" tabindex="-1"></a>  <span class="st">&quot;INSERT INTO cars (speed, dist) VALUES (1, 1), (2, 2), (3, 3)&quot;</span></span>
<span id="cb21-8"><a href="#cb21-8" tabindex="-1"></a>)</span>
<span id="cb21-9"><a href="#cb21-9" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;cars&quot;</span>)   <span class="co"># there are now 6 rows</span></span>
<span id="cb21-10"><a href="#cb21-10" tabindex="-1"></a></span>
<span id="cb21-11"><a href="#cb21-11" tabindex="-1"></a><span class="co"># Pass values using the param argument:</span></span>
<span id="cb21-12"><a href="#cb21-12" tabindex="-1"></a><span class="fu">dbExecute</span>(</span>
<span id="cb21-13"><a href="#cb21-13" tabindex="-1"></a>  con,</span>
<span id="cb21-14"><a href="#cb21-14" tabindex="-1"></a>  <span class="st">&quot;INSERT INTO cars (speed, dist) VALUES (?, ?)&quot;</span>,</span>
<span id="cb21-15"><a href="#cb21-15" tabindex="-1"></a>  <span class="at">params =</span> <span class="fu">list</span>(<span class="dv">4</span><span class="sc">:</span><span class="dv">7</span>, <span class="dv">5</span><span class="sc">:</span><span class="dv">8</span>)</span>
<span id="cb21-16"><a href="#cb21-16" tabindex="-1"></a>)</span>
<span id="cb21-17"><a href="#cb21-17" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;cars&quot;</span>)   <span class="co"># there are now 10 rows</span></span>
<span id="cb21-18"><a href="#cb21-18" tabindex="-1"></a></span>
<span id="cb21-19"><a href="#cb21-19" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="quote-literal-strings" class="section level2">
<h2>Quote literal strings</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb22"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb22-1"><a href="#cb22-1" tabindex="-1"></a><span class="fu">dbQuoteString</span>(conn, x, ...)</span></code></pre></div>
<div id="description-10" class="section level3">
<h3>Description</h3>
<p>Call this method to generate a string that is suitable for use in a
query as a string literal, to make sure that you generate valid SQL and
protect against SQL injection attacks.</p>
</div>
<div id="arguments-10" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>conn</code></td>
<td>A DBIConnection object, as returned by
<code>dbConnect()</code>.</td>
</tr>
<tr class="even">
<td><code>x</code></td>
<td>A character vector to quote as string.</td>
</tr>
<tr class="odd">
<td><code>...</code></td>
<td>Other arguments passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="value-10" class="section level3">
<h3>Value</h3>
<p><code>dbQuoteString()</code> returns an object that can be coerced to
character, of the same length as the input. For an empty character
vector this function returns a length-0 object.</p>
<p>When passing the returned object again to
<code>dbQuoteString()</code> as <code>x</code> argument, it is returned
unchanged. Passing objects of class SQL should also return them
unchanged. (For backends it may be most convenient to return SQL objects
to achieve this behavior, but this is not required.)</p>
</div>
<div id="failure-modes-9" class="section level3">
<h3>Failure modes</h3>
<p>Passing a numeric, integer, logical, or raw vector, or a list for the
<code>x</code> argument raises an error.</p>
</div>
<div id="specification-9" class="section level3">
<h3>Specification</h3>
<p>The returned expression can be used in a <code>⁠SELECT ...⁠</code>
query, and for any scalar character <code>x</code> the value of
<code>dbGetQuery(paste0(&quot;SELECT &quot;, dbQuoteString(x)))[[1]]</code> must
be identical to <code>x</code>, even if <code>x</code> contains spaces,
tabs, quotes (single or double), backticks, or newlines (in any
combination) or is itself the result of a <code>dbQuoteString()</code>
call coerced back to character (even repeatedly). If <code>x</code> is
<code>NA</code>, the result must merely satisfy <code>is.na()</code>.
The strings <code>&quot;NA&quot;</code> or <code>&quot;NULL&quot;</code> are not treated
specially.</p>
<p><code>NA</code> should be translated to an unquoted SQL
<code>NULL</code>, so that the query
<code>⁠SELECT * FROM (SELECT 1) a WHERE ... IS NULL⁠</code> returns one
row.</p>
</div>
<div id="examples-11" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb23"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb23-1"><a href="#cb23-1" tabindex="-1"></a><span class="co"># Quoting ensures that arbitrary input is safe for use in a query</span></span>
<span id="cb23-2"><a href="#cb23-2" tabindex="-1"></a>name <span class="ot">&lt;-</span> <span class="st">&quot;Robert&#39;); DROP TABLE Students;--&quot;</span></span>
<span id="cb23-3"><a href="#cb23-3" tabindex="-1"></a><span class="fu">dbQuoteString</span>(<span class="fu">ANSI</span>(), name)</span>
<span id="cb23-4"><a href="#cb23-4" tabindex="-1"></a></span>
<span id="cb23-5"><a href="#cb23-5" tabindex="-1"></a><span class="co"># NAs become NULL</span></span>
<span id="cb23-6"><a href="#cb23-6" tabindex="-1"></a><span class="fu">dbQuoteString</span>(<span class="fu">ANSI</span>(), <span class="fu">c</span>(<span class="st">&quot;x&quot;</span>, <span class="cn">NA</span>))</span>
<span id="cb23-7"><a href="#cb23-7" tabindex="-1"></a></span>
<span id="cb23-8"><a href="#cb23-8" tabindex="-1"></a><span class="co"># SQL vectors are always passed through as is</span></span>
<span id="cb23-9"><a href="#cb23-9" tabindex="-1"></a>var_name <span class="ot">&lt;-</span> <span class="fu">SQL</span>(<span class="st">&quot;select&quot;</span>)</span>
<span id="cb23-10"><a href="#cb23-10" tabindex="-1"></a>var_name</span>
<span id="cb23-11"><a href="#cb23-11" tabindex="-1"></a><span class="fu">dbQuoteString</span>(<span class="fu">ANSI</span>(), var_name)</span>
<span id="cb23-12"><a href="#cb23-12" tabindex="-1"></a></span>
<span id="cb23-13"><a href="#cb23-13" tabindex="-1"></a><span class="co"># This mechanism is used to prevent double escaping</span></span>
<span id="cb23-14"><a href="#cb23-14" tabindex="-1"></a><span class="fu">dbQuoteString</span>(<span class="fu">ANSI</span>(), <span class="fu">dbQuoteString</span>(<span class="fu">ANSI</span>(), name))</span></code></pre></div>
</div>
</div>
<div id="quote-identifiers" class="section level2">
<h2>Quote identifiers</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb24"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb24-1"><a href="#cb24-1" tabindex="-1"></a><span class="fu">dbQuoteIdentifier</span>(conn, x, ...)</span></code></pre></div>
<div id="description-11" class="section level3">
<h3>Description</h3>
<p>Call this method to generate a string that is suitable for use in a
query as a column or table name, to make sure that you generate valid
SQL and protect against SQL injection attacks. The inverse operation is
<code>dbUnquoteIdentifier()</code>.</p>
</div>
<div id="arguments-11" class="section level3">
<h3>Arguments</h3>
<table>
<colgroup>
<col width="11%" />
<col width="88%" />
</colgroup>
<tbody>
<tr class="odd">
<td><code>conn</code></td>
<td>A DBIConnection object, as returned by
<code>dbConnect()</code>.</td>
</tr>
<tr class="even">
<td><code>x</code></td>
<td>A character vector, SQL or Id object to quote as identifier.</td>
</tr>
<tr class="odd">
<td><code>...</code></td>
<td>Other arguments passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="value-11" class="section level3">
<h3>Value</h3>
<p><code>dbQuoteIdentifier()</code> returns an object that can be
coerced to character, of the same length as the input. For an empty
character vector this function returns a length-0 object. The names of
the input argument are preserved in the output. When passing the
returned object again to <code>dbQuoteIdentifier()</code> as
<code>x</code> argument, it is returned unchanged. Passing objects of
class SQL should also return them unchanged. (For backends it may be
most convenient to return SQL objects to achieve this behavior, but this
is not required.)</p>
</div>
<div id="failure-modes-10" class="section level3">
<h3>Failure modes</h3>
<p>An error is raised if the input contains <code>NA</code>, but not for
an empty string.</p>
</div>
<div id="specification-10" class="section level3">
<h3>Specification</h3>
<p>Calling <code>dbGetQuery()</code> for a query of the format
<code>⁠SELECT 1 AS ...⁠</code> returns a data frame with the identifier,
unquoted, as column name. Quoted identifiers can be used as table and
column names in SQL queries, in particular in queries like
<code>⁠SELECT 1 AS ...⁠</code> and
<code>⁠SELECT * FROM (SELECT 1) ...⁠</code>. The method must use a quoting
mechanism that is unambiguously different from the quoting mechanism
used for strings, so that a query like
<code>⁠SELECT ... FROM (SELECT 1 AS ...)⁠</code> throws an error if the
column names do not match.</p>
<p>The method can quote column names that contain special characters
such as a space, a dot, a comma, or quotes used to mark strings or
identifiers, if the database supports this. In any case, checking the
validity of the identifier should be performed only when executing a
query, and not by <code>dbQuoteIdentifier()</code>.</p>
</div>
<div id="examples-12" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb25"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb25-1"><a href="#cb25-1" tabindex="-1"></a><span class="co"># Quoting ensures that arbitrary input is safe for use in a query</span></span>
<span id="cb25-2"><a href="#cb25-2" tabindex="-1"></a>name <span class="ot">&lt;-</span> <span class="st">&quot;Robert&#39;); DROP TABLE Students;--&quot;</span></span>
<span id="cb25-3"><a href="#cb25-3" tabindex="-1"></a><span class="fu">dbQuoteIdentifier</span>(<span class="fu">ANSI</span>(), name)</span>
<span id="cb25-4"><a href="#cb25-4" tabindex="-1"></a></span>
<span id="cb25-5"><a href="#cb25-5" tabindex="-1"></a><span class="co"># Use Id() to specify other components such as the schema</span></span>
<span id="cb25-6"><a href="#cb25-6" tabindex="-1"></a>id_name <span class="ot">&lt;-</span> <span class="fu">Id</span>(<span class="at">schema =</span> <span class="st">&quot;schema_name&quot;</span>, <span class="at">table =</span> <span class="st">&quot;table_name&quot;</span>)</span>
<span id="cb25-7"><a href="#cb25-7" tabindex="-1"></a>id_name</span>
<span id="cb25-8"><a href="#cb25-8" tabindex="-1"></a><span class="fu">dbQuoteIdentifier</span>(<span class="fu">ANSI</span>(), id_name)</span>
<span id="cb25-9"><a href="#cb25-9" tabindex="-1"></a></span>
<span id="cb25-10"><a href="#cb25-10" tabindex="-1"></a><span class="co"># SQL vectors are always passed through as is</span></span>
<span id="cb25-11"><a href="#cb25-11" tabindex="-1"></a>var_name <span class="ot">&lt;-</span> <span class="fu">SQL</span>(<span class="st">&quot;select&quot;</span>)</span>
<span id="cb25-12"><a href="#cb25-12" tabindex="-1"></a>var_name</span>
<span id="cb25-13"><a href="#cb25-13" tabindex="-1"></a><span class="fu">dbQuoteIdentifier</span>(<span class="fu">ANSI</span>(), var_name)</span>
<span id="cb25-14"><a href="#cb25-14" tabindex="-1"></a></span>
<span id="cb25-15"><a href="#cb25-15" tabindex="-1"></a><span class="co"># This mechanism is used to prevent double escaping</span></span>
<span id="cb25-16"><a href="#cb25-16" tabindex="-1"></a><span class="fu">dbQuoteIdentifier</span>(<span class="fu">ANSI</span>(), <span class="fu">dbQuoteIdentifier</span>(<span class="fu">ANSI</span>(), name))</span></code></pre></div>
</div>
</div>
<div id="read-database-tables-as-data-frames" class="section level2">
<h2>Read database tables as data frames</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb26"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb26-1"><a href="#cb26-1" tabindex="-1"></a><span class="fu">dbReadTable</span>(conn, name, ...)</span></code></pre></div>
<div id="description-12" class="section level3">
<h3>Description</h3>
<p>Reads a database table to a data frame, optionally converting a
column to row names and converting the column names to valid R
identifiers. Use <code>dbReadTableArrow()</code> instead to obtain an
Arrow object.</p>
</div>
<div id="arguments-12" class="section level3">
<h3>Arguments</h3>
<table>
<colgroup>
<col style="width: 50%" />
<col style="width: 50%" />
</colgroup>
<tbody>
<tr class="odd">
<td>
<code id="dbReadTable_:_conn">conn</code>
</td>
<td>
<p>
A DBIConnection object, as returned by <code>dbConnect()</code>.
</p>
</td>
</tr>
<tr class="even">
<td>
<code id="dbReadTable_:_name">name</code>
</td>
<td>
<p>
The table name, passed on to <code>dbQuoteIdentifier()</code>. Options
are:
</p>
<ul>
<li>
<p>
a character string with the unquoted DBMS table name, e.g.
<code>“table_name”</code>,
</p>
</li>
<li>
<p>
a call to <code>Id()</code> with components to the fully qualified table
name, e.g. <code>Id(schema = “my_schema”, table = “table_name”)</code>
</p>
</li>
<li>
<p>
a call to <code>SQL()</code> with the quoted and fully qualified table
name given verbatim, e.g. <code>SQL(‘“my_schema”.”table_name”’)</code>
</p>
</li>
</ul>
</td>
</tr>
<tr class="odd">
<td>
<code id="dbReadTable_:_...">…</code>
</td>
<td>
<p>
Other parameters passed on to methods.
</p>
</td>
</tr>
</tbody>
</table>
</div>
<div id="additional-arguments-4" class="section level3">
<h3>Additional arguments</h3>
<p>The following arguments are not part of the
<code>dbReadTable()</code> generic (to improve compatibility across
backends) but are part of the DBI specification:</p>
<ul>
<li><p><code>row.names</code> (default: <code>FALSE</code>)</p></li>
<li><p><code>check.names</code></p></li>
</ul>
<p>They must be provided as named arguments. See the “Value” section for
details on their usage.</p>
</div>
<div id="specification-11" class="section level3">
<h3>Specification</h3>
<p>The <code>name</code> argument is processed as follows, to support
databases that allow non-syntactic names for their objects:</p>
<ul>
<li><p>If an unquoted table name as string: <code>dbReadTable()</code>
will do the quoting, perhaps by calling
<code>dbQuoteIdentifier(conn, x = name)</code></p></li>
<li><p>If the result of a call to <code>dbQuoteIdentifier()</code>: no
more quoting is done</p></li>
</ul>
</div>
<div id="details-7" class="section level3">
<h3>Details</h3>
<p>This function returns a data frame. Use
<code>dbReadTableArrow()</code> to obtain an Arrow object.</p>
</div>
<div id="value-12" class="section level3">
<h3>Value</h3>
<p><code>dbReadTable()</code> returns a data frame that contains the
complete data from the remote table, effectively the result of calling
<code>dbGetQuery()</code> with
<code>⁠SELECT * FROM &lt;name&gt;⁠</code>.</p>
<p>An empty table is returned as a data frame with zero rows.</p>
<p>The presence of rownames depends on the <code>row.names</code>
argument, see <code>sqlColumnToRownames()</code> for details:</p>
<ul>
<li><p>If <code>FALSE</code> or <code>NULL</code>, the returned data
frame doesn’t have row names.</p></li>
<li><p>If <code>TRUE</code>, a column named “row_names” is converted to
row names.</p></li>
</ul>
<!-- -->
<ul>
<li><p>If <code>NA</code>, a column named “row_names” is converted to
row names if it exists, otherwise no translation occurs.</p></li>
<li><p>If a string, this specifies the name of the column in the remote
table that contains the row names.</p></li>
</ul>
<p>The default is <code>row.names = FALSE</code>.</p>
<p>If the database supports identifiers with special characters, the
columns in the returned data frame are converted to valid R identifiers
if the <code>check.names</code> argument is <code>TRUE</code>, If
<code>check.names = FALSE</code>, the returned table has non-syntactic
column names without quotes.</p>
</div>
<div id="failure-modes-11" class="section level3">
<h3>Failure modes</h3>
<p>An error is raised if the table does not exist.</p>
<p>An error is raised if <code>row.names</code> is <code>TRUE</code> and
no “row_names” column exists,</p>
<p>An error is raised if <code>row.names</code> is set to a string and
no corresponding column exists.</p>
<p>An error is raised when calling this method for a closed or invalid
connection. An error is raised if <code>name</code> cannot be processed
with <code>dbQuoteIdentifier()</code> or if this results in a
non-scalar. Unsupported values for <code>row.names</code> and
<code>check.names</code> (non-scalars, unsupported data types,
<code>NA</code> for <code>check.names</code>) also raise an error.</p>
</div>
<div id="examples-13" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb27"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb27-1"><a href="#cb27-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb27-2"><a href="#cb27-2" tabindex="-1"></a></span>
<span id="cb27-3"><a href="#cb27-3" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;mtcars&quot;</span>, mtcars[<span class="dv">1</span><span class="sc">:</span><span class="dv">10</span>, ])</span>
<span id="cb27-4"><a href="#cb27-4" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;mtcars&quot;</span>)</span>
<span id="cb27-5"><a href="#cb27-5" tabindex="-1"></a></span>
<span id="cb27-6"><a href="#cb27-6" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="copy-data-frames-to-database-tables" class="section level2">
<h2>Copy data frames to database tables</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb28"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb28-1"><a href="#cb28-1" tabindex="-1"></a><span class="fu">dbWriteTable</span>(conn, name, value, ...)</span></code></pre></div>
<div id="description-13" class="section level3">
<h3>Description</h3>
<p>Writes, overwrites or appends a data frame to a database table,
optionally converting row names to a column and specifying SQL data
types for fields.</p>
</div>
<div id="arguments-13" class="section level3">
<h3>Arguments</h3>
<table>
<colgroup>
<col style="width: 50%" />
<col style="width: 50%" />
</colgroup>
<tbody>
<tr class="odd">
<td>
<code id="dbWriteTable_:_conn">conn</code>
</td>
<td>
<p>
A DBIConnection object, as returned by <code>dbConnect()</code>.
</p>
</td>
</tr>
<tr class="even">
<td>
<code id="dbWriteTable_:_name">name</code>
</td>
<td>
<p>
The table name, passed on to <code>dbQuoteIdentifier()</code>. Options
are:
</p>
<ul>
<li>
<p>
a character string with the unquoted DBMS table name, e.g.
<code>“table_name”</code>,
</p>
</li>
<li>
<p>
a call to <code>Id()</code> with components to the fully qualified table
name, e.g. <code>Id(schema = “my_schema”, table = “table_name”)</code>
</p>
</li>
<li>
<p>
a call to <code>SQL()</code> with the quoted and fully qualified table
name given verbatim, e.g. <code>SQL(‘“my_schema”.”table_name”’)</code>
</p>
</li>
</ul>
</td>
</tr>
<tr class="odd">
<td>
<code id="dbWriteTable_:_value">value</code>
</td>
<td>
<p>
A data.frame (or coercible to data.frame).
</p>
</td>
</tr>
<tr class="even">
<td>
<code id="dbWriteTable_:_...">…</code>
</td>
<td>
<p>
Other parameters passed on to methods.
</p>
</td>
</tr>
</tbody>
</table>
</div>
<div id="additional-arguments-5" class="section level3">
<h3>Additional arguments</h3>
<p>The following arguments are not part of the
<code>dbWriteTable()</code> generic (to improve compatibility across
backends) but are part of the DBI specification:</p>
<ul>
<li><p><code>row.names</code> (default: <code>FALSE</code>)</p></li>
<li><p><code>overwrite</code> (default: <code>FALSE</code>)</p></li>
<li><p><code>append</code> (default: <code>FALSE</code>)</p></li>
<li><p><code>field.types</code> (default: <code>NULL</code>)</p></li>
<li><p><code>temporary</code> (default: <code>FALSE</code>)</p></li>
</ul>
<p>They must be provided as named arguments. See the “Specification” and
“Value” sections for details on their usage.</p>
</div>
<div id="specification-12" class="section level3">
<h3>Specification</h3>
<p>The <code>name</code> argument is processed as follows, to support
databases that allow non-syntactic names for their objects:</p>
<ul>
<li><p>If an unquoted table name as string: <code>dbWriteTable()</code>
will do the quoting, perhaps by calling
<code>dbQuoteIdentifier(conn, x = name)</code></p></li>
<li><p>If the result of a call to <code>dbQuoteIdentifier()</code>: no
more quoting is done</p></li>
</ul>
<p>The <code>value</code> argument must be a data frame with a subset of
the columns of the existing table if <code>append = TRUE</code>. The
order of the columns does not matter with
<code>append = TRUE</code>.</p>
<p>If the <code>overwrite</code> argument is <code>TRUE</code>, an
existing table of the same name will be overwritten. This argument
doesn’t change behavior if the table does not exist yet.</p>
<p>If the <code>append</code> argument is <code>TRUE</code>, the rows in
an existing table are preserved, and the new data are appended. If the
table doesn’t exist yet, it is created.</p>
<p>If the <code>temporary</code> argument is <code>TRUE</code>, the
table is not available in a second connection and is gone after
reconnecting. Not all backends support this argument. A regular,
non-temporary table is visible in a second connection, in a pre-existing
connection, and after reconnecting to the database.</p>
<p>SQL keywords can be used freely in table names, column names, and
data. Quotes, commas, spaces, and other special characters such as
newlines and tabs, can also be used in the data, and, if the database
supports non-syntactic identifiers, also for table names and column
names.</p>
<p>The following data types must be supported at least, and be read
identically with <code>dbReadTable()</code>:</p>
<ul>
<li><p>integer</p></li>
<li><p>numeric (the behavior for <code>Inf</code> and <code>NaN</code>
is not specified)</p></li>
<li><p>logical</p></li>
<li><p><code>NA</code> as NULL</p></li>
<li><p>64-bit values (using <code>&quot;bigint&quot;</code> as field type); the
result can be</p>
<ul>
<li><p>converted to a numeric, which may lose precision,</p></li>
<li><p>converted a character vector, which gives the full decimal
representation</p></li>
<li><p>written to another table and read again unchanged</p></li>
</ul></li>
<li><p>character (in both UTF-8 and native encodings), supporting empty
strings before and after a non-empty string</p></li>
<li><p>factor (returned as character)</p></li>
<li><p>list of raw (if supported by the database)</p></li>
<li><p>objects of type <a href="blob::blob" class="uri">blob::blob</a>
(if supported by the database)</p></li>
<li><p>date (if supported by the database; returned as
<code>Date</code>), also for dates prior to 1970 or 1900 or after
2038</p></li>
<li><p>time (if supported by the database; returned as objects that
inherit from <code>difftime</code>)</p></li>
<li><p>timestamp (if supported by the database; returned as
<code>POSIXct</code> respecting the time zone but not necessarily
preserving the input time zone), also for timestamps prior to 1970 or
1900 or after 2038 respecting the time zone but not necessarily
preserving the input time zone)</p></li>
</ul>
<p>Mixing column types in the same table is supported.</p>
<p>The <code>field.types</code> argument must be a named character
vector with at most one entry for each column. It indicates the SQL data
type to be used for a new column. If a column is missed from
<code>field.types</code>, the type is inferred from the input data with
<code>dbDataType()</code>.</p>
<p>The interpretation of rownames depends on the <code>row.names</code>
argument, see <code>sqlRownamesToColumn()</code> for details:</p>
<ul>
<li><p>If <code>FALSE</code> or <code>NULL</code>, row names are
ignored.</p></li>
<li><p>If <code>TRUE</code>, row names are converted to a column named
“row_names”, even if the input data frame only has natural row names
from 1 to <code>nrow(...)</code>.</p></li>
<li><p>If <code>NA</code>, a column named “row_names” is created if the
data has custom row names, no extra column is created in the case of
natural row names.</p></li>
<li><p>If a string, this specifies the name of the column in the remote
table that contains the row names, even if the input data frame only has
natural row names.</p></li>
</ul>
<p>The default is <code>row.names = FALSE</code>.</p>
</div>
<div id="details-8" class="section level3">
<h3>Details</h3>
<p>This function expects a data frame. Use
<code>dbWriteTableArrow()</code> to write an Arrow object.</p>
<p>This function is useful if you want to create and load a table at the
same time. Use <code>dbAppendTable()</code> or
<code>dbAppendTableArrow()</code> for appending data to an existing
table, <code>dbCreateTable()</code> or <code>dbCreateTableArrow()</code>
for creating a table, and <code>dbExistsTable()</code> and
<code>dbRemoveTable()</code> for overwriting tables.</p>
<p>DBI only standardizes writing data frames with
<code>dbWriteTable()</code>. Some backends might implement methods that
can consume CSV files or other data formats. For details, see the
documentation for the individual methods.</p>
</div>
<div id="value-13" class="section level3">
<h3>Value</h3>
<p><code>dbWriteTable()</code> returns <code>TRUE</code>, invisibly.</p>
</div>
<div id="failure-modes-12" class="section level3">
<h3>Failure modes</h3>
<p>If the table exists, and both <code>append</code> and
<code>overwrite</code> arguments are unset, or
<code>append = TRUE</code> and the data frame with the new data has
different column names, an error is raised; the remote table remains
unchanged.</p>
<p>An error is raised when calling this method for a closed or invalid
connection. An error is also raised if <code>name</code> cannot be
processed with <code>dbQuoteIdentifier()</code> or if this results in a
non-scalar. Invalid values for the additional arguments
<code>row.names</code>, <code>overwrite</code>, <code>append</code>,
<code>field.types</code>, and <code>temporary</code> (non-scalars,
unsupported data types, <code>NA</code>, incompatible values, duplicate
or missing names, incompatible columns) also raise an error.</p>
</div>
<div id="examples-14" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb29"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb29-1"><a href="#cb29-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb29-2"><a href="#cb29-2" tabindex="-1"></a></span>
<span id="cb29-3"><a href="#cb29-3" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;mtcars&quot;</span>, mtcars[<span class="dv">1</span><span class="sc">:</span><span class="dv">5</span>, ])</span>
<span id="cb29-4"><a href="#cb29-4" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;mtcars&quot;</span>)</span>
<span id="cb29-5"><a href="#cb29-5" tabindex="-1"></a></span>
<span id="cb29-6"><a href="#cb29-6" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;mtcars&quot;</span>, mtcars[<span class="dv">6</span><span class="sc">:</span><span class="dv">10</span>, ], <span class="at">append =</span> <span class="cn">TRUE</span>)</span>
<span id="cb29-7"><a href="#cb29-7" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;mtcars&quot;</span>)</span>
<span id="cb29-8"><a href="#cb29-8" tabindex="-1"></a></span>
<span id="cb29-9"><a href="#cb29-9" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;mtcars&quot;</span>, mtcars[<span class="dv">1</span><span class="sc">:</span><span class="dv">10</span>, ], <span class="at">overwrite =</span> <span class="cn">TRUE</span>)</span>
<span id="cb29-10"><a href="#cb29-10" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;mtcars&quot;</span>)</span>
<span id="cb29-11"><a href="#cb29-11" tabindex="-1"></a></span>
<span id="cb29-12"><a href="#cb29-12" tabindex="-1"></a><span class="co"># No row names</span></span>
<span id="cb29-13"><a href="#cb29-13" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;mtcars&quot;</span>, mtcars[<span class="dv">1</span><span class="sc">:</span><span class="dv">10</span>, ], <span class="at">overwrite =</span> <span class="cn">TRUE</span>, <span class="at">row.names =</span> <span class="cn">FALSE</span>)</span>
<span id="cb29-14"><a href="#cb29-14" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;mtcars&quot;</span>)</span></code></pre></div>
</div>
</div>
<div id="list-remote-tables" class="section level2">
<h2>List remote tables</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb30"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb30-1"><a href="#cb30-1" tabindex="-1"></a><span class="fu">dbListTables</span>(conn, ...)</span></code></pre></div>
<div id="description-14" class="section level3">
<h3>Description</h3>
<p>Returns the unquoted names of remote tables accessible through this
connection. This should include views and temporary objects, but not all
database backends (in particular <span class="pkg">RMariaDB</span> and
<span class="pkg">RMySQL</span>) support this.</p>
</div>
<div id="arguments-14" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>conn</code></td>
<td>A DBIConnection object, as returned by
<code>dbConnect()</code>.</td>
</tr>
<tr class="even">
<td><code>...</code></td>
<td>Other parameters passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="value-14" class="section level3">
<h3>Value</h3>
<p><code>dbListTables()</code> returns a character vector that
enumerates all tables and views in the database. Tables added with
<code>dbWriteTable()</code> are part of the list. As soon a table is
removed from the database, it is also removed from the list of database
tables.</p>
<p>The same applies to temporary tables if supported by the
database.</p>
<p>The returned names are suitable for quoting with
<code>dbQuoteIdentifier()</code>.</p>
</div>
<div id="failure-modes-13" class="section level3">
<h3>Failure modes</h3>
<p>An error is raised when calling this method for a closed or invalid
connection.</p>
</div>
<div id="examples-15" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb31"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb31-1"><a href="#cb31-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb31-2"><a href="#cb31-2" tabindex="-1"></a></span>
<span id="cb31-3"><a href="#cb31-3" tabindex="-1"></a><span class="fu">dbListTables</span>(con)</span>
<span id="cb31-4"><a href="#cb31-4" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;mtcars&quot;</span>, mtcars)</span>
<span id="cb31-5"><a href="#cb31-5" tabindex="-1"></a><span class="fu">dbListTables</span>(con)</span>
<span id="cb31-6"><a href="#cb31-6" tabindex="-1"></a></span>
<span id="cb31-7"><a href="#cb31-7" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="does-a-table-exist" class="section level2">
<h2>Does a table exist?</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb32"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb32-1"><a href="#cb32-1" tabindex="-1"></a><span class="fu">dbExistsTable</span>(conn, name, ...)</span></code></pre></div>
<div id="description-15" class="section level3">
<h3>Description</h3>
<p>Returns if a table given by name exists in the database.</p>
</div>
<div id="arguments-15" class="section level3">
<h3>Arguments</h3>
<table>
<colgroup>
<col style="width: 50%" />
<col style="width: 50%" />
</colgroup>
<tbody>
<tr class="odd">
<td>
<code id="dbExistsTable_:_conn">conn</code>
</td>
<td>
<p>
A DBIConnection object, as returned by <code>dbConnect()</code>.
</p>
</td>
</tr>
<tr class="even">
<td>
<code id="dbExistsTable_:_name">name</code>
</td>
<td>
<p>
The table name, passed on to <code>dbQuoteIdentifier()</code>. Options
are:
</p>
<ul>
<li>
<p>
a character string with the unquoted DBMS table name, e.g.
<code>“table_name”</code>,
</p>
</li>
<li>
<p>
a call to <code>Id()</code> with components to the fully qualified table
name, e.g. <code>Id(schema = “my_schema”, table = “table_name”)</code>
</p>
</li>
<li>
<p>
a call to <code>SQL()</code> with the quoted and fully qualified table
name given verbatim, e.g. <code>SQL(‘“my_schema”.”table_name”’)</code>
</p>
</li>
</ul>
</td>
</tr>
<tr class="odd">
<td>
<code id="dbExistsTable_:_...">…</code>
</td>
<td>
<p>
Other parameters passed on to methods.
</p>
</td>
</tr>
</tbody>
</table>
</div>
<div id="value-15" class="section level3">
<h3>Value</h3>
<p><code>dbExistsTable()</code> returns a logical scalar,
<code>TRUE</code> if the table or view specified by the
<code>name</code> argument exists, <code>FALSE</code> otherwise.</p>
<p>This includes temporary tables if supported by the database.</p>
</div>
<div id="failure-modes-14" class="section level3">
<h3>Failure modes</h3>
<p>An error is raised when calling this method for a closed or invalid
connection. An error is also raised if <code>name</code> cannot be
processed with <code>dbQuoteIdentifier()</code> or if this results in a
non-scalar.</p>
</div>
<div id="specification-13" class="section level3">
<h3>Specification</h3>
<p>The <code>name</code> argument is processed as follows, to support
databases that allow non-syntactic names for their objects:</p>
<ul>
<li><p>If an unquoted table name as string: <code>dbExistsTable()</code>
will do the quoting, perhaps by calling
<code>dbQuoteIdentifier(conn, x = name)</code></p></li>
<li><p>If the result of a call to <code>dbQuoteIdentifier()</code>: no
more quoting is done</p></li>
</ul>
<p>For all tables listed by <code>dbListTables()</code>,
<code>dbExistsTable()</code> returns <code>TRUE</code>.</p>
</div>
<div id="examples-16" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb33"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb33-1"><a href="#cb33-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb33-2"><a href="#cb33-2" tabindex="-1"></a></span>
<span id="cb33-3"><a href="#cb33-3" tabindex="-1"></a><span class="fu">dbExistsTable</span>(con, <span class="st">&quot;iris&quot;</span>)</span>
<span id="cb33-4"><a href="#cb33-4" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;iris&quot;</span>, iris)</span>
<span id="cb33-5"><a href="#cb33-5" tabindex="-1"></a><span class="fu">dbExistsTable</span>(con, <span class="st">&quot;iris&quot;</span>)</span>
<span id="cb33-6"><a href="#cb33-6" tabindex="-1"></a></span>
<span id="cb33-7"><a href="#cb33-7" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="remove-a-table-from-the-database" class="section level2">
<h2>Remove a table from the database</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb34"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb34-1"><a href="#cb34-1" tabindex="-1"></a><span class="fu">dbRemoveTable</span>(conn, name, ...)</span></code></pre></div>
<div id="description-16" class="section level3">
<h3>Description</h3>
<p>Remove a remote table (e.g., created by <code>dbWriteTable()</code>)
from the database.</p>
</div>
<div id="arguments-16" class="section level3">
<h3>Arguments</h3>
<table>
<colgroup>
<col style="width: 50%" />
<col style="width: 50%" />
</colgroup>
<tbody>
<tr class="odd">
<td>
<code id="dbRemoveTable_:_conn">conn</code>
</td>
<td>
<p>
A DBIConnection object, as returned by <code>dbConnect()</code>.
</p>
</td>
</tr>
<tr class="even">
<td>
<code id="dbRemoveTable_:_name">name</code>
</td>
<td>
<p>
The table name, passed on to <code>dbQuoteIdentifier()</code>. Options
are:
</p>
<ul>
<li>
<p>
a character string with the unquoted DBMS table name, e.g.
<code>“table_name”</code>,
</p>
</li>
<li>
<p>
a call to <code>Id()</code> with components to the fully qualified table
name, e.g. <code>Id(schema = “my_schema”, table = “table_name”)</code>
</p>
</li>
<li>
<p>
a call to <code>SQL()</code> with the quoted and fully qualified table
name given verbatim, e.g. <code>SQL(‘“my_schema”.”table_name”’)</code>
</p>
</li>
</ul>
</td>
</tr>
<tr class="odd">
<td>
<code id="dbRemoveTable_:_...">…</code>
</td>
<td>
<p>
Other parameters passed on to methods.
</p>
</td>
</tr>
</tbody>
</table>
</div>
<div id="additional-arguments-6" class="section level3">
<h3>Additional arguments</h3>
<p>The following arguments are not part of the
<code>dbRemoveTable()</code> generic (to improve compatibility across
backends) but are part of the DBI specification:</p>
<ul>
<li><p><code>temporary</code> (default: <code>FALSE</code>)</p></li>
<li><p><code>fail_if_missing</code> (default:
<code>TRUE</code>)</p></li>
</ul>
<p>These arguments must be provided as named arguments.</p>
<p>If <code>temporary</code> is <code>TRUE</code>, the call to
<code>dbRemoveTable()</code> will consider only temporary tables. Not
all backends support this argument. In particular, permanent tables of
the same name are left untouched.</p>
<p>If <code>fail_if_missing</code> is <code>FALSE</code>, the call to
<code>dbRemoveTable()</code> succeeds if the table does not exist.</p>
</div>
<div id="specification-14" class="section level3">
<h3>Specification</h3>
<p>A table removed by <code>dbRemoveTable()</code> doesn’t appear in the
list of tables returned by <code>dbListTables()</code>, and
<code>dbExistsTable()</code> returns <code>FALSE</code>. The removal
propagates immediately to other connections to the same database. This
function can also be used to remove a temporary table.</p>
<p>The <code>name</code> argument is processed as follows, to support
databases that allow non-syntactic names for their objects:</p>
<ul>
<li><p>If an unquoted table name as string: <code>dbRemoveTable()</code>
will do the quoting, perhaps by calling
<code>dbQuoteIdentifier(conn, x = name)</code></p></li>
<li><p>If the result of a call to <code>dbQuoteIdentifier()</code>: no
more quoting is done</p></li>
</ul>
</div>
<div id="value-16" class="section level3">
<h3>Value</h3>
<p><code>dbRemoveTable()</code> returns <code>TRUE</code>,
invisibly.</p>
</div>
<div id="failure-modes-15" class="section level3">
<h3>Failure modes</h3>
<p>If the table does not exist, an error is raised. An attempt to remove
a view with this function may result in an error.</p>
<p>An error is raised when calling this method for a closed or invalid
connection. An error is also raised if <code>name</code> cannot be
processed with <code>dbQuoteIdentifier()</code> or if this results in a
non-scalar.</p>
</div>
<div id="examples-17" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb35"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb35-1"><a href="#cb35-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb35-2"><a href="#cb35-2" tabindex="-1"></a></span>
<span id="cb35-3"><a href="#cb35-3" tabindex="-1"></a><span class="fu">dbExistsTable</span>(con, <span class="st">&quot;iris&quot;</span>)</span>
<span id="cb35-4"><a href="#cb35-4" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;iris&quot;</span>, iris)</span>
<span id="cb35-5"><a href="#cb35-5" tabindex="-1"></a><span class="fu">dbExistsTable</span>(con, <span class="st">&quot;iris&quot;</span>)</span>
<span id="cb35-6"><a href="#cb35-6" tabindex="-1"></a><span class="fu">dbRemoveTable</span>(con, <span class="st">&quot;iris&quot;</span>)</span>
<span id="cb35-7"><a href="#cb35-7" tabindex="-1"></a><span class="fu">dbExistsTable</span>(con, <span class="st">&quot;iris&quot;</span>)</span>
<span id="cb35-8"><a href="#cb35-8" tabindex="-1"></a></span>
<span id="cb35-9"><a href="#cb35-9" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="list-field-names-of-a-remote-table" class="section level2">
<h2>List field names of a remote table</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb36"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb36-1"><a href="#cb36-1" tabindex="-1"></a><span class="fu">dbListFields</span>(conn, name, ...)</span></code></pre></div>
<div id="description-17" class="section level3">
<h3>Description</h3>
<p>Returns the field names of a remote table as a character vector.</p>
</div>
<div id="arguments-17" class="section level3">
<h3>Arguments</h3>
<table>
<colgroup>
<col style="width: 50%" />
<col style="width: 50%" />
</colgroup>
<tbody>
<tr class="odd">
<td>
<code id="dbListFields_:_conn">conn</code>
</td>
<td>
<p>
A DBIConnection object, as returned by <code>dbConnect()</code>.
</p>
</td>
</tr>
<tr class="even">
<td>
<code id="dbListFields_:_name">name</code>
</td>
<td>
<p>
The table name, passed on to <code>dbQuoteIdentifier()</code>. Options
are:
</p>
<ul>
<li>
<p>
a character string with the unquoted DBMS table name, e.g.
<code>“table_name”</code>,
</p>
</li>
<li>
<p>
a call to <code>Id()</code> with components to the fully qualified table
name, e.g. <code>Id(schema = “my_schema”, table = “table_name”)</code>
</p>
</li>
<li>
<p>
a call to <code>SQL()</code> with the quoted and fully qualified table
name given verbatim, e.g. <code>SQL(‘“my_schema”.”table_name”’)</code>
</p>
</li>
</ul>
</td>
</tr>
<tr class="odd">
<td>
<code id="dbListFields_:_...">…</code>
</td>
<td>
<p>
Other parameters passed on to methods.
</p>
</td>
</tr>
</tbody>
</table>
</div>
<div id="value-17" class="section level3">
<h3>Value</h3>
<p><code>dbListFields()</code> returns a character vector that
enumerates all fields in the table in the correct order. This also works
for temporary tables if supported by the database. The returned names
are suitable for quoting with <code>dbQuoteIdentifier()</code>.</p>
</div>
<div id="failure-modes-16" class="section level3">
<h3>Failure modes</h3>
<p>If the table does not exist, an error is raised. Invalid types for
the <code>name</code> argument (e.g., <code>character</code> of length
not equal to one, or numeric) lead to an error. An error is also raised
when calling this method for a closed or invalid connection.</p>
</div>
<div id="specification-15" class="section level3">
<h3>Specification</h3>
<p>The <code>name</code> argument can be</p>
<ul>
<li><p>a string</p></li>
<li><p>the return value of <code>dbQuoteIdentifier()</code></p></li>
<li><p>a value from the <code>table</code> column from the return value
of <code>dbListObjects()</code> where <code>is_prefix</code> is
<code>FALSE</code></p></li>
</ul>
<p>A column named <code>row_names</code> is treated like any other
column.</p>
</div>
<div id="examples-18" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb37"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb37-1"><a href="#cb37-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb37-2"><a href="#cb37-2" tabindex="-1"></a></span>
<span id="cb37-3"><a href="#cb37-3" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;mtcars&quot;</span>, mtcars)</span>
<span id="cb37-4"><a href="#cb37-4" tabindex="-1"></a><span class="fu">dbListFields</span>(con, <span class="st">&quot;mtcars&quot;</span>)</span>
<span id="cb37-5"><a href="#cb37-5" tabindex="-1"></a></span>
<span id="cb37-6"><a href="#cb37-6" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="is-this-dbms-object-still-valid" class="section level2">
<h2>Is this DBMS object still valid?</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb38"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb38-1"><a href="#cb38-1" tabindex="-1"></a><span class="fu">dbIsValid</span>(dbObj, ...)</span></code></pre></div>
<div id="description-18" class="section level3">
<h3>Description</h3>
<p>This generic tests whether a database object is still valid (i.e. it
hasn’t been disconnected or cleared).</p>
</div>
<div id="arguments-18" class="section level3">
<h3>Arguments</h3>
<table>
<colgroup>
<col width="9%" />
<col width="90%" />
</colgroup>
<tbody>
<tr class="odd">
<td><code>dbObj</code></td>
<td>An object inheriting from DBIObject, i.e. DBIDriver, DBIConnection,
or a DBIResult</td>
</tr>
<tr class="even">
<td><code>...</code></td>
<td>Other arguments to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="value-18" class="section level3">
<h3>Value</h3>
<p><code>dbIsValid()</code> returns a logical scalar, <code>TRUE</code>
if the object specified by <code>dbObj</code> is valid,
<code>FALSE</code> otherwise. A DBIConnection object is initially valid,
and becomes invalid after disconnecting with
<code>dbDisconnect()</code>. For an invalid connection object (e.g., for
some drivers if the object is saved to a file and then restored), the
method also returns <code>FALSE</code>. A DBIResult object is valid
after a call to <code>dbSendQuery()</code>, and stays valid even after
all rows have been fetched; only clearing it with
<code>dbClearResult()</code> invalidates it. A DBIResult object is also
valid after a call to <code>dbSendStatement()</code>, and stays valid
after querying the number of rows affected; only clearing it with
<code>dbClearResult()</code> invalidates it. If the connection to the
database system is dropped (e.g., due to connectivity problems, server
failure, etc.), <code>dbIsValid()</code> should return
<code>FALSE</code>. This is not tested automatically.</p>
</div>
<div id="examples-19" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb39"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb39-1"><a href="#cb39-1" tabindex="-1"></a><span class="fu">dbIsValid</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>())</span>
<span id="cb39-2"><a href="#cb39-2" tabindex="-1"></a></span>
<span id="cb39-3"><a href="#cb39-3" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb39-4"><a href="#cb39-4" tabindex="-1"></a><span class="fu">dbIsValid</span>(con)</span>
<span id="cb39-5"><a href="#cb39-5" tabindex="-1"></a></span>
<span id="cb39-6"><a href="#cb39-6" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendQuery</span>(con, <span class="st">&quot;SELECT 1&quot;</span>)</span>
<span id="cb39-7"><a href="#cb39-7" tabindex="-1"></a><span class="fu">dbIsValid</span>(rs)</span>
<span id="cb39-8"><a href="#cb39-8" tabindex="-1"></a></span>
<span id="cb39-9"><a href="#cb39-9" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb39-10"><a href="#cb39-10" tabindex="-1"></a><span class="fu">dbIsValid</span>(rs)</span>
<span id="cb39-11"><a href="#cb39-11" tabindex="-1"></a></span>
<span id="cb39-12"><a href="#cb39-12" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span>
<span id="cb39-13"><a href="#cb39-13" tabindex="-1"></a><span class="fu">dbIsValid</span>(con)</span></code></pre></div>
</div>
</div>
<div id="completion-status" class="section level2">
<h2>Completion status</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb40"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb40-1"><a href="#cb40-1" tabindex="-1"></a><span class="fu">dbHasCompleted</span>(res, ...)</span></code></pre></div>
<div id="description-19" class="section level3">
<h3>Description</h3>
<p>This method returns if the operation has completed. A
<code>SELECT</code> query is completed if all rows have been fetched. A
data manipulation statement is always completed.</p>
</div>
<div id="arguments-19" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>res</code></td>
<td>An object inheriting from DBIResult.</td>
</tr>
<tr class="even">
<td><code>...</code></td>
<td>Other arguments passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="value-19" class="section level3">
<h3>Value</h3>
<p><code>dbHasCompleted()</code> returns a logical scalar. For a query
initiated by <code>dbSendQuery()</code> with non-empty result set,
<code>dbHasCompleted()</code> returns <code>FALSE</code> initially and
<code>TRUE</code> after calling <code>dbFetch()</code> without limit.
For a query initiated by <code>dbSendStatement()</code>,
<code>dbHasCompleted()</code> always returns <code>TRUE</code>.</p>
</div>
<div id="the-data-retrieval-flow-4" class="section level3">
<h3>The data retrieval flow</h3>
<p>This section gives a complete overview over the flow for the
execution of queries that return tabular data as data frames.</p>
<p>Most of this flow, except repeated calling of <code>dbBind()</code>
or <code>dbBindArrow()</code>, is implemented by
<code>dbGetQuery()</code>, which should be sufficient unless you want to
access the results in a paged way or you have a parameterized query that
you want to reuse. This flow requires an active connection established
by <code>dbConnect()</code>. See also
<code>vignette(&quot;dbi-advanced&quot;)</code> for a walkthrough.</p>
<ol style="list-style-type: decimal">
<li><p>Use <code>dbSendQuery()</code> to create a result set object of
class DBIResult.</p></li>
<li><p>Optionally, bind query parameters with <code>dbBind()</code> or
<code>dbBindArrow()</code>. This is required only if the query contains
placeholders such as <code>⁠?⁠</code> or <code>⁠\$1⁠</code>, depending on
the database backend.</p></li>
<li><p>Optionally, use <code>dbColumnInfo()</code> to retrieve the
structure of the result set without retrieving actual data.</p></li>
<li><p>Use <code>dbFetch()</code> to get the entire result set, a page
of results, or the remaining rows. Fetching zero rows is also possible
to retrieve the structure of the result set as a data frame. This step
can be called multiple times. Only forward paging is supported, you need
to cache previous pages if you need to navigate backwards.</p></li>
<li><p>Use <code>dbHasCompleted()</code> to tell when you’re done. This
method returns <code>TRUE</code> if no more rows are available for
fetching.</p></li>
<li><p>Repeat the last four steps as necessary.</p></li>
<li><p>Use <code>dbClearResult()</code> 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 use
<code>on.exit()</code> or <code>withr::defer()</code> to ensure that
this step is always executed.</p></li>
</ol>
</div>
<div id="failure-modes-17" class="section level3">
<h3>Failure modes</h3>
<p>Attempting to query completion status for a result set cleared with
<code>dbClearResult()</code> gives an error.</p>
</div>
<div id="specification-16" class="section level3">
<h3>Specification</h3>
<p>The completion status for a query is only guaranteed to be set to
<code>FALSE</code> after attempting to fetch past the end of the entire
result. Therefore, for a query with an empty result set, the initial
return value is unspecified, but the result value is <code>TRUE</code>
after trying to fetch only one row.</p>
<p>Similarly, for a query with a result set of length n, the return
value is unspecified after fetching n rows, but the result value is
<code>TRUE</code> after trying to fetch only one more row.</p>
</div>
<div id="examples-20" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb41"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb41-1"><a href="#cb41-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb41-2"><a href="#cb41-2" tabindex="-1"></a></span>
<span id="cb41-3"><a href="#cb41-3" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;mtcars&quot;</span>, mtcars)</span>
<span id="cb41-4"><a href="#cb41-4" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendQuery</span>(con, <span class="st">&quot;SELECT * FROM mtcars&quot;</span>)</span>
<span id="cb41-5"><a href="#cb41-5" tabindex="-1"></a></span>
<span id="cb41-6"><a href="#cb41-6" tabindex="-1"></a><span class="fu">dbHasCompleted</span>(rs)</span>
<span id="cb41-7"><a href="#cb41-7" tabindex="-1"></a>ret1 <span class="ot">&lt;-</span> <span class="fu">dbFetch</span>(rs, <span class="dv">10</span>)</span>
<span id="cb41-8"><a href="#cb41-8" tabindex="-1"></a><span class="fu">dbHasCompleted</span>(rs)</span>
<span id="cb41-9"><a href="#cb41-9" tabindex="-1"></a>ret2 <span class="ot">&lt;-</span> <span class="fu">dbFetch</span>(rs)</span>
<span id="cb41-10"><a href="#cb41-10" tabindex="-1"></a><span class="fu">dbHasCompleted</span>(rs)</span>
<span id="cb41-11"><a href="#cb41-11" tabindex="-1"></a></span>
<span id="cb41-12"><a href="#cb41-12" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb41-13"><a href="#cb41-13" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="get-the-statement-associated-with-a-result-set" class="section level2">
<h2>Get the statement associated with a result set</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb42"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb42-1"><a href="#cb42-1" tabindex="-1"></a><span class="fu">dbGetStatement</span>(res, ...)</span></code></pre></div>
<div id="description-20" class="section level3">
<h3>Description</h3>
<p>Returns the statement that was passed to <code>dbSendQuery()</code>
or <code>dbSendStatement()</code>.</p>
</div>
<div id="arguments-20" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>res</code></td>
<td>An object inheriting from DBIResult.</td>
</tr>
<tr class="even">
<td><code>...</code></td>
<td>Other arguments passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="value-20" class="section level3">
<h3>Value</h3>
<p><code>dbGetStatement()</code> returns a string, the query used in
either <code>dbSendQuery()</code> or <code>dbSendStatement()</code>.</p>
</div>
<div id="failure-modes-18" class="section level3">
<h3>Failure modes</h3>
<p>Attempting to query the statement for a result set cleared with
<code>dbClearResult()</code> gives an error.</p>
</div>
<div id="examples-21" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb43"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb43-1"><a href="#cb43-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb43-2"><a href="#cb43-2" tabindex="-1"></a></span>
<span id="cb43-3"><a href="#cb43-3" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;mtcars&quot;</span>, mtcars)</span>
<span id="cb43-4"><a href="#cb43-4" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendQuery</span>(con, <span class="st">&quot;SELECT * FROM mtcars&quot;</span>)</span>
<span id="cb43-5"><a href="#cb43-5" tabindex="-1"></a><span class="fu">dbGetStatement</span>(rs)</span>
<span id="cb43-6"><a href="#cb43-6" tabindex="-1"></a></span>
<span id="cb43-7"><a href="#cb43-7" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb43-8"><a href="#cb43-8" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="the-number-of-rows-fetched-so-far" class="section level2">
<h2>The number of rows fetched so far</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb44"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb44-1"><a href="#cb44-1" tabindex="-1"></a><span class="fu">dbGetRowCount</span>(res, ...)</span></code></pre></div>
<div id="description-21" class="section level3">
<h3>Description</h3>
<p>Returns the total number of rows actually fetched with calls to
<code>dbFetch()</code> for this result set.</p>
</div>
<div id="arguments-21" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>res</code></td>
<td>An object inheriting from DBIResult.</td>
</tr>
<tr class="even">
<td><code>...</code></td>
<td>Other arguments passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="value-21" class="section level3">
<h3>Value</h3>
<p><code>dbGetRowCount()</code> returns a scalar number (integer or
numeric), the number of rows fetched so far. After calling
<code>dbSendQuery()</code>, the row count is initially zero. After a
call to <code>dbFetch()</code> without limit, the row count matches the
total number of rows returned. Fetching a limited number of rows
increases the number of rows by the number of rows returned, even if
fetching past the end of the result set. For queries with an empty
result set, zero is returned even after fetching. For data manipulation
statements issued with <code>dbSendStatement()</code>, zero is returned
before and after calling <code>dbFetch()</code>.</p>
</div>
<div id="failure-modes-19" class="section level3">
<h3>Failure modes</h3>
<p>Attempting to get the row count for a result set cleared with
<code>dbClearResult()</code> gives an error.</p>
</div>
<div id="examples-22" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb45"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb45-1"><a href="#cb45-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb45-2"><a href="#cb45-2" tabindex="-1"></a></span>
<span id="cb45-3"><a href="#cb45-3" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;mtcars&quot;</span>, mtcars)</span>
<span id="cb45-4"><a href="#cb45-4" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendQuery</span>(con, <span class="st">&quot;SELECT * FROM mtcars&quot;</span>)</span>
<span id="cb45-5"><a href="#cb45-5" tabindex="-1"></a></span>
<span id="cb45-6"><a href="#cb45-6" tabindex="-1"></a><span class="fu">dbGetRowCount</span>(rs)</span>
<span id="cb45-7"><a href="#cb45-7" tabindex="-1"></a>ret1 <span class="ot">&lt;-</span> <span class="fu">dbFetch</span>(rs, <span class="dv">10</span>)</span>
<span id="cb45-8"><a href="#cb45-8" tabindex="-1"></a><span class="fu">dbGetRowCount</span>(rs)</span>
<span id="cb45-9"><a href="#cb45-9" tabindex="-1"></a>ret2 <span class="ot">&lt;-</span> <span class="fu">dbFetch</span>(rs)</span>
<span id="cb45-10"><a href="#cb45-10" tabindex="-1"></a><span class="fu">dbGetRowCount</span>(rs)</span>
<span id="cb45-11"><a href="#cb45-11" tabindex="-1"></a><span class="fu">nrow</span>(ret1) <span class="sc">+</span> <span class="fu">nrow</span>(ret2)</span>
<span id="cb45-12"><a href="#cb45-12" tabindex="-1"></a></span>
<span id="cb45-13"><a href="#cb45-13" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb45-14"><a href="#cb45-14" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="the-number-of-rows-affected" class="section level2">
<h2>The number of rows affected</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb46"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb46-1"><a href="#cb46-1" tabindex="-1"></a><span class="fu">dbGetRowsAffected</span>(res, ...)</span></code></pre></div>
<div id="description-22" class="section level3">
<h3>Description</h3>
<p>This method returns the number of rows that were added, deleted, or
updated by a data manipulation statement.</p>
</div>
<div id="arguments-22" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>res</code></td>
<td>An object inheriting from DBIResult.</td>
</tr>
<tr class="even">
<td><code>...</code></td>
<td>Other arguments passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="value-22" class="section level3">
<h3>Value</h3>
<p><code>dbGetRowsAffected()</code> returns a scalar number (integer or
numeric), the number of rows affected by a data manipulation statement
issued with <code>dbSendStatement()</code>. The value is available
directly after the call and does not change after calling
<code>dbFetch()</code>. <code>NA_integer_</code> or
<code>NA_numeric_</code> are allowed if the number of rows affected is
not known.</p>
<p>For queries issued with <code>dbSendQuery()</code>, zero is returned
before and after the call to <code>dbFetch()</code>. <code>NA</code>
values are not allowed.</p>
</div>
<div id="the-command-execution-flow-3" class="section level3">
<h3>The command execution flow</h3>
<p>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
<code>dbBindArrow()</code>, is implemented by <code>dbExecute()</code>,
which should be sufficient for non-parameterized queries. This flow
requires an active connection established by <code>dbConnect()</code>.
See also <code>vignette(&quot;dbi-advanced&quot;)</code> for a walkthrough.</p>
<ol style="list-style-type: decimal">
<li><p>Use <code>dbSendStatement()</code> to create a result set object
of class DBIResult. For some queries you need to pass
<code>immediate = TRUE</code>.</p></li>
<li><p>Optionally, bind query parameters with<code>dbBind()</code> or
<code>dbBindArrow()</code>. This is required only if the query contains
placeholders such as <code>⁠?⁠</code> or <code>⁠\$1⁠</code>, depending on
the database backend.</p></li>
<li><p>Optionally, use <code>dbGetRowsAffected()</code> to retrieve the
number of rows affected by the query.</p></li>
<li><p>Repeat the last two steps as necessary.</p></li>
<li><p>Use <code>dbClearResult()</code> 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 use
<code>on.exit()</code> or <code>withr::defer()</code> to ensure that
this step is always executed.</p></li>
</ol>
</div>
<div id="failure-modes-20" class="section level3">
<h3>Failure modes</h3>
<p>Attempting to get the rows affected for a result set cleared with
<code>dbClearResult()</code> gives an error.</p>
</div>
<div id="examples-23" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb47"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb47-1"><a href="#cb47-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb47-2"><a href="#cb47-2" tabindex="-1"></a></span>
<span id="cb47-3"><a href="#cb47-3" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;mtcars&quot;</span>, mtcars)</span>
<span id="cb47-4"><a href="#cb47-4" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendStatement</span>(con, <span class="st">&quot;DELETE FROM mtcars&quot;</span>)</span>
<span id="cb47-5"><a href="#cb47-5" tabindex="-1"></a><span class="fu">dbGetRowsAffected</span>(rs)</span>
<span id="cb47-6"><a href="#cb47-6" tabindex="-1"></a><span class="fu">nrow</span>(mtcars)</span>
<span id="cb47-7"><a href="#cb47-7" tabindex="-1"></a></span>
<span id="cb47-8"><a href="#cb47-8" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb47-9"><a href="#cb47-9" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="information-about-result-types" class="section level2">
<h2>Information about result types</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb48"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb48-1"><a href="#cb48-1" tabindex="-1"></a><span class="fu">dbColumnInfo</span>(res, ...)</span></code></pre></div>
<div id="description-23" class="section level3">
<h3>Description</h3>
<p>Produces a data.frame that describes the output of a query. The
data.frame should have as many rows as there are output fields in the
result set, and each column in the data.frame describes an aspect of the
result set field (field name, type, etc.)</p>
</div>
<div id="arguments-23" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>res</code></td>
<td>An object inheriting from DBIResult.</td>
</tr>
<tr class="even">
<td><code>...</code></td>
<td>Other arguments passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="value-23" class="section level3">
<h3>Value</h3>
<p><code>dbColumnInfo()</code> returns a data frame with at least two
columns <code>&quot;name&quot;</code> and <code>&quot;type&quot;</code> (in that order) (and
optional columns that start with a dot). The <code>&quot;name&quot;</code> and
<code>&quot;type&quot;</code> columns contain the names and types of the R columns
of the data frame that is returned from <code>dbFetch()</code>. The
<code>&quot;type&quot;</code> column is of type <code>character</code> and only
for information. Do not compute on the <code>&quot;type&quot;</code> column,
instead use <code>dbFetch(res, n = 0)</code> to create a zero-row data
frame initialized with the correct data types.</p>
</div>
<div id="the-data-retrieval-flow-5" class="section level3">
<h3>The data retrieval flow</h3>
<p>This section gives a complete overview over the flow for the
execution of queries that return tabular data as data frames.</p>
<p>Most of this flow, except repeated calling of <code>dbBind()</code>
or <code>dbBindArrow()</code>, is implemented by
<code>dbGetQuery()</code>, which should be sufficient unless you want to
access the results in a paged way or you have a parameterized query that
you want to reuse. This flow requires an active connection established
by <code>dbConnect()</code>. See also
<code>vignette(&quot;dbi-advanced&quot;)</code> for a walkthrough.</p>
<ol style="list-style-type: decimal">
<li><p>Use <code>dbSendQuery()</code> to create a result set object of
class DBIResult.</p></li>
<li><p>Optionally, bind query parameters with <code>dbBind()</code> or
<code>dbBindArrow()</code>. This is required only if the query contains
placeholders such as <code>⁠?⁠</code> or <code>⁠\$1⁠</code>, depending on
the database backend.</p></li>
<li><p>Optionally, use <code>dbColumnInfo()</code> to retrieve the
structure of the result set without retrieving actual data.</p></li>
<li><p>Use <code>dbFetch()</code> to get the entire result set, a page
of results, or the remaining rows. Fetching zero rows is also possible
to retrieve the structure of the result set as a data frame. This step
can be called multiple times. Only forward paging is supported, you need
to cache previous pages if you need to navigate backwards.</p></li>
<li><p>Use <code>dbHasCompleted()</code> to tell when you’re done. This
method returns <code>TRUE</code> if no more rows are available for
fetching.</p></li>
<li><p>Repeat the last four steps as necessary.</p></li>
<li><p>Use <code>dbClearResult()</code> 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 use
<code>on.exit()</code> or <code>withr::defer()</code> to ensure that
this step is always executed.</p></li>
</ol>
</div>
<div id="failure-modes-21" class="section level3">
<h3>Failure modes</h3>
<p>An attempt to query columns for a closed result set raises an
error.</p>
</div>
<div id="specification-17" class="section level3">
<h3>Specification</h3>
<p>A column named <code>row_names</code> is treated like any other
column.</p>
<p>The column names are always consistent with the data returned by
<code>dbFetch()</code>.</p>
<p>If the query returns unnamed columns, non-empty and
non-<code>NA</code> names are assigned.</p>
<p>Column names that correspond to SQL or R keywords are left
unchanged.</p>
</div>
<div id="examples-24" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb49"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb49-1"><a href="#cb49-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb49-2"><a href="#cb49-2" tabindex="-1"></a></span>
<span id="cb49-3"><a href="#cb49-3" tabindex="-1"></a>rs <span class="ot">&lt;-</span> <span class="fu">dbSendQuery</span>(con, <span class="st">&quot;SELECT 1 AS a, 2 AS b&quot;</span>)</span>
<span id="cb49-4"><a href="#cb49-4" tabindex="-1"></a><span class="fu">dbColumnInfo</span>(rs)</span>
<span id="cb49-5"><a href="#cb49-5" tabindex="-1"></a><span class="fu">dbFetch</span>(rs)</span>
<span id="cb49-6"><a href="#cb49-6" tabindex="-1"></a></span>
<span id="cb49-7"><a href="#cb49-7" tabindex="-1"></a><span class="fu">dbClearResult</span>(rs)</span>
<span id="cb49-8"><a href="#cb49-8" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="begincommitrollback-sql-transactions" class="section level2">
<h2>Begin/commit/rollback SQL transactions</h2>
<p>This section describes the behavior of the following methods:</p>
<div class="sourceCode" id="cb50"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb50-1"><a href="#cb50-1" tabindex="-1"></a><span class="fu">dbBegin</span>(conn, ...)</span>
<span id="cb50-2"><a href="#cb50-2" tabindex="-1"></a></span>
<span id="cb50-3"><a href="#cb50-3" tabindex="-1"></a><span class="fu">dbCommit</span>(conn, ...)</span>
<span id="cb50-4"><a href="#cb50-4" tabindex="-1"></a></span>
<span id="cb50-5"><a href="#cb50-5" tabindex="-1"></a><span class="fu">dbRollback</span>(conn, ...)</span></code></pre></div>
<div id="description-24" class="section level3">
<h3>Description</h3>
<p>A transaction encapsulates several SQL statements in an atomic unit.
It is initiated with <code>dbBegin()</code> and either made persistent
with <code>dbCommit()</code> or undone with <code>dbRollback()</code>.
In any case, the DBMS guarantees that either all or none of the
statements have a permanent effect. This helps ensuring consistency of
write operations to multiple tables.</p>
</div>
<div id="arguments-24" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>conn</code></td>
<td>A DBIConnection object, as returned by
<code>dbConnect()</code>.</td>
</tr>
<tr class="even">
<td><code>...</code></td>
<td>Other parameters passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="details-9" class="section level3">
<h3>Details</h3>
<p>Not all database engines implement transaction management, in which
case these methods should not be implemented for the specific
DBIConnection subclass.</p>
</div>
<div id="value-24" class="section level3">
<h3>Value</h3>
<p><code>dbBegin()</code>, <code>dbCommit()</code> and
<code>dbRollback()</code> return <code>TRUE</code>, invisibly.</p>
</div>
<div id="failure-modes-22" class="section level3">
<h3>Failure modes</h3>
<p>The implementations are expected to raise an error in case of
failure, but this is not tested. In any way, all generics throw an error
with a closed or invalid connection. In addition, a call to
<code>dbCommit()</code> or <code>dbRollback()</code> without a prior
call to <code>dbBegin()</code> raises an error. Nested transactions are
not supported by DBI, an attempt to call <code>dbBegin()</code> twice
yields an error.</p>
</div>
<div id="specification-18" class="section level3">
<h3>Specification</h3>
<p>Actual support for transactions may vary between backends. A
transaction is initiated by a call to <code>dbBegin()</code> and
committed by a call to <code>dbCommit()</code>. Data written in a
transaction must persist after the transaction is committed. For
example, a record that is missing when the transaction is started but is
created during the transaction must exist both during and after the
transaction, and also in a new connection.</p>
<p>A transaction can also be aborted with <code>dbRollback()</code>. All
data written in such a transaction must be removed after the transaction
is rolled back. For example, a record that is missing when the
transaction is started but is created during the transaction must not
exist anymore after the rollback.</p>
<p>Disconnection from a connection with an open transaction effectively
rolls back the transaction. All data written in such a transaction must
be removed after the transaction is rolled back.</p>
<p>The behavior is not specified if other arguments are passed to these
functions. In particular, <span class="pkg">RSQLite</span> issues named
transactions with support for nesting if the <code>name</code> argument
is set.</p>
<p>The transaction isolation level is not specified by DBI.</p>
</div>
<div id="examples-25" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb51"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb51-1"><a href="#cb51-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb51-2"><a href="#cb51-2" tabindex="-1"></a></span>
<span id="cb51-3"><a href="#cb51-3" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;cash&quot;</span>, <span class="fu">data.frame</span>(<span class="at">amount =</span> <span class="dv">100</span>))</span>
<span id="cb51-4"><a href="#cb51-4" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;account&quot;</span>, <span class="fu">data.frame</span>(<span class="at">amount =</span> <span class="dv">2000</span>))</span>
<span id="cb51-5"><a href="#cb51-5" tabindex="-1"></a></span>
<span id="cb51-6"><a href="#cb51-6" tabindex="-1"></a><span class="co"># All operations are carried out as logical unit:</span></span>
<span id="cb51-7"><a href="#cb51-7" tabindex="-1"></a><span class="fu">dbBegin</span>(con)</span>
<span id="cb51-8"><a href="#cb51-8" tabindex="-1"></a>withdrawal <span class="ot">&lt;-</span> <span class="dv">300</span></span>
<span id="cb51-9"><a href="#cb51-9" tabindex="-1"></a><span class="fu">dbExecute</span>(con, <span class="st">&quot;UPDATE cash SET amount = amount + ?&quot;</span>, <span class="fu">list</span>(withdrawal))</span>
<span id="cb51-10"><a href="#cb51-10" tabindex="-1"></a><span class="fu">dbExecute</span>(con, <span class="st">&quot;UPDATE account SET amount = amount - ?&quot;</span>, <span class="fu">list</span>(withdrawal))</span>
<span id="cb51-11"><a href="#cb51-11" tabindex="-1"></a><span class="fu">dbCommit</span>(con)</span>
<span id="cb51-12"><a href="#cb51-12" tabindex="-1"></a></span>
<span id="cb51-13"><a href="#cb51-13" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;cash&quot;</span>)</span>
<span id="cb51-14"><a href="#cb51-14" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;account&quot;</span>)</span>
<span id="cb51-15"><a href="#cb51-15" tabindex="-1"></a></span>
<span id="cb51-16"><a href="#cb51-16" tabindex="-1"></a><span class="co"># Rolling back after detecting negative value on account:</span></span>
<span id="cb51-17"><a href="#cb51-17" tabindex="-1"></a><span class="fu">dbBegin</span>(con)</span>
<span id="cb51-18"><a href="#cb51-18" tabindex="-1"></a>withdrawal <span class="ot">&lt;-</span> <span class="dv">5000</span></span>
<span id="cb51-19"><a href="#cb51-19" tabindex="-1"></a><span class="fu">dbExecute</span>(con, <span class="st">&quot;UPDATE cash SET amount = amount + ?&quot;</span>, <span class="fu">list</span>(withdrawal))</span>
<span id="cb51-20"><a href="#cb51-20" tabindex="-1"></a><span class="fu">dbExecute</span>(con, <span class="st">&quot;UPDATE account SET amount = amount - ?&quot;</span>, <span class="fu">list</span>(withdrawal))</span>
<span id="cb51-21"><a href="#cb51-21" tabindex="-1"></a><span class="cf">if</span> (<span class="fu">dbReadTable</span>(con, <span class="st">&quot;account&quot;</span>)\<span class="sc">$</span>amount <span class="sc">&gt;=</span> <span class="dv">0</span>) {</span>
<span id="cb51-22"><a href="#cb51-22" tabindex="-1"></a>  <span class="fu">dbCommit</span>(con)</span>
<span id="cb51-23"><a href="#cb51-23" tabindex="-1"></a>} <span class="cf">else</span> {</span>
<span id="cb51-24"><a href="#cb51-24" tabindex="-1"></a>  <span class="fu">dbRollback</span>(con)</span>
<span id="cb51-25"><a href="#cb51-25" tabindex="-1"></a>}</span>
<span id="cb51-26"><a href="#cb51-26" tabindex="-1"></a></span>
<span id="cb51-27"><a href="#cb51-27" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;cash&quot;</span>)</span>
<span id="cb51-28"><a href="#cb51-28" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;account&quot;</span>)</span>
<span id="cb51-29"><a href="#cb51-29" tabindex="-1"></a></span>
<span id="cb51-30"><a href="#cb51-30" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="self-contained-sql-transactions" class="section level2">
<h2>Self-contained SQL transactions</h2>
<p>This section describes the behavior of the following methods:</p>
<div class="sourceCode" id="cb52"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb52-1"><a href="#cb52-1" tabindex="-1"></a><span class="fu">dbWithTransaction</span>(conn, code, ...)</span>
<span id="cb52-2"><a href="#cb52-2" tabindex="-1"></a></span>
<span id="cb52-3"><a href="#cb52-3" tabindex="-1"></a><span class="fu">dbBreak</span>()</span></code></pre></div>
<div id="description-25" class="section level3">
<h3>Description</h3>
<p>Given that transactions are implemented, this function allows you to
pass in code that is run in a transaction. The default method of
<code>dbWithTransaction()</code> calls <code>dbBegin()</code> before
executing the code, and <code>dbCommit()</code> after successful
completion, or <code>dbRollback()</code> in case of an error. The
advantage is that you don’t have to remember to do
<code>dbBegin()</code> and <code>dbCommit()</code> or
<code>dbRollback()</code> – that is all taken care of. The special
function <code>dbBreak()</code> allows an early exit with rollback, it
can be called only inside <code>dbWithTransaction()</code>.</p>
</div>
<div id="arguments-25" class="section level3">
<h3>Arguments</h3>
<table>
<tbody>
<tr class="odd">
<td><code>conn</code></td>
<td>A DBIConnection object, as returned by
<code>dbConnect()</code>.</td>
</tr>
<tr class="even">
<td><code>code</code></td>
<td>An arbitrary block of R code.</td>
</tr>
<tr class="odd">
<td><code>...</code></td>
<td>Other parameters passed on to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="details-10" class="section level3">
<h3>Details</h3>
<p>DBI implements <code>dbWithTransaction()</code>, backends should need
to override this generic only if they implement specialized
handling.</p>
</div>
<div id="value-25" class="section level3">
<h3>Value</h3>
<p><code>dbWithTransaction()</code> returns the value of the executed
code.</p>
</div>
<div id="failure-modes-23" class="section level3">
<h3>Failure modes</h3>
<p>Failure to initiate the transaction (e.g., if the connection is
closed or invalid of if <code>dbBegin()</code> has been called already)
gives an error.</p>
</div>
<div id="specification-19" class="section level3">
<h3>Specification</h3>
<p><code>dbWithTransaction()</code> initiates a transaction with
<code>dbBegin()</code>, executes the code given in the <code>code</code>
argument, and commits the transaction with <code>dbCommit()</code>. If
the code raises an error, the transaction is instead aborted with
<code>dbRollback()</code>, and the error is propagated. If the code
calls <code>dbBreak()</code>, execution of the code stops and the
transaction is silently aborted. All side effects caused by the code
(such as the creation of new variables) propagate to the calling
environment.</p>
</div>
<div id="examples-26" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb53"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb53-1"><a href="#cb53-1" tabindex="-1"></a>con <span class="ot">&lt;-</span> <span class="fu">dbConnect</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>(), <span class="st">&quot;:memory:&quot;</span>)</span>
<span id="cb53-2"><a href="#cb53-2" tabindex="-1"></a></span>
<span id="cb53-3"><a href="#cb53-3" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;cash&quot;</span>, <span class="fu">data.frame</span>(<span class="at">amount =</span> <span class="dv">100</span>))</span>
<span id="cb53-4"><a href="#cb53-4" tabindex="-1"></a><span class="fu">dbWriteTable</span>(con, <span class="st">&quot;account&quot;</span>, <span class="fu">data.frame</span>(<span class="at">amount =</span> <span class="dv">2000</span>))</span>
<span id="cb53-5"><a href="#cb53-5" tabindex="-1"></a></span>
<span id="cb53-6"><a href="#cb53-6" tabindex="-1"></a><span class="co"># All operations are carried out as logical unit:</span></span>
<span id="cb53-7"><a href="#cb53-7" tabindex="-1"></a><span class="fu">dbWithTransaction</span>(</span>
<span id="cb53-8"><a href="#cb53-8" tabindex="-1"></a>  con,</span>
<span id="cb53-9"><a href="#cb53-9" tabindex="-1"></a>  {</span>
<span id="cb53-10"><a href="#cb53-10" tabindex="-1"></a>    withdrawal <span class="ot">&lt;-</span> <span class="dv">300</span></span>
<span id="cb53-11"><a href="#cb53-11" tabindex="-1"></a>    <span class="fu">dbExecute</span>(con, <span class="st">&quot;UPDATE cash SET amount = amount + ?&quot;</span>, <span class="fu">list</span>(withdrawal))</span>
<span id="cb53-12"><a href="#cb53-12" tabindex="-1"></a>    <span class="fu">dbExecute</span>(con, <span class="st">&quot;UPDATE account SET amount = amount - ?&quot;</span>, <span class="fu">list</span>(withdrawal))</span>
<span id="cb53-13"><a href="#cb53-13" tabindex="-1"></a>  }</span>
<span id="cb53-14"><a href="#cb53-14" tabindex="-1"></a>)</span>
<span id="cb53-15"><a href="#cb53-15" tabindex="-1"></a></span>
<span id="cb53-16"><a href="#cb53-16" tabindex="-1"></a><span class="co"># The code is executed as if in the current environment:</span></span>
<span id="cb53-17"><a href="#cb53-17" tabindex="-1"></a>withdrawal</span>
<span id="cb53-18"><a href="#cb53-18" tabindex="-1"></a></span>
<span id="cb53-19"><a href="#cb53-19" tabindex="-1"></a><span class="co"># The changes are committed to the database after successful execution:</span></span>
<span id="cb53-20"><a href="#cb53-20" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;cash&quot;</span>)</span>
<span id="cb53-21"><a href="#cb53-21" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;account&quot;</span>)</span>
<span id="cb53-22"><a href="#cb53-22" tabindex="-1"></a></span>
<span id="cb53-23"><a href="#cb53-23" tabindex="-1"></a><span class="co"># Rolling back with dbBreak():</span></span>
<span id="cb53-24"><a href="#cb53-24" tabindex="-1"></a><span class="fu">dbWithTransaction</span>(</span>
<span id="cb53-25"><a href="#cb53-25" tabindex="-1"></a>  con,</span>
<span id="cb53-26"><a href="#cb53-26" tabindex="-1"></a>  {</span>
<span id="cb53-27"><a href="#cb53-27" tabindex="-1"></a>    withdrawal <span class="ot">&lt;-</span> <span class="dv">5000</span></span>
<span id="cb53-28"><a href="#cb53-28" tabindex="-1"></a>    <span class="fu">dbExecute</span>(con, <span class="st">&quot;UPDATE cash SET amount = amount + ?&quot;</span>, <span class="fu">list</span>(withdrawal))</span>
<span id="cb53-29"><a href="#cb53-29" tabindex="-1"></a>    <span class="fu">dbExecute</span>(con, <span class="st">&quot;UPDATE account SET amount = amount - ?&quot;</span>, <span class="fu">list</span>(withdrawal))</span>
<span id="cb53-30"><a href="#cb53-30" tabindex="-1"></a>    <span class="cf">if</span> (<span class="fu">dbReadTable</span>(con, <span class="st">&quot;account&quot;</span>)\<span class="sc">$</span>amount <span class="sc">&lt;</span> <span class="dv">0</span>) {</span>
<span id="cb53-31"><a href="#cb53-31" tabindex="-1"></a>      <span class="fu">dbBreak</span>()</span>
<span id="cb53-32"><a href="#cb53-32" tabindex="-1"></a>    }</span>
<span id="cb53-33"><a href="#cb53-33" tabindex="-1"></a>  }</span>
<span id="cb53-34"><a href="#cb53-34" tabindex="-1"></a>)</span>
<span id="cb53-35"><a href="#cb53-35" tabindex="-1"></a></span>
<span id="cb53-36"><a href="#cb53-36" tabindex="-1"></a><span class="co"># These changes were not committed to the database:</span></span>
<span id="cb53-37"><a href="#cb53-37" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;cash&quot;</span>)</span>
<span id="cb53-38"><a href="#cb53-38" tabindex="-1"></a><span class="fu">dbReadTable</span>(con, <span class="st">&quot;account&quot;</span>)</span>
<span id="cb53-39"><a href="#cb53-39" tabindex="-1"></a></span>
<span id="cb53-40"><a href="#cb53-40" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span></code></pre></div>
</div>
</div>
<div id="get-dbms-metadata" class="section level2">
<h2>Get DBMS metadata</h2>
<p>This section describes the behavior of the following method:</p>
<div class="sourceCode" id="cb54"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb54-1"><a href="#cb54-1" tabindex="-1"></a><span class="fu">dbGetInfo</span>(dbObj, ...)</span></code></pre></div>
<div id="description-26" class="section level3">
<h3>Description</h3>
<p>Retrieves information on objects of class DBIDriver, DBIConnection or
DBIResult.</p>
</div>
<div id="arguments-26" class="section level3">
<h3>Arguments</h3>
<table>
<colgroup>
<col width="9%" />
<col width="90%" />
</colgroup>
<tbody>
<tr class="odd">
<td><code>dbObj</code></td>
<td>An object inheriting from DBIObject, i.e. DBIDriver, DBIConnection,
or a DBIResult</td>
</tr>
<tr class="even">
<td><code>...</code></td>
<td>Other arguments to methods.</td>
</tr>
</tbody>
</table>
</div>
<div id="value-26" class="section level3">
<h3>Value</h3>
<p>For objects of class DBIDriver, <code>dbGetInfo()</code> returns a
named list that contains at least the following components:</p>
<ul>
<li><p><code>driver.version</code>: the package version of the DBI
backend,</p></li>
<li><p><code>client.version</code>: the version of the DBMS client
library.</p></li>
</ul>
<p>For objects of class DBIConnection, <code>dbGetInfo()</code> returns
a named list that contains at least the following components:</p>
<ul>
<li><p><code>db.version</code>: version of the database server,</p></li>
<li><p><code>dbname</code>: database name,</p></li>
<li><p><code>username</code>: username to connect to the
database,</p></li>
<li><p><code>host</code>: hostname of the database server,</p></li>
<li><p><code>port</code>: port on the database server. It must not
contain a <code>password</code> component. Components that are not
applicable should be set to <code>NA</code>.</p></li>
</ul>
<p>For objects of class DBIResult, <code>dbGetInfo()</code> returns a
named list that contains at least the following components:</p>
<ul>
<li><p><code>statatment</code>: the statement used with
<code>dbSendQuery()</code> or <code>dbExecute()</code>, as returned by
<code>dbGetStatement()</code>,</p></li>
<li><p><code>row.count</code>: the number of rows fetched so far (for
queries), as returned by <code>dbGetRowCount()</code>,</p></li>
<li><p><code>rows.affected</code>: the number of rows affected (for
statements), as returned by <code>dbGetRowsAffected()</code></p></li>
<li><p><code>has.completed</code>: a logical that indicates if the query
or statement has completed, as returned by
<code>dbHasCompleted()</code>.</p></li>
</ul>
</div>
<div id="implementation-notes-2" class="section level3">
<h3>Implementation notes</h3>
<p>The default implementation for <code>⁠DBIResult objects⁠</code>
constructs such a list from the return values of the corresponding
methods, <code>dbGetStatement()</code>, <code>dbGetRowCount()</code>,
<code>dbGetRowsAffected()</code>, and <code>dbHasCompleted()</code>.</p>
</div>
<div id="examples-27" class="section level3">
<h3>Examples</h3>
<div class="sourceCode" id="cb55"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb55-1"><a href="#cb55-1" tabindex="-1"></a><span class="fu">dbGetInfo</span>(RSQLite<span class="sc">::</span><span class="fu">SQLite</span>())</span></code></pre></div>
</div>
</div>



<!-- code folding -->


<!-- dynamically load mathjax for compatibility with self-contained -->
<script>
  (function () {
    var script = document.createElement("script");
    script.type = "text/javascript";
    script.src  = "https://mathjax.rstudio.com/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML";
    document.getElementsByTagName("head")[0].appendChild(script);
  })();
</script>

</body>
</html>