Movatterモバイル変換

Title:	R Database Interface
Version:	1.2.3
Date:	2024-06-02
Description:	A database interface definition 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.
License:	LGPL-2.1 |LGPL-3 [expanded from: LGPL (≥ 2.1)]
URL:	https://dbi.r-dbi.org,https://github.com/r-dbi/DBI
BugReports:	https://github.com/r-dbi/DBI/issues
Depends:	methods, R (≥ 3.0.0)
Suggests:	arrow, blob, covr, DBItest, dbplyr, downlit, dplyr, glue,hms, knitr, magrittr, nanoarrow (≥ 0.3.0.1), RMariaDB,rmarkdown, rprojroot, RSQLite (≥ 1.1-2), testthat (≥ 3.0.0),vctrs, xml2
VignetteBuilder:	knitr
Config/autostyle/scope:	line_breaks
Config/autostyle/strict:	false
Config/Needs/check:	r-dbi/DBItest
Encoding:	UTF-8
RoxygenNote:	7.3.1
Config/Needs/website:	r-dbi/DBItest, r-dbi/dbitemplate, adbi,AzureKusto, bigrquery, DatabaseConnector, dittodb, duckdb,implyr, lazysf, odbc, pool, RAthena, IMSMWU/RClickhouse, RH2,RJDBC, RMariaDB, RMySQL, RPostgres, RPostgreSQL, RPresto,RSQLite, sergeant, sparklyr, withr
Config/testthat/edition:	3
NeedsCompilation:	no
Packaged:	2024-06-02 20:26:05 UTC; kirill
Author:	R Special Interest Group on Databases (R-SIG-DB) [aut], Hadley Wickham [aut], Kirill Müller [aut, cre], R Consortium [fnd]
Maintainer:	Kirill Müller <kirill@cynkra.com>
Repository:	CRAN
Date/Publication:	2024-06-02 21:50:02 UTC

DBI: R Database Interface

Description

DBI defines an interface for communication between R and relational databasemanagement systems.All classes in this package are virtual and need to be extended bythe various R/DBMS implementations (so-calledDBI backends).

Definition

A DBI backend is an R packagewhich imports theDBIandmethodspackages.For better or worse, the names of many existing backends start with‘R’, e.g.,RSQLite,RMySQL,RSQLServer; it is upto the backend author to adopt this convention or not.

DBI classes and methods

A backend defines three classes,which are subclasses ofDBIDriver,DBIConnection,andDBIResult.The backend provides implementation for all methodsof these base classesthat are defined but not implemented by DBI.All methods defined inDBI are reexported (so that the package canbe used without having to attachDBI),and have an ellipsis... in their formals for extensibility.

Construction of the DBIDriver object

The backend must support creation of an instance of itsDBIDriver subclasswith aconstructor function.By default, its name is the package name without the leading ‘R’(if it exists), e.g.,SQLite for theRSQLite package.However, backend authors may choose a different name.The constructor must be exported, andit must be a functionthat is callable without arguments.DBI recommends to define a constructor with an empty argument list.

Author(s)

Maintainer: Kirill Müllerkirill@cynkra.com (ORCID)

Authors:

• R Special Interest Group on Databases (R-SIG-DB)

• Hadley Wickham

Other contributors:

• R Consortium [funder]

See Also

Important generics:dbConnect(),dbGetQuery(),dbReadTable(),dbWriteTable(),dbDisconnect()

Formal specification (currently work in progress and incomplete):vignette("spec", package = "DBI")

Examples

RSQLite::SQLite()

Keywords according to the SQL-92 standard

Description

A character vector of SQL-92 keywords, uppercase.

Usage

.SQL92Keywords

Format

An object of classcharacter of length 220.

Examples

"SELECT" %in% .SQL92Keywords

A dummy DBI connector that simulates ANSI-SQL compliance

Description

A dummy DBI connector that simulates ANSI-SQL compliance

Usage

ANSI()

Examples

ANSI()

DBIConnection class

Description

This virtual class encapsulates the connection to a DBMS, and it providesaccess to dynamic queries, result sets, DBMS session management(transactions), etc.

Implementation note

Individual drivers are free to implement single or multiple simultaneousconnections.

See Also

Other DBI classes:DBIConnector-class,DBIDriver-class,DBIObject-class,DBIResult-class,DBIResultArrow-class

Other DBIConnection generics:dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")condbDisconnect(con)## Not run: con <- dbConnect(RPostgreSQL::PostgreSQL(), "username", "password")condbDisconnect(con)## End(Not run)

DBIConnector class

Description

Wraps objects of theDBIDriver class to include connection options.The purpose of this class is to store both the driverand the connection options.A database connection can be establishedwith a call todbConnect(), passing only that objectwithout additional arguments.

Details

To prevent leakage of passwords and other credentials,this class supports delayed evaluation.All arguments can optionally be a function (callable without arguments).In such a case, the function is evaluated transparently when connecting indbGetConnectArgs().

See Also

Other DBI classes:DBIConnection-class,DBIDriver-class,DBIObject-class,DBIResult-class,DBIResultArrow-class

Other DBIConnector generics:dbConnect(),dbDataType(),dbGetConnectArgs(),dbIsReadOnly()

Examples

# Create a connector:cnr <- new("DBIConnector",  .drv = RSQLite::SQLite(),  .conn_args = list(dbname = ":memory:"))cnr# Establish a connection through this connector:con <- dbConnect(cnr)con# Access the database through this connection:dbGetQuery(con, "SELECT 1 AS a")dbDisconnect(con)

DBIDriver class

Description

Base class for all DBMS drivers (e.g., RSQLite, MySQL, PostgreSQL).The virtual classDBIDriver defines the operations for creatingconnections and defining data type mappings. Actual driver classes, forinstanceRPostgres,RMariaDB, etc. implement these operations in aDBMS-specific manner.

See Also

Other DBI classes:DBIConnection-class,DBIConnector-class,DBIObject-class,DBIResult-class,DBIResultArrow-class

Other DBIDriver generics:dbCanConnect(),dbConnect(),dbDataType(),dbDriver(),dbGetInfo(),dbIsReadOnly(),dbIsValid(),dbListConnections()

DBIObject class

Description

Base class for all other DBI classes (e.g., drivers, connections). Thisis a virtual Class: No objects may be created from it.

Details

More generally, the DBI defines a very small set of classes and generics thatallows users and applications access DBMS with a common interface. Thevirtual classes areDBIDriver that individual drivers extend,DBIConnection that represent instances of DBMS connections, andDBIResult that represent the result of a DBMS statement. These threeclasses extend the basic class ofDBIObject, which serves as the rootor parent of the class hierarchy.

Implementation notes

An implementation MUST provide methods for the following generics:

• dbGetInfo().

It MAY also provide methods for:

• summary(). Print a concise description of theobject. The default method invokesdbGetInfo(dbObj) and printsthe name-value pairs one per line. Individual implementations maytailor this appropriately.

See Also

Other DBI classes:DBIConnection-class,DBIConnector-class,DBIDriver-class,DBIResult-class,DBIResultArrow-class

Examples

drv <- RSQLite::SQLite()con <- dbConnect(drv)rs <- dbSendQuery(con, "SELECT 1")is(drv, "DBIObject")   ## Trueis(con, "DBIObject")   ## Trueis(rs, "DBIObject")dbClearResult(rs)dbDisconnect(con)

DBIResult class

Description

This virtual class describes the result and state of execution ofa DBMS statement (any statement, query or non-query). The result setkeeps track of whether the statement produces output how many rows wereaffected by the operation, how many rows have been fetched (if statement isa query), whether there are more rows to fetch, etc.

Implementation notes

Individual drivers are free to allow single or multipleactive results per connection.

The default show method displays a summary of the query using otherDBI generics.

See Also

Other DBI classes:DBIConnection-class,DBIConnector-class,DBIDriver-class,DBIObject-class,DBIResultArrow-class

Other DBIResult generics:dbBind(),dbClearResult(),dbColumnInfo(),dbFetch(),dbGetInfo(),dbGetRowCount(),dbGetRowsAffected(),dbGetStatement(),dbHasCompleted(),dbIsReadOnly(),dbIsValid(),dbQuoteLiteral(),dbQuoteString()

DBIResultArrow class

Description

This virtual class describes the result and state of execution ofa DBMS statement (any statement, query or non-query)for returning data as an Arrow object.

Implementation notes

Individual drivers are free to allow single or multipleactive results per connection.

The default show method displays a summary of the query using otherDBI generics.

See Also

Other DBI classes:DBIConnection-class,DBIConnector-class,DBIDriver-class,DBIObject-class,DBIResult-class

Other DBIResultArrow generics:dbBind(),dbClearResult(),dbFetchArrow(),dbFetchArrowChunk(),dbHasCompleted(),dbIsValid()

Refer to a table nested in a hierarchy (e.g. within a schema)

Description

Objects of classId have a single slotname, which is a character vector.ThedbQuoteIdentifier() method convertsId objects to strings.Support forId objects depends on the database backend.

They can be used in the following methods asname ortable argument:

• dbCreateTable()

• dbAppendTable()

• dbReadTable()

• dbWriteTable()

• dbExistsTable()

• dbRemoveTable()

Objects of this class are also returned fromdbListObjects().

Usage

Id(...)

Arguments

Examples

# Identifies a table in a specific schema:Id("dbo", "Customer")# You can name the components if you want, but it's not neededId(table = "Customer", schema = "dbo")# Create a SQL expression for an identifier:dbQuoteIdentifier(ANSI(), Id("nycflights13", "flights"))# Write a table in a specific schema:## Not run: dbWriteTable(con, Id("myschema", "mytable"), data.frame(a = 1))## End(Not run)

SQL quoting

Description

This set of classes and generics make it possible to flexibly deal with SQLescaping needs. By default, any user supplied input to a query should beescaped using eitherdbQuoteIdentifier() ordbQuoteString()depending on whether it refers to a table or variable name, or is a literalstring.These functions may return an object of theSQL class,which tells DBI functions that a character string does not need to be escapedanymore, to prevent double escaping.TheSQL class has associated theSQL() constructor function.

Usage

SQL(x, ..., names = NULL)

Arguments

Value

An object of classSQL.

Implementation notes

DBI provides default generics for SQL-92 compatible quoting. If the databaseuses a different convention, you will need to provide your own methods.Note that because of the way that S4 dispatch finds methods and becauseSQL inherits from character, if you implement (e.g.) a method fordbQuoteString(MyConnection, character), you will also need toimplementdbQuoteString(MyConnection, SQL) - this should simplyreturnx unchanged.

Examples

dbQuoteIdentifier(ANSI(), "SELECT")dbQuoteString(ANSI(), "SELECT")# SQL vectors are always passed through as isvar_name <- SQL("SELECT")var_namedbQuoteIdentifier(ANSI(), var_name)dbQuoteString(ANSI(), var_name)# This mechanism is used to prevent double escapingdbQuoteString(ANSI(), dbQuoteString(ANSI(), "SELECT"))

Insert rows into a table

Description

ThedbAppendTable() method assumes that the table has been createdbeforehand, e.g. withdbCreateTable().The default implementation callssqlAppendTableTemplate() and thendbExecute() with theparam argument.UsedbAppendTableArrow() to append data from an Arrow stream.

Usage

dbAppendTable(conn, name, value, ..., row.names = NULL)

Arguments

Details

Backends compliant toANSI SQL 99 which use⁠?⁠ as a placeholder for prepared queries don't needto override it. Backends with a different SQL syntax which use⁠?⁠as a placeholder for prepared queries can overridesqlAppendTable().Other backends (with different placeholders or with entirely differentways to create tables) need to override thedbAppendTable() method.

Therow.names argument is not supported by this method.Process the values withsqlRownamesToColumn() before calling this method.

Value

dbAppendTable() returns ascalarnumeric.

Failure modes

If the table does not exist,or the new data invalues is not a data frame or has different column names,an error is raised; the remote table remains unchanged.

An error is raised when calling this method for a closedor invalid connection.An error is also raisedifname cannot be processed withdbQuoteIdentifier() orif this results in a non-scalar.Invalid values for therow.names argument(non-scalars,unsupported data types,NA)also raise an error.

Passing avalue argument different toNULL to therow.names argument(in particularTRUE,NA,and a string)raises an error.

Specification

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 namesand column names.

The following data types must be supported at least,and be read identically withdbReadTable():

• integer

• numeric(the behavior forInf andNaN is not specified)

• logical

• NA as NULL

• 64-bit values (using"bigint" as field type); the result can be

  • converted to a numeric, which may lose precision,

  • converted a character vector, which gives the full decimalrepresentation

  • written to another table and read again unchanged

• character (in both UTF-8and native encodings),supporting empty strings(before and after non-empty strings)

• factor (returned as character,with a warning)

• list of raw(if supported by the database)

• objects of typeblob::blob(if supported by the database)

• date(if supported by the database;returned asDate)also for dates prior to 1970 or 1900 or after 2038

• time(if supported by the database;returned as objects that inherit fromdifftime)

• timestamp(if supported by the database;returned asPOSIXctrespecting the time zone but not necessarily preserving theinput time zone),also for timestamps prior to 1970 or 1900 or after 2038respecting the time zone but not necessarily preserving theinput time zone)

Mixing column types in the same table is supported.

Thename argument is processed as follows,to support databases that allow non-syntactic names for their objects:

• If an unquoted table name as string:dbAppendTable() will do the quoting,perhaps by callingdbQuoteIdentifier(conn, x = name)

• If the result of a call todbQuoteIdentifier(): no more quoting is doneto support databases that allow non-syntactic names for their objects:

Therow.names argument must beNULL, the default value.Row names are ignored.

Thevalue argument must be a data framewith a subset of the columns of the existing table.The order of the columns does not matter.

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbCreateTable(con, "iris", iris)dbAppendTable(con, "iris", iris)dbReadTable(con, "iris")dbDisconnect(con)

Insert rows into a table from an Arrow stream

Description

ThedbAppendTableArrow() method assumes that the table has been createdbeforehand, e.g. withdbCreateTableArrow().The default implementation callsdbAppendTable() for each chunkof the stream.UsedbAppendTable() to append data from a data.frame.

Usage

dbAppendTableArrow(conn, name, value, ...)

Arguments

Value

dbAppendTableArrow() returns ascalarnumeric.

Failure modes

If the table does not exist,or the new data invalues is not a data frame or has different column names,an error is raised; the remote table remains unchanged.

An error is raised when calling this method for a closedor invalid connection.An error is also raisedifname cannot be processed withdbQuoteIdentifier() orif this results in a non-scalar.

Specification

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 namesand column names.

The following data types must be supported at least,and be read identically withdbReadTable():

• integer

• numeric(the behavior forInf andNaN is not specified)

• logical

• NA as NULL

• 64-bit values (using"bigint" as field type); the result can be

  • converted to a numeric, which may lose precision,

  • converted a character vector, which gives the full decimalrepresentation

  • written to another table and read again unchanged

• character (in both UTF-8and native encodings),supporting empty strings(before and after non-empty strings)

• factor (possibly returned as character)

• objects of typeblob::blob(if supported by the database)

• date(if supported by the database;returned asDate)also for dates prior to 1970 or 1900 or after 2038

• time(if supported by the database;returned as objects that inherit fromdifftime)

• timestamp(if supported by the database;returned asPOSIXctrespecting the time zone but not necessarily preserving theinput time zone),also for timestamps prior to 1970 or 1900 or after 2038respecting the time zone but not necessarily preserving theinput time zone)

Mixing column types in the same table is supported.

Thename argument is processed as follows,to support databases that allow non-syntactic names for their objects:

• If an unquoted table name as string:dbAppendTableArrow() will do the quoting,perhaps by callingdbQuoteIdentifier(conn, x = name)

• If the result of a call todbQuoteIdentifier(): no more quoting is doneto support databases that allow non-syntactic names for their objects:

Thevalue argument must be a data framewith a subset of the columns of the existing table.The order of the columns does not matter.

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbCreateTableArrow(con, "iris", iris[0, ])dbAppendTableArrow(con, "iris", iris[1:5, ])dbReadTable(con, "iris")dbDisconnect(con)

Begin/commit/rollback SQL transactions

Description

A transaction encapsulates several SQL statements in an atomic unit.It is initiated withdbBegin() and either made persistent withdbCommit()or undone withdbRollback().In any case, the DBMS guarantees that either all or none of the statementshave a permanent effect.This helps ensuring consistency of write operations to multiple tables.

Usage

dbBegin(conn, ...)dbCommit(conn, ...)dbRollback(conn, ...)

Arguments

Details

Not all database engines implement transaction management, in which casethese methods should not be implemented for the specificDBIConnection subclass.

Value

dbBegin(),dbCommit() anddbRollback() returnTRUE, invisibly.

Failure modes

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 closedor invalid connection.In addition, a call todbCommit()ordbRollback()without a prior call todbBegin() raises an error.Nested transactions are not supported by DBI,an attempt to calldbBegin() twiceyields an error.

Specification

Actual support for transactions may vary between backends.A transaction is initiated by a call todbBegin()and committed by a call todbCommit().Data written in a transaction must persist after the transaction is committed.For example, a record that is missing when the transaction is startedbut is created during the transactionmust existboth duringand after the transaction,and also in a new connection.

A transactioncan also be aborted withdbRollback().All data written in such a transaction must be removed after thetransaction is rolled back.For example, a record that is missing when the transaction is startedbut is created during the transactionmust not exist anymore after the rollback.

Disconnection from a connection with an open transactioneffectively rolls back the transaction.All data written in such a transaction must be removed after thetransaction is rolled back.

The behavior is not specified if other arguments are passed to thesefunctions. In particular,RSQLite issues named transactionswith support for nestingif thename argument is set.

The transaction isolation level is not specified by DBI.

See Also

Self-contained transactions:dbWithTransaction()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "cash", data.frame(amount = 100))dbWriteTable(con, "account", data.frame(amount = 2000))# All operations are carried out as logical unit:dbBegin(con)withdrawal <- 300dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))dbCommit(con)dbReadTable(con, "cash")dbReadTable(con, "account")# Rolling back after detecting negative value on account:dbBegin(con)withdrawal <- 5000dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))if (dbReadTable(con, "account")$amount >= 0) {  dbCommit(con)} else {  dbRollback(con)}dbReadTable(con, "cash")dbReadTable(con, "account")dbDisconnect(con)

Bind values to a parameterized/prepared statement

Description

For parametrized or prepared statements,thedbSendQuery(),dbSendQueryArrow(), anddbSendStatement() functionscan be called with statements that contain placeholders for values.ThedbBind() anddbBindArrow() functions bind these placeholdersto actual values,and are intended to be called on the result setbefore callingdbFetch() ordbFetchArrow().The values are passed todbBind() as lists or data frames,and todbBindArrow() as a streamcreated bynanoarrow::as_nanoarrow_array_stream().

dbBindArrow() is experimental, as are the other⁠*Arrow⁠ functions.dbSendQuery() is compatible withdbBindArrow(), anddbSendQueryArrow()is compatible withdbBind().

Usage

dbBind(res, params, ...)dbBindArrow(res, params, ...)

Arguments

Details

DBI supports parametrized (or prepared) queries and statementsvia thedbBind() anddbBindArrow() generics.Parametrized queries are different from normal queriesin that they allow an arbitrary number of placeholders,which are later substituted by actual values.Parametrized queries (and statements) serve two purposes:

• 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.

• Separation of query syntax and parameters protects against SQL injection.

The placeholder format is currently not specified byDBI;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 withtheplaceholder_pattern tweak.Known examples are:

• ⁠?⁠ (positional matching in order of appearance) inRMariaDB andRSQLite

• ⁠$1⁠ (positional matching by index) inRPostgres andRSQLite

• ⁠:name⁠ and⁠$name⁠ (named matching) inRSQLite

Value

dbBind() returns the result set,invisibly,for queries issued bydbSendQuery() ordbSendQueryArrow() andalso for data manipulation statements issued bydbSendStatement().

The data retrieval flow

This section gives a complete overview over the flowfor the execution of queries that return tabular data as data frames.

Most of this flow, except repeated calling ofdbBind() ordbBindArrow(),is implemented bydbGetQuery(), which should be sufficientunless you want to access the results in a paged wayor you have a parameterized query that you want to reuse.This flow requires an active connection established bydbConnect().See alsovignette("dbi-advanced") for a walkthrough.

1. UsedbSendQuery() to create a result set object of classDBIResult.

2. Optionally, bind query parameters withdbBind() ordbBindArrow().This is required only if the query contains placeholderssuch as⁠?⁠ or⁠$1⁠, depending on the database backend.

3. Optionally, usedbColumnInfo() to retrieve the structure of the result setwithout retrieving actual data.

4. UsedbFetch() 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 setas a data frame.This step can be called multiple times.Only forward paging is supported, you need to cache previous pagesif you need to navigate backwards.

5. UsedbHasCompleted() to tell when you're done.This method returnsTRUE if no more rows are available for fetching.

6. Repeat the last four steps as necessary.

7. UsedbClearResult() to clean up the result set object.This step is mandatory even if no rows have been fetchedor if an error has occurred during the processing.It is good practice to useon.exit() orwithr::defer()to ensure that this step is always executed.

The data retrieval flow for Arrow streams

This section gives a complete overview over the flowfor the execution of queries that return tabular data as an Arrow stream.

Most of this flow, except repeated calling ofdbBindArrow() ordbBind(),is implemented bydbGetQueryArrow(),which should be sufficientunless you have a parameterized query that you want to reuse.This flow requires an active connection established bydbConnect().See alsovignette("dbi-advanced") for a walkthrough.

1. UsedbSendQueryArrow() to create a result set object of classDBIResultArrow.

2. Optionally, bind query parameters withdbBindArrow() ordbBind().This is required only if the query contains placeholderssuch as⁠?⁠ or⁠$1⁠, depending on the database backend.

3. UsedbFetchArrow() to get a data stream.

4. Repeat the last two steps as necessary.

5. UsedbClearResult() to clean up the result set object.This step is mandatory even if no rows have been fetchedor if an error has occurred during the processing.It is good practice to useon.exit() orwithr::defer()to ensure that this step is always executed.

The command execution flow

This section gives a complete overview over the flowfor the execution of SQL statements that have side effectssuch as stored procedures, inserting or deleting data,or setting database or connection options.Most of this flow, except repeated calling ofdbBindArrow(),is implemented bydbExecute(), which should be sufficientfor non-parameterized queries.This flow requires an active connection established bydbConnect().See alsovignette("dbi-advanced") for a walkthrough.

1. UsedbSendStatement() to create a result set object of classDBIResult.For some queries you need to passimmediate = TRUE.

2. Optionally, bind query parameters withdbBind() ordbBindArrow().This is required only if the query contains placeholderssuch as⁠?⁠ or⁠$1⁠, depending on the database backend.

3. Optionally, usedbGetRowsAffected() to retrieve the numberof rows affected by the query.

4. Repeat the last two steps as necessary.

5. UsedbClearResult() to clean up the result set object.This step is mandatory even if no rows have been fetchedor if an error has occurred during the processing.It is good practice to useon.exit() orwithr::defer()to ensure that this step is always executed.

Failure modes

CallingdbBind() for a query without parametersraises an error.

Binding too manyor not enough values,or parameters with wrong namesor unequal length,also raises an error.If the placeholders in the query are named,all parameter values must have names(which must not be emptyorNA),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.

CallingdbBind() on a result set already cleared bydbClearResult()also raises an error.

Specification

DBI clients execute parametrized statements as follows:

1. CalldbSendQuery(),dbSendQueryArrow() ordbSendStatement()with a query or statement that contains placeholders,store the returnedDBIResult object in a variable.Mixing placeholders (in particular, named and unnamed ones) is notrecommended.It is good practice to register a call todbClearResult() viaon.exit() right after callingdbSendQuery() ordbSendStatement()(see the last enumeration item).UntildbBind() ordbBindArrow() have been called,the returned result set object has the following behavior:

   • dbFetch() raises an error (fordbSendQuery() anddbSendQueryArrow())

   • dbGetRowCount() returns zero (fordbSendQuery() anddbSendQueryArrow())

   • dbGetRowsAffected() returns an integerNA (fordbSendStatement())

   • dbIsValid() returnsTRUE

   • dbHasCompleted() returnsFALSE

2. CalldbBind() ordbBindArrow():

   • FordbBind(), theparams argument must be a list where all elementshave the same lengths and contain values supported by the backend.Adata.frame is internally stored as such a list.

   • FordbBindArrow(), theparams argument must be ananoarrow array stream, with one column per query parameter.

3. Retrieve the data or the number of affected rows from theDBIResult object.

   • For queries issued bydbSendQuery() ordbSendQueryArrow(), calldbFetch().

   • For statements issued bydbSendStatements(),calldbGetRowsAffected().(Execution begins immediately after thedbBind() call,the statement is processed entirely before the function returns.)

4. Repeat 2. and 3. as necessary.

5. Close the result set viadbClearResult().

The elements of theparams argument do not need to be scalars,vectors of arbitrary length(including length 0)are supported.For queries, callingdbFetch() binding such parameters returnsconcatenated results, equivalent to binding and fetching for each setof values and connecting viarbind().For data manipulation statements,dbGetRowsAffected() returns thetotal number of rows affected if binding non-scalar parameters.dbBind() also accepts repeated calls on the same result setfor both queriesand data manipulation statements,even if no results are fetched between calls todbBind(),for both queriesand data manipulation statements.

If the placeholders in the query are named,their order in theparams argument is not important.

At least the following data types are accepted on input (includingNA):

• integer

• numeric

• logical for Boolean values

• character(also with special characters such as spaces, newlines, quotes, and backslashes)

• factor (bound as character,with warning)

• Date(also when stored internally as integer)

• POSIXct timestamps

• POSIXlt timestamps

• difftime values(also with units other than secondsand with the value stored as integer)

• lists ofraw for blobs (withNULL entries for SQL NULL values)

• objects of typeblob::blob

See Also

Other DBIResult generics:DBIResult-class,dbClearResult(),dbColumnInfo(),dbFetch(),dbGetInfo(),dbGetRowCount(),dbGetRowsAffected(),dbGetStatement(),dbHasCompleted(),dbIsReadOnly(),dbIsValid(),dbQuoteLiteral(),dbQuoteString()

Other DBIResultArrow generics:DBIResultArrow-class,dbClearResult(),dbFetchArrow(),dbFetchArrowChunk(),dbHasCompleted(),dbIsValid()

Other data retrieval generics:dbClearResult(),dbFetch(),dbFetchArrow(),dbFetchArrowChunk(),dbGetQuery(),dbGetQueryArrow(),dbHasCompleted(),dbSendQuery(),dbSendQueryArrow()

Other command execution generics:dbClearResult(),dbExecute(),dbGetRowsAffected(),dbSendStatement()

Examples

# Data frame flow:con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "iris", iris)# Using the same query for different valuesiris_result <- dbSendQuery(con, "SELECT * FROM iris WHERE [Petal.Width] > ?")dbBind(iris_result, list(2.3))dbFetch(iris_result)dbBind(iris_result, list(3))dbFetch(iris_result)dbClearResult(iris_result)# Executing the same statement with different values at onceiris_result <- dbSendStatement(con, "DELETE FROM iris WHERE [Species] = $species")dbBind(iris_result, list(species = c("setosa", "versicolor", "unknown")))dbGetRowsAffected(iris_result)dbClearResult(iris_result)nrow(dbReadTable(con, "iris"))dbDisconnect(con)# Arrow flow:con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "iris", iris)# Using the same query for different valuesiris_result <- dbSendQueryArrow(con, "SELECT * FROM iris WHERE [Petal.Width] > ?")dbBindArrow(  iris_result,  nanoarrow::as_nanoarrow_array_stream(data.frame(2.3, fix.empty.names = FALSE)))as.data.frame(dbFetchArrow(iris_result))dbBindArrow(  iris_result,  nanoarrow::as_nanoarrow_array_stream(data.frame(3, fix.empty.names = FALSE)))as.data.frame(dbFetchArrow(iris_result))dbClearResult(iris_result)# Executing the same statement with different values at onceiris_result <- dbSendStatement(con, "DELETE FROM iris WHERE [Species] = $species")dbBindArrow(iris_result, nanoarrow::as_nanoarrow_array_stream(data.frame(  species = c("setosa", "versicolor", "unknown"))))dbGetRowsAffected(iris_result)dbClearResult(iris_result)nrow(dbReadTable(con, "iris"))dbDisconnect(con)

Call an SQL stored procedure

Description

Deprecated since 2014

Usage

dbCallProc(conn, ...)

Arguments

Details

The recommended way of calling a stored procedure is now

1. dbGetQuery if a result set is returned

2. dbExecute for data manipulation and other cases where no result set is returned

Check if a connection to a DBMS can be established

Description

LikedbConnect(), but only checks validity without actually returninga connection object. The default implementation opens a connectionand disconnects on success, but individual backends might implementa lighter-weight check.

Usage

dbCanConnect(drv, ...)

Arguments

Value

A scalar logical. IfFALSE, the"reason" attribute indicatesa reason for failure.

See Also

Other DBIDriver generics:DBIDriver-class,dbConnect(),dbDataType(),dbDriver(),dbGetInfo(),dbIsReadOnly(),dbIsValid(),dbListConnections()

Examples

# SQLite only needs a path to the database. (Here, ":memory:" is a special# path that creates an in-memory database.) Other database drivers# will require more details (like user, password, host, port, etc.)dbCanConnect(RSQLite::SQLite(), ":memory:")

Clear a result set

Description

Frees all resources (local and remote) associated with a result set.This step is mandatory for all objects obtained by callingdbSendQuery() ordbSendStatement().

Usage

dbClearResult(res, ...)

Arguments

Value

dbClearResult() returnsTRUE, invisibly, for result sets obtained fromdbSendQuery(),dbSendStatement(),ordbSendQueryArrow(),

The data retrieval flow

This section gives a complete overview over the flowfor the execution of queries that return tabular data as data frames.

Most of this flow, except repeated calling ofdbBind() ordbBindArrow(),is implemented bydbGetQuery(), which should be sufficientunless you want to access the results in a paged wayor you have a parameterized query that you want to reuse.This flow requires an active connection established bydbConnect().See alsovignette("dbi-advanced") for a walkthrough.

1. UsedbSendQuery() to create a result set object of classDBIResult.

2. Optionally, bind query parameters withdbBind() ordbBindArrow().This is required only if the query contains placeholderssuch as⁠?⁠ or⁠$1⁠, depending on the database backend.

3. Optionally, usedbColumnInfo() to retrieve the structure of the result setwithout retrieving actual data.

4. UsedbFetch() 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 setas a data frame.This step can be called multiple times.Only forward paging is supported, you need to cache previous pagesif you need to navigate backwards.

5. UsedbHasCompleted() to tell when you're done.This method returnsTRUE if no more rows are available for fetching.

6. Repeat the last four steps as necessary.

7. UsedbClearResult() to clean up the result set object.This step is mandatory even if no rows have been fetchedor if an error has occurred during the processing.It is good practice to useon.exit() orwithr::defer()to ensure that this step is always executed.

The command execution flow

This section gives a complete overview over the flowfor the execution of SQL statements that have side effectssuch as stored procedures, inserting or deleting data,or setting database or connection options.Most of this flow, except repeated calling ofdbBindArrow(),is implemented bydbExecute(), which should be sufficientfor non-parameterized queries.This flow requires an active connection established bydbConnect().See alsovignette("dbi-advanced") for a walkthrough.

1. UsedbSendStatement() to create a result set object of classDBIResult.For some queries you need to passimmediate = TRUE.

2. Optionally, bind query parameters withdbBind() ordbBindArrow().This is required only if the query contains placeholderssuch as⁠?⁠ or⁠$1⁠, depending on the database backend.

3. Optionally, usedbGetRowsAffected() to retrieve the numberof rows affected by the query.

4. Repeat the last two steps as necessary.

5. UsedbClearResult() to clean up the result set object.This step is mandatory even if no rows have been fetchedor if an error has occurred during the processing.It is good practice to useon.exit() orwithr::defer()to ensure that this step is always executed.

Failure modes

An attempt to close an already closed result set issues a warningfordbSendQuery(),dbSendStatement(),anddbSendQueryArrow(),

Specification

dbClearResult() frees all resources associated with retrievingthe result of a query or update operation.The DBI backend can expect a call todbClearResult() for eachdbSendQuery() ordbSendStatement() call.

See Also

Other DBIResult generics:DBIResult-class,dbBind(),dbColumnInfo(),dbFetch(),dbGetInfo(),dbGetRowCount(),dbGetRowsAffected(),dbGetStatement(),dbHasCompleted(),dbIsReadOnly(),dbIsValid(),dbQuoteLiteral(),dbQuoteString()

Other DBIResultArrow generics:DBIResultArrow-class,dbBind(),dbFetchArrow(),dbFetchArrowChunk(),dbHasCompleted(),dbIsValid()

Other data retrieval generics:dbBind(),dbFetch(),dbFetchArrow(),dbFetchArrowChunk(),dbGetQuery(),dbGetQueryArrow(),dbHasCompleted(),dbSendQuery(),dbSendQueryArrow()

Other command execution generics:dbBind(),dbExecute(),dbGetRowsAffected(),dbSendStatement()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")rs <- dbSendQuery(con, "SELECT 1")print(dbFetch(rs))dbClearResult(rs)dbDisconnect(con)

Information about result types

Description

Produces a data.frame that describes the output of a query. The data.frameshould have as many rows as there are output fields in the result set, andeach column in the data.frame describes an aspect of the result setfield (field name, type, etc.)

Usage

dbColumnInfo(res, ...)

Arguments

Value

dbColumnInfo()returns a data framewith at least two columns"name" and"type" (in that order)(and optional columns that start with a dot).The"name" and"type" columns contain the names and typesof the R columns of the data frame that is returned fromdbFetch().The"type" column is of typecharacter and only for information.Do not compute on the"type" column, instead usedbFetch(res, n = 0)to create a zero-row data frame initialized with the correct data types.

The data retrieval flow

This section gives a complete overview over the flowfor the execution of queries that return tabular data as data frames.

Most of this flow, except repeated calling ofdbBind() ordbBindArrow(),is implemented bydbGetQuery(), which should be sufficientunless you want to access the results in a paged wayor you have a parameterized query that you want to reuse.This flow requires an active connection established bydbConnect().See alsovignette("dbi-advanced") for a walkthrough.

1. UsedbSendQuery() to create a result set object of classDBIResult.

2. Optionally, bind query parameters withdbBind() ordbBindArrow().This is required only if the query contains placeholderssuch as⁠?⁠ or⁠$1⁠, depending on the database backend.

3. Optionally, usedbColumnInfo() to retrieve the structure of the result setwithout retrieving actual data.

4. UsedbFetch() 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 setas a data frame.This step can be called multiple times.Only forward paging is supported, you need to cache previous pagesif you need to navigate backwards.

5. UsedbHasCompleted() to tell when you're done.This method returnsTRUE if no more rows are available for fetching.

6. Repeat the last four steps as necessary.

7. UsedbClearResult() to clean up the result set object.This step is mandatory even if no rows have been fetchedor if an error has occurred during the processing.It is good practice to useon.exit() orwithr::defer()to ensure that this step is always executed.

Failure modes

An attempt to query columns for a closed result set raises an error.

Specification

A column namedrow_names is treated like any other column.

The column names are always consistentwith the data returned bydbFetch().

If the query returns unnamed columns,non-empty and non-NA names are assigned.

Column names that correspond to SQL or R keywords are left unchanged.

See Also

Other DBIResult generics:DBIResult-class,dbBind(),dbClearResult(),dbFetch(),dbGetInfo(),dbGetRowCount(),dbGetRowsAffected(),dbGetStatement(),dbHasCompleted(),dbIsReadOnly(),dbIsValid(),dbQuoteLiteral(),dbQuoteString()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")rs <- dbSendQuery(con, "SELECT 1 AS a, 2 AS b")dbColumnInfo(rs)dbFetch(rs)dbClearResult(rs)dbDisconnect(con)

Create a connection to a DBMS

Description

Connect to a DBMS going through the appropriate authentication procedure.Some implementations may allow you to have multiple connections open, so youmay invoke this function repeatedly assigning its output to differentobjects.The authentication mechanism is left unspecified, so check thedocumentation of individual drivers for details.UsedbCanConnect() to check if a connection can be established.

Usage

dbConnect(drv, ...)

Arguments

Value

dbConnect() returns an S4 object that inherits fromDBIConnection.This object is used to communicate with the database engine.

Aformat() method is defined for the connection object.It returns a string that consists of a single line of text.

Specification

DBI recommends using the following argument names for authenticationparameters, withNULL default:

• user for the user name (default: current user)

• password for the password

• host for the host name (default: local connection)

• port for the port number (default: local connection)

• dbname for the name of the database on the host, or the database filename

The defaults should provide reasonable behavior, in particular alocal connection forhost = NULL. For some DBMS (e.g., PostgreSQL),this is different to a TCP/IP connection tolocalhost.

In addition, DBI supports thebigint argument that governs how64-bit integer data is returned. The following values are supported:

• "integer": always return asinteger, silently overflow

• "numeric": always return asnumeric, silently round

• "character": always return the decimal representation ascharacter

• "integer64": return as a data type that can be coerced usingas.integer() (with warning on overflow),as.numeric()andas.character()

See Also

dbDisconnect() to disconnect from a database.

Other DBIDriver generics:DBIDriver-class,dbCanConnect(),dbDataType(),dbDriver(),dbGetInfo(),dbIsReadOnly(),dbIsValid(),dbListConnections()

Other DBIConnector generics:DBIConnector-class,dbDataType(),dbGetConnectArgs(),dbIsReadOnly()

Examples

# SQLite only needs a path to the database. (Here, ":memory:" is a special# path that creates an in-memory database.) Other database drivers# will require more details (like user, password, host, port, etc.)con <- dbConnect(RSQLite::SQLite(), ":memory:")condbListTables(con)dbDisconnect(con)# Bad, for subtle reasons:# This code fails when RSQLite isn't loaded yet,# because dbConnect() doesn't know yet about RSQLite.dbListTables(con <- dbConnect(RSQLite::SQLite(), ":memory:"))

Create a table in the database

Description

The defaultdbCreateTable() method callssqlCreateTable() anddbExecute().UsedbCreateTableArrow() to create a table from an Arrow schema.

Usage

dbCreateTable(conn, name, fields, ..., row.names = NULL, temporary = FALSE)

Arguments

Details

Backends compliant to ANSI SQL 99 don't need to override it.Backends with a different SQL syntax can overridesqlCreateTable(),backends with entirely different ways to create tables need tooverride this method.

Therow.names argument is not supported by this method.Process the values withsqlRownamesToColumn() before calling this method.

The argument order is different from thesqlCreateTable() method, thelatter will be adapted in a later release of DBI.

Value

dbCreateTable() returnsTRUE, invisibly.

Failure modes

If the table exists, an error is raised; the remote table remains unchanged.

An error is raised when calling this method for a closedor invalid connection.An error is also raisedifname cannot be processed withdbQuoteIdentifier() orif this results in a non-scalar.Invalid values for therow.names andtemporary arguments(non-scalars,unsupported data types,NA,incompatible values,duplicate names)also raise an error.

Additional arguments

The following arguments are not part of thedbCreateTable() generic(to improve compatibility across backends)but are part of the DBI specification:

• temporary (default:FALSE)

They must be provided as named arguments.See the "Specification" and "Value" sections for details on their usage.

Specification

Thename argument is processed as follows,to support databases that allow non-syntactic names for their objects:

• If an unquoted table name as string:dbCreateTable() will do the quoting,perhaps by callingdbQuoteIdentifier(conn, x = name)

• If the result of a call todbQuoteIdentifier(): no more quoting is done

Thevalue argument can be:

• a data frame,

• a named list of SQL types

If thetemporary argument isTRUE, the table is not available in asecond 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.

SQL keywords can be used freely in table names, column names, and data.Quotes, commas, and spaces can also be used for table names and column names,if the database supports non-syntactic identifiers.

Therow.names argument must be missingorNULL, the default value.All other values for therow.names argument(in particularTRUE,NA,and a string)raise an error.

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbCreateTable(con, "iris", iris)dbReadTable(con, "iris")dbDisconnect(con)

Create a table in the database based on an Arrow object

Description

The defaultdbCreateTableArrow() method determines the R data typesof the Arrow schema associated with the Arrow object,and callsdbCreateTable().Backends that implementdbAppendTableArrow() should typicallyalso implement this generic.UsedbCreateTable() to create a table from the column typesas defined in a data frame.

Usage

dbCreateTableArrow(conn, name, value, ..., temporary = FALSE)

Arguments

Value

dbCreateTableArrow() returnsTRUE, invisibly.

Failure modes

If the table exists, an error is raised; the remote table remains unchanged.

An error is raised when calling this method for a closedor invalid connection.An error is also raisedifname cannot be processed withdbQuoteIdentifier() orif this results in a non-scalar.Invalid values for thetemporary argument(non-scalars,unsupported data types,NA,incompatible values,duplicate names)also raise an error.

Additional arguments

The following arguments are not part of thedbCreateTableArrow() generic(to improve compatibility across backends)but are part of the DBI specification:

• temporary (default:FALSE)

They must be provided as named arguments.See the "Specification" and "Value" sections for details on their usage.

Specification

Thename argument is processed as follows,to support databases that allow non-syntactic names for their objects:

• If an unquoted table name as string:dbCreateTableArrow() will do the quoting,perhaps by callingdbQuoteIdentifier(conn, x = name)

• If the result of a call todbQuoteIdentifier(): no more quoting is done

Thevalue argument can be:

• a data frame,

• a nanoarrow array

• a nanoarrow array stream(which will still contain the data after the call)

• a nanoarrow schema

If thetemporary argument isTRUE, the table is not available in asecond 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.

SQL keywords can be used freely in table names, column names, and data.Quotes, commas, and spaces can also be used for table names and column names,if the database supports non-syntactic identifiers.

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")ptype <- data.frame(a = numeric())dbCreateTableArrow(con, "df", nanoarrow::infer_nanoarrow_schema(ptype))dbReadTable(con, "df")dbDisconnect(con)

Determine the SQL data type of an object

Description

Returns an SQL string that describes the SQL data type to be used for anobject.The default implementation of this generic determines the SQL type of anR object according to the SQL 92 specification, which may serve as a startingpoint for driver implementations. DBI also provides an implementationfor data.frame which will return a character vector giving the type for eachcolumn in the dataframe.

Usage

dbDataType(dbObj, obj, ...)

Arguments

Details

The data types supported by databases are different than the data types in R,but the mapping between the primitive types is straightforward:

• Any of the many fixed and varying length character types are mapped tocharacter vectors

• Fixed-precision (non-IEEE) numbers are mapped into either numeric orinteger vectors.

Notice that many DBMS do not follow IEEE arithmetic, so there are potentialproblems with under/overflows and loss of precision.

Value

dbDataType() returns the SQL type that corresponds to theobj argumentas a non-emptycharacter string.For data frames, a character vector with one element per columnis returned.

Failure modes

An error is raised for invalid values for theobj argument such as aNULL value.

Specification

The backend can override thedbDataType() genericfor its driver class.

This generic expects an arbitrary object as second argument.To query the values returned by the default implementation,runexample(dbDataType, package = "DBI").If the backend needs to override this generic,it must accept all basic R data types as its second argument, namelylogical,integer,numeric,character,dates (seeDates),date-time (seeDateTimeClasses),anddifftime.If the database supports blobs,this method also must accept lists ofraw vectors,andblob::blob objects.As-is objects (i.e., wrapped byI()) must besupported and return the same results as their unwrapped counterparts.The SQL data type forfactor andordered is the same as for character.The behavior for other object types is not specified.

All data types returned bydbDataType() are usable in an SQL statementof the form"CREATE TABLE test (a ...)".

See Also

Other DBIDriver generics:DBIDriver-class,dbCanConnect(),dbConnect(),dbDriver(),dbGetInfo(),dbIsReadOnly(),dbIsValid(),dbListConnections()

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Other DBIConnector generics:DBIConnector-class,dbConnect(),dbGetConnectArgs(),dbIsReadOnly()

Examples

dbDataType(ANSI(), 1:5)dbDataType(ANSI(), 1)dbDataType(ANSI(), TRUE)dbDataType(ANSI(), Sys.Date())dbDataType(ANSI(), Sys.time())dbDataType(ANSI(), Sys.time() - as.POSIXct(Sys.Date()))dbDataType(ANSI(), c("x", "abc"))dbDataType(ANSI(), list(raw(10), raw(20)))dbDataType(ANSI(), I(3))dbDataType(ANSI(), iris)con <- dbConnect(RSQLite::SQLite(), ":memory:")dbDataType(con, 1:5)dbDataType(con, 1)dbDataType(con, TRUE)dbDataType(con, Sys.Date())dbDataType(con, Sys.time())dbDataType(con, Sys.time() - as.POSIXct(Sys.Date()))dbDataType(con, c("x", "abc"))dbDataType(con, list(raw(10), raw(20)))dbDataType(con, I(3))dbDataType(con, iris)dbDisconnect(con)

Disconnect (close) a connection

Description

This closes the connection, discards all pending work, and freesresources (e.g., memory, sockets).

Usage

dbDisconnect(conn, ...)

Arguments

Value

dbDisconnect() returnsTRUE, invisibly.

Failure modes

A warning is issued on garbage collection when a connection has beenreleased without callingdbDisconnect(),but this cannot be tested automatically.At least one warning is issued immediately when callingdbDisconnect() on analready disconnectedor invalid connection.

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbDisconnect(con)

Load and unload database drivers

Description

These methods are deprecated, please consult the documentation of theindividual backends for the construction of driver instances.

dbDriver() is a helper method used to create an new driver objectgiven the name of a database or the corresponding R package. It worksthrough convention: all DBI-extending packages should provide an exportedobject with the same name as the package.dbDriver() just looks forthis object in the right places: if you know what database you are connectingto, you should call the function directly.

dbUnloadDriver() is not implemented for modern backends.

Usage

dbDriver(drvName, ...)dbUnloadDriver(drv, ...)

Arguments

Details

The client part of the database communication isinitialized (typically dynamically loading C code, etc.) but note thatconnecting to the database engine itself needs to be done through calls todbConnect.

Value

In the case ofdbDriver, an driver object whose class extendsDBIDriver. This object may be used to create connections to theactual DBMS engine.

In the case ofdbUnloadDriver, a logical indicating whether theoperation succeeded or not.

See Also

Other DBIDriver generics:DBIDriver-class,dbCanConnect(),dbConnect(),dbDataType(),dbGetInfo(),dbIsReadOnly(),dbIsValid(),dbListConnections()

Other DBIDriver generics:DBIDriver-class,dbCanConnect(),dbConnect(),dbDataType(),dbGetInfo(),dbIsReadOnly(),dbIsValid(),dbListConnections()

Examples

# Create a RSQLite driver with a stringd <- dbDriver("SQLite")d# But better, access the object directlyRSQLite::SQLite()

Change database state

Description

Executes a statement and returns the number of rows affected.dbExecute() comes with a default implementation(which should work with most backends) that callsdbSendStatement(), thendbGetRowsAffected(), ensuring thatthe result is always freed bydbClearResult().For passing query parameters, seedbBind(), in particularthe "The command execution flow" section.

Usage

dbExecute(conn, statement, ...)

Arguments

Details

You can also usedbExecute() to call a stored procedurethat 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 setsuch as⁠INSERT INTO ... RETURNING ...⁠, usedbGetQuery() instead.

Value

dbExecute() always returns ascalarnumericthat specifies the number of rows affectedby the statement.

Implementation notes

Subclasses should override this method only if they provide some sort ofperformance optimization.

Failure modes

An error is raised when issuing a statement over a closedor invalid connection,if the syntax of the statement is invalid,or if the statement is not a non-NA string.

Additional arguments

The following arguments are not part of thedbExecute() generic(to improve compatibility across backends)but are part of the DBI specification:

• params (default:NULL)

• immediate (default:NULL)

They must be provided as named arguments.See the "Specification" sections for details on their usage.

Specification

Theparam argument allows passing query parameters, seedbBind() for details.

Specification for theimmediate argument

Theimmediate argument supports distinguishing between "direct"and "prepared" APIs offered by many database drivers.Passingimmediate = TRUE leads to immediate execution of thequery or statement, via the "direct" API (if supported by the driver).The defaultNULL means that the backend should choose whatever APImakes the most sense for the database, and (if relevant) tries theother API if the first attempt fails. A successful second attemptshould result in a message that suggests passing the correctimmediate argument.Examples for possible behaviors:

1. DBI backend defaults toimmediate = TRUE internally

   1. A query without parameters is passed: query is executed

   2. A query with parameters is passed:

      1. params not given: rejected immediately by the databasebecause of a syntax error in the query, the backend triesimmediate = FALSE (and gives a message)

      2. params given: query is executed usingimmediate = FALSE

2. DBI backend defaults toimmediate = FALSE internally

   1. A query without parameters is passed:

      1. simple query: query is executed

      2. "special" query (such as setting a config options): fails,the backend triesimmediate = TRUE (and gives a message)

   2. A query with parameters is passed:

      1. params not given: waiting for parameters viadbBind()

      2. params given: query is executed

See Also

For queries:dbSendQuery() anddbGetQuery().

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Other command execution generics:dbBind(),dbClearResult(),dbGetRowsAffected(),dbSendStatement()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "cars", head(cars, 3))dbReadTable(con, "cars")   # there are 3 rowsdbExecute(  con,  "INSERT INTO cars (speed, dist) VALUES (1, 1), (2, 2), (3, 3)")dbReadTable(con, "cars")   # there are now 6 rows# Pass values using the param argument:dbExecute(  con,  "INSERT INTO cars (speed, dist) VALUES (?, ?)",  params = list(4:7, 5:8))dbReadTable(con, "cars")   # there are now 10 rowsdbDisconnect(con)

Does a table exist?

Description

Returns if a table given by name exists in the database.

Usage

dbExistsTable(conn, name, ...)

Arguments

Value

dbExistsTable() returns a logical scalar,TRUE if the table or viewspecified by thename argument exists,FALSE otherwise.

This includes temporary tables if supported by the database.

Failure modes

An error is raised when calling this method for a closedor invalid connection.An error is also raisedifname cannot be processed withdbQuoteIdentifier() orif this results in a non-scalar.

Specification

Thename argument is processed as follows,to support databases that allow non-syntactic names for their objects:

• If an unquoted table name as string:dbExistsTable() will do thequoting,perhaps by callingdbQuoteIdentifier(conn, x = name)

• If the result of a call todbQuoteIdentifier(): no more quoting is done

For all tables listed bydbListTables(),dbExistsTable() returnsTRUE.

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbExistsTable(con, "iris")dbWriteTable(con, "iris", iris)dbExistsTable(con, "iris")dbDisconnect(con)

Fetch records from a previously executed query

Description

Fetch the nextn elements (rows) from the result set and return themas a data.frame.

Usage

dbFetch(res, n = -1, ...)fetch(res, n = -1, ...)

Arguments

Details

fetch() is provided for compatibility with older DBI clients - for allnew code you are strongly encouraged to usedbFetch(). The defaultimplementation fordbFetch() callsfetch() so that it is compatible withexisting code. Modern backends should implement fordbFetch() only.

Value

dbFetch() always returns adata.frame withas many rows as records were fetched and as manycolumns as fields in the result set,even if the result is a single valueor has oneor zero rows.Passingn = NA 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.

The data retrieval flow

This section gives a complete overview over the flowfor the execution of queries that return tabular data as data frames.

Most of this flow, except repeated calling ofdbBind() ordbBindArrow(),is implemented bydbGetQuery(), which should be sufficientunless you want to access the results in a paged wayor you have a parameterized query that you want to reuse.This flow requires an active connection established bydbConnect().See alsovignette("dbi-advanced") for a walkthrough.

1. UsedbSendQuery() to create a result set object of classDBIResult.

2. Optionally, bind query parameters withdbBind() ordbBindArrow().This is required only if the query contains placeholderssuch as⁠?⁠ or⁠$1⁠, depending on the database backend.

3. Optionally, usedbColumnInfo() to retrieve the structure of the result setwithout retrieving actual data.

4. UsedbFetch() 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 setas a data frame.This step can be called multiple times.Only forward paging is supported, you need to cache previous pagesif you need to navigate backwards.

5. UsedbHasCompleted() to tell when you're done.This method returnsTRUE if no more rows are available for fetching.

6. Repeat the last four steps as necessary.

7. UsedbClearResult() to clean up the result set object.This step is mandatory even if no rows have been fetchedor if an error has occurred during the processing.It is good practice to useon.exit() orwithr::defer()to ensure that this step is always executed.

Failure modes

An attempt to fetch from a closed result set raises an error.If then argument is not an atomic whole numbergreater or equal to -1 or Inf, an error is raised,but a subsequent call todbFetch() with propern argument succeeds.

CallingdbFetch() on a result set from a data manipulation querycreated bydbSendStatement() canbe fetched and return an empty data frame, with a warning.

Specification

Fetching multi-row queries with oneor more columns by default returns the entire result.Multi-row queries can also be fetched progressivelyby passing a whole number (integer ornumeric)as then argument.A value ofInf for then argument is supportedand also returns the full result.If more rows than available are fetched, the result is returned in fullwithout warning.If fewer rows than requested are returned, further fetches willreturn a data frame with zero rows.If zero rows are fetched, the columns of the data frame are still fullytyped.Fetching fewer rows than available is permitted,no warning is issued when clearing the result set.

A column namedrow_names is treated like any other column.

The column types of the returned data frame depend on the data returned:

• integer (or coercible to an integer) for integer values between -2^31 and 2^31 - 1,withNA for SQLNULL values

• numeric for numbers with a fractional component,with NA for SQLNULL values

• logical for Boolean values (some backends may return an integer);with NA for SQLNULL values

• character for text,with NA for SQLNULL values

• lists ofraw for blobswithNULL entries for SQL NULL values

• coercible usingas.Date() for dates,with NA for SQLNULL values(also applies to the return value of the SQL functioncurrent_date)

• coercible usinghms::as_hms() for times,with NA for SQLNULL values(also applies to the return value of the SQL functioncurrent_time)

• coercible usingas.POSIXct() for timestamps,with NA for SQLNULL values(also applies to the return value of the SQL functioncurrent_timestamp)

If dates and timestamps are supported by the backend, the following R types areused:

• Date for dates(also applies to the return value of the SQL functioncurrent_date)

• POSIXct for timestamps(also applies to the return value of the SQL functioncurrent_timestamp)

R has no built-in type with lossless support for the full range of 64-bitor larger integers. If 64-bit integers are returned from a query,the following rules apply:

• Values are returned in a container with support for the full range ofvalid 64-bit values (such as theinteger64 class of thebit64package)

• Coercion to numeric always returns a number that is as close as possibleto the true value

• Loss of precision when converting to numeric gives a warning

• Conversion to character always returns a lossless decimal representationof the data

See Also

Close the result set withdbClearResult() as soon as youfinish retrieving the records you want.

Other DBIResult generics:DBIResult-class,dbBind(),dbClearResult(),dbColumnInfo(),dbGetInfo(),dbGetRowCount(),dbGetRowsAffected(),dbGetStatement(),dbHasCompleted(),dbIsReadOnly(),dbIsValid(),dbQuoteLiteral(),dbQuoteString()

Other data retrieval generics:dbBind(),dbClearResult(),dbFetchArrow(),dbFetchArrowChunk(),dbGetQuery(),dbGetQueryArrow(),dbHasCompleted(),dbSendQuery(),dbSendQueryArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars)# Fetch all resultsrs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")dbFetch(rs)dbClearResult(rs)# Fetch in chunksrs <- dbSendQuery(con, "SELECT * FROM mtcars")while (!dbHasCompleted(rs)) {  chunk <- dbFetch(rs, 10)  print(nrow(chunk))}dbClearResult(rs)dbDisconnect(con)

Fetch records from a previously executed query as an Arrow object

Description

Fetch the result set and return it as an Arrow object.UsedbFetchArrowChunk() to fetch results in chunks.

Usage

dbFetchArrow(res, ...)

Arguments

Value

dbFetchArrow() always returns an object coercible to adata.frame withas many rows as records were fetched and as manycolumns as fields in the result set,even if the result is a single valueor has oneor zero rows.

The data retrieval flow for Arrow streams

This section gives a complete overview over the flowfor the execution of queries that return tabular data as an Arrow stream.

Most of this flow, except repeated calling ofdbBindArrow() ordbBind(),is implemented bydbGetQueryArrow(),which should be sufficientunless you have a parameterized query that you want to reuse.This flow requires an active connection established bydbConnect().See alsovignette("dbi-advanced") for a walkthrough.

1. UsedbSendQueryArrow() to create a result set object of classDBIResultArrow.

2. Optionally, bind query parameters withdbBindArrow() ordbBind().This is required only if the query contains placeholderssuch as⁠?⁠ or⁠$1⁠, depending on the database backend.

3. UsedbFetchArrow() to get a data stream.

4. Repeat the last two steps as necessary.

5. UsedbClearResult() to clean up the result set object.This step is mandatory even if no rows have been fetchedor if an error has occurred during the processing.It is good practice to useon.exit() orwithr::defer()to ensure that this step is always executed.

Failure modes

An attempt to fetch from a closed result set raises an error.

Specification

Fetching multi-row queries with oneor more columns by default returns the entire result.The object returned bydbFetchArrow() can also be passed tonanoarrow::as_nanoarrow_array_stream() to create a nanoarrowarray stream object that can be used to read the result setin batches.The chunk size is implementation-specific.

See Also

Close the result set withdbClearResult() as soon as youfinish retrieving the records you want.

Other DBIResultArrow generics:DBIResultArrow-class,dbBind(),dbClearResult(),dbFetchArrowChunk(),dbHasCompleted(),dbIsValid()

Other data retrieval generics:dbBind(),dbClearResult(),dbFetch(),dbFetchArrowChunk(),dbGetQuery(),dbGetQueryArrow(),dbHasCompleted(),dbSendQuery(),dbSendQueryArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars)# Fetch all resultsrs <- dbSendQueryArrow(con, "SELECT * FROM mtcars WHERE cyl = 4")as.data.frame(dbFetchArrow(rs))dbClearResult(rs)dbDisconnect(con)

Fetch the next batch of records from a previously executed query as an Arrow object

Description

Fetch the next chunk of the result set and return it as an Arrow object.The chunk size is implementation-specific.UsedbFetchArrow() to fetch all results.

Usage

dbFetchArrowChunk(res, ...)

Arguments

Value

dbFetchArrowChunk() always returns an object coercible to adata.frame withas many rows as records were fetched and as manycolumns as fields in the result set,even if the result is a single valueor has oneor zero rows.

The data retrieval flow for Arrow streams

This section gives a complete overview over the flowfor the execution of queries that return tabular data as an Arrow stream.

Most of this flow, except repeated calling ofdbBindArrow() ordbBind(),is implemented bydbGetQueryArrow(),which should be sufficientunless you have a parameterized query that you want to reuse.This flow requires an active connection established bydbConnect().See alsovignette("dbi-advanced") for a walkthrough.

1. UsedbSendQueryArrow() to create a result set object of classDBIResultArrow.

2. Optionally, bind query parameters withdbBindArrow() ordbBind().This is required only if the query contains placeholderssuch as⁠?⁠ or⁠$1⁠, depending on the database backend.

3. UsedbFetchArrow() to get a data stream.

4. Repeat the last two steps as necessary.

5. UsedbClearResult() to clean up the result set object.This step is mandatory even if no rows have been fetchedor if an error has occurred during the processing.It is good practice to useon.exit() orwithr::defer()to ensure that this step is always executed.

Failure modes

An attempt to fetch from a closed result set raises an error.

Specification

Fetching multi-row queries with oneor more columns returns the next chunk.The size of the chunk is implementation-specific.The object returned bydbFetchArrowChunk() can also be passed tonanoarrow::as_nanoarrow_array() to create a nanoarrow array object.The chunk size is implementation-specific.

See Also

Close the result set withdbClearResult() as soon as youfinish retrieving the records you want.

Other DBIResultArrow generics:DBIResultArrow-class,dbBind(),dbClearResult(),dbFetchArrow(),dbHasCompleted(),dbIsValid()

Other data retrieval generics:dbBind(),dbClearResult(),dbFetch(),dbFetchArrow(),dbGetQuery(),dbGetQueryArrow(),dbHasCompleted(),dbSendQuery(),dbSendQueryArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars)# Fetch all resultsrs <- dbSendQueryArrow(con, "SELECT * FROM mtcars WHERE cyl = 4")dbHasCompleted(rs)as.data.frame(dbFetchArrowChunk(rs))dbHasCompleted(rs)as.data.frame(dbFetchArrowChunk(rs))dbClearResult(rs)dbDisconnect(con)

Get connection arguments

Description

Returns the arguments stored in aDBIConnector object for inspection,optionally evaluating them.This function is called bydbConnect()and usually does not need to be called directly.

Usage

dbGetConnectArgs(drv, eval = TRUE, ...)

Arguments

See Also

Other DBIConnector generics:DBIConnector-class,dbConnect(),dbDataType(),dbIsReadOnly()

Examples

cnr <- new("DBIConnector",  .drv = RSQLite::SQLite(),  .conn_args = list(dbname = ":memory:", password = function() "supersecret"))dbGetConnectArgs(cnr)dbGetConnectArgs(cnr, eval = FALSE)

Determine the current version of the package.

Description

Determine the current version of the package.

Usage

dbGetDBIVersion()

Get DBMS exceptions

Description

DEPRECATED. Backends should use R's condition system to signal errors andwarnings.

Usage

dbGetException(conn, ...)

Arguments

Value

a list with elementserrorNum (an integer error number) anderrorMsg (a character string) describing the last error in theconnectionconn.

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Get DBMS metadata

Description

Retrieves information on objects of classDBIDriver,DBIConnection orDBIResult.

Usage

dbGetInfo(dbObj, ...)

Arguments

Value

For objects of classDBIDriver,dbGetInfo()returns a named listthat contains at least the following components:

• driver.version: the package version of the DBI backend,

• client.version: the version of the DBMS client library.

For objects of classDBIConnection,dbGetInfo()returns a named listthat contains at least the following components:

• db.version: version of the database server,

• dbname: database name,

• username: username to connect to the database,

• host: hostname of the database server,

• port: port on the database server.It must not contain apassword component.Components that are not applicable should be set toNA.

For objects of classDBIResult,dbGetInfo()returns a named listthat contains at least the following components:

• statatment: the statement used withdbSendQuery() ordbExecute(),as returned bydbGetStatement(),

• row.count: the number of rows fetched so far (for queries),as returned bydbGetRowCount(),

• rows.affected: the number of rows affected (for statements),as returned bydbGetRowsAffected()

• has.completed: a logical that indicatesif the query or statement has completed,as returned bydbHasCompleted().

Implementation notes

The default implementation for⁠DBIResult objects⁠constructs such a list from the return values of the corresponding methods,dbGetStatement(),dbGetRowCount(),dbGetRowsAffected(), anddbHasCompleted().

See Also

Other DBIDriver generics:DBIDriver-class,dbCanConnect(),dbConnect(),dbDataType(),dbDriver(),dbIsReadOnly(),dbIsValid(),dbListConnections()

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Other DBIResult generics:DBIResult-class,dbBind(),dbClearResult(),dbColumnInfo(),dbFetch(),dbGetRowCount(),dbGetRowsAffected(),dbGetStatement(),dbHasCompleted(),dbIsReadOnly(),dbIsValid(),dbQuoteLiteral(),dbQuoteString()

Examples

dbGetInfo(RSQLite::SQLite())

Retrieve results from a query

Description

Returns the result of a query as a data frame.dbGetQuery() comes with a default implementation(which should work with most backends) that callsdbSendQuery(), thendbFetch(), ensuring thatthe result is always freed bydbClearResult().For retrieving chunked/paged results or for passing query parameters,seedbSendQuery(), in particular the "The data retrieval flow" section.For retrieving results as an Arrow object, seedbGetQueryArrow().

Usage

dbGetQuery(conn, statement, ...)

Arguments

Details

This method is forSELECT queries only(incl. other SQL statements that return aSELECT-alike result,e.g., execution of a stored procedure or data manipulation querieslike⁠INSERT INTO ... RETURNING ...⁠).To execute a stored procedure that does not return a result set,usedbExecute().

Some backends maysupport data manipulation statements through this method for compatibilityreasons. However, callers are strongly advised to usedbExecute() for data manipulation statements.

Value

dbGetQuery() always returns adata.frame, withas many rows as records were fetched and as manycolumns as fields in the result set,even if the result is a single valueor has oneor zero rows.

Implementation notes

Subclasses should override this method only if they provide some sort ofperformance optimization.

Failure modes

An error is raised when issuing a query over a closedor invalid connection,if the syntax of the query is invalid,or if the query is not a non-NA string.If then argument is not an atomic whole numbergreater or equal to -1 or Inf, an error is raised,but a subsequent call todbGetQuery() with propern argument succeeds.

Additional arguments

The following arguments are not part of thedbGetQuery() generic(to improve compatibility across backends)but are part of the DBI specification:

• n (default: -1)

• params (default:NULL)

• immediate (default:NULL)

They must be provided as named arguments.See the "Specification" and "Value" sections for details on their usage.

Specification

A column namedrow_names is treated like any other column.

Then argument specifies the number of rows to be fetched.If omitted, fetching multi-row queries with oneor more columns returns the entire result.A value ofInf for then argument is supportedand also returns the full result.If more rows than available are fetched (by passing a too large value forn), the result is returned in full without warning.If zero rows are requested, the columns of the data frame are still fullytyped.Fetching fewer rows than available is permitted,no warning is issued.

Theparam argument allows passing query parameters, seedbBind() for details.

Specification for theimmediate argument

Theimmediate argument supports distinguishing between "direct"and "prepared" APIs offered by many database drivers.Passingimmediate = TRUE leads to immediate execution of thequery or statement, via the "direct" API (if supported by the driver).The defaultNULL means that the backend should choose whatever APImakes the most sense for the database, and (if relevant) tries theother API if the first attempt fails. A successful second attemptshould result in a message that suggests passing the correctimmediate argument.Examples for possible behaviors:

1. DBI backend defaults toimmediate = TRUE internally

   1. A query without parameters is passed: query is executed

   2. A query with parameters is passed:

      1. params not given: rejected immediately by the databasebecause of a syntax error in the query, the backend triesimmediate = FALSE (and gives a message)

      2. params given: query is executed usingimmediate = FALSE

2. DBI backend defaults toimmediate = FALSE internally

   1. A query without parameters is passed:

      1. simple query: query is executed

      2. "special" query (such as setting a config options): fails,the backend triesimmediate = TRUE (and gives a message)

   2. A query with parameters is passed:

      1. params not given: waiting for parameters viadbBind()

      2. params given: query is executed

See Also

For updates:dbSendStatement() anddbExecute().

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Other data retrieval generics:dbBind(),dbClearResult(),dbFetch(),dbFetchArrow(),dbFetchArrowChunk(),dbGetQueryArrow(),dbHasCompleted(),dbSendQuery(),dbSendQueryArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars)dbGetQuery(con, "SELECT * FROM mtcars")dbGetQuery(con, "SELECT * FROM mtcars", n = 6)# Pass values using the param argument:# (This query runs eight times, once for each different# parameter. The resulting rows are combined into a single# data frame.)dbGetQuery(  con,  "SELECT COUNT(*) FROM mtcars WHERE cyl = ?",  params = list(1:8))dbDisconnect(con)

Retrieve results from a query as an Arrow object

Description

Returns the result of a query as an Arrow object.dbGetQueryArrow() comes with a default implementation(which should work with most backends) that callsdbSendQueryArrow(), thendbFetchArrow(), ensuring thatthe result is always freed bydbClearResult().For passing query parameters,seedbSendQueryArrow(), in particularthe "The data retrieval flow for Arrow streams" section.For retrieving results as a data frame, seedbGetQuery().

Usage

dbGetQueryArrow(conn, statement, ...)

Arguments

Details

This method is forSELECT queries only(incl. other SQL statements that return aSELECT-alike result,e.g., execution of a stored procedure or data manipulation querieslike⁠INSERT INTO ... RETURNING ...⁠).To execute a stored procedure that does not return a result set,usedbExecute().

Some backends maysupport data manipulation statements through this method.However, callers are strongly advised to usedbExecute() for data manipulation statements.

Value

dbGetQueryArrow() always returns an object coercible to adata.frame, withas many rows as records were fetched and as manycolumns as fields in the result set,even if the result is a single valueor has oneor zero rows.

Implementation notes

Subclasses should override this method only if they provide some sort ofperformance optimization.

Failure modes

An error is raised when issuing a query over a closedor invalid connection,if the syntax of the query is invalid,or if the query is not a non-NA string.The object returned bydbGetQueryArrow() can also be passed tonanoarrow::as_nanoarrow_array_stream() to create a nanoarrowarray stream object that can be used to read the result setin batches.The chunk size is implementation-specific.

Additional arguments

The following arguments are not part of thedbGetQueryArrow() generic(to improve compatibility across backends)but are part of the DBI specification:

• params (default:NULL)

• immediate (default:NULL)

They must be provided as named arguments.See the "Specification" and "Value" sections for details on their usage.

Theparam argument allows passing query parameters, seedbBind() for details.

Specification for theimmediate argument

Theimmediate argument supports distinguishing between "direct"and "prepared" APIs offered by many database drivers.Passingimmediate = TRUE leads to immediate execution of thequery or statement, via the "direct" API (if supported by the driver).The defaultNULL means that the backend should choose whatever APImakes the most sense for the database, and (if relevant) tries theother API if the first attempt fails. A successful second attemptshould result in a message that suggests passing the correctimmediate argument.Examples for possible behaviors:

1. DBI backend defaults toimmediate = TRUE internally

   1. A query without parameters is passed: query is executed

   2. A query with parameters is passed:

      1. params not given: rejected immediately by the databasebecause of a syntax error in the query, the backend triesimmediate = FALSE (and gives a message)

      2. params given: query is executed usingimmediate = FALSE

2. DBI backend defaults toimmediate = FALSE internally

   1. A query without parameters is passed:

      1. simple query: query is executed

      2. "special" query (such as setting a config options): fails,the backend triesimmediate = TRUE (and gives a message)

   2. A query with parameters is passed:

      1. params not given: waiting for parameters viadbBind()

      2. params given: query is executed

See Also

For updates:dbSendStatement() anddbExecute().

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Other data retrieval generics:dbBind(),dbClearResult(),dbFetch(),dbFetchArrow(),dbFetchArrowChunk(),dbGetQuery(),dbHasCompleted(),dbSendQuery(),dbSendQueryArrow()

Examples

# Retrieve data as arrow tablecon <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars)dbGetQueryArrow(con, "SELECT * FROM mtcars")dbDisconnect(con)

The number of rows fetched so far

Description

Returns the total number of rows actually fetched with calls todbFetch()for this result set.

Usage

dbGetRowCount(res, ...)

Arguments

Value

dbGetRowCount() returns a scalar number (integer or numeric),the number of rows fetched so far.After callingdbSendQuery(),the row count is initially zero.After a call todbFetch() without limit,the row count matches the total number of rows returned.Fetching a limited number of rowsincreases 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 returnedeven after fetching.For data manipulation statements issued withdbSendStatement(),zero is returned beforeand after callingdbFetch().

Failure modes

Attempting to get the row count for a result set cleared withdbClearResult() gives an error.

See Also

Other DBIResult generics:DBIResult-class,dbBind(),dbClearResult(),dbColumnInfo(),dbFetch(),dbGetInfo(),dbGetRowsAffected(),dbGetStatement(),dbHasCompleted(),dbIsReadOnly(),dbIsValid(),dbQuoteLiteral(),dbQuoteString()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars)rs <- dbSendQuery(con, "SELECT * FROM mtcars")dbGetRowCount(rs)ret1 <- dbFetch(rs, 10)dbGetRowCount(rs)ret2 <- dbFetch(rs)dbGetRowCount(rs)nrow(ret1) + nrow(ret2)dbClearResult(rs)dbDisconnect(con)

The number of rows affected

Description

This method returns the number of rows that were added, deleted, or updatedby a data manipulation statement.

Usage

dbGetRowsAffected(res, ...)

Arguments

Value

dbGetRowsAffected() returns a scalar number (integer or numeric),the number of rows affected by a data manipulation statementissued withdbSendStatement().The value is available directly after the calland does not change after callingdbFetch().NA_integer_ orNA_numeric_ are allowed if the number of rows affected is not known.

For queries issued withdbSendQuery(),zero is returned beforeand after the call todbFetch().NA values are not allowed.

The command execution flow

This section gives a complete overview over the flowfor the execution of SQL statements that have side effectssuch as stored procedures, inserting or deleting data,or setting database or connection options.Most of this flow, except repeated calling ofdbBindArrow(),is implemented bydbExecute(), which should be sufficientfor non-parameterized queries.This flow requires an active connection established bydbConnect().See alsovignette("dbi-advanced") for a walkthrough.

1. UsedbSendStatement() to create a result set object of classDBIResult.For some queries you need to passimmediate = TRUE.

2. Optionally, bind query parameters withdbBind() ordbBindArrow().This is required only if the query contains placeholderssuch as⁠?⁠ or⁠$1⁠, depending on the database backend.

3. Optionally, usedbGetRowsAffected() to retrieve the numberof rows affected by the query.

4. Repeat the last two steps as necessary.

5. UsedbClearResult() to clean up the result set object.This step is mandatory even if no rows have been fetchedor if an error has occurred during the processing.It is good practice to useon.exit() orwithr::defer()to ensure that this step is always executed.

Failure modes

Attempting to get the rows affected for a result set cleared withdbClearResult() gives an error.

See Also

Other DBIResult generics:DBIResult-class,dbBind(),dbClearResult(),dbColumnInfo(),dbFetch(),dbGetInfo(),dbGetRowCount(),dbGetStatement(),dbHasCompleted(),dbIsReadOnly(),dbIsValid(),dbQuoteLiteral(),dbQuoteString()

Other command execution generics:dbBind(),dbClearResult(),dbExecute(),dbSendStatement()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars)rs <- dbSendStatement(con, "DELETE FROM mtcars")dbGetRowsAffected(rs)nrow(mtcars)dbClearResult(rs)dbDisconnect(con)

Get the statement associated with a result set

Description

Returns the statement that was passed todbSendQuery()ordbSendStatement().

Usage

dbGetStatement(res, ...)

Arguments

Value

dbGetStatement() returns a string, the query used ineitherdbSendQuery() ordbSendStatement().

Failure modes

Attempting to query the statement for a result set cleared withdbClearResult() gives an error.

See Also

Other DBIResult generics:DBIResult-class,dbBind(),dbClearResult(),dbColumnInfo(),dbFetch(),dbGetInfo(),dbGetRowCount(),dbGetRowsAffected(),dbHasCompleted(),dbIsReadOnly(),dbIsValid(),dbQuoteLiteral(),dbQuoteString()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars)rs <- dbSendQuery(con, "SELECT * FROM mtcars")dbGetStatement(rs)dbClearResult(rs)dbDisconnect(con)

Completion status

Description

This method returns if the operation has completed.ASELECT query is completed if all rows have been fetched.A data manipulation statement is always completed.

Usage

dbHasCompleted(res, ...)

Arguments

Value

dbHasCompleted() returns a logical scalar.For a query initiated bydbSendQuery() with non-empty result set,dbHasCompleted() returnsFALSE initiallyandTRUE after callingdbFetch() without limit.For a query initiated bydbSendStatement(),dbHasCompleted() always returnsTRUE.

The data retrieval flow

This section gives a complete overview over the flowfor the execution of queries that return tabular data as data frames.

Most of this flow, except repeated calling ofdbBind() ordbBindArrow(),is implemented bydbGetQuery(), which should be sufficientunless you want to access the results in a paged wayor you have a parameterized query that you want to reuse.This flow requires an active connection established bydbConnect().See alsovignette("dbi-advanced") for a walkthrough.

1. UsedbSendQuery() to create a result set object of classDBIResult.

2. Optionally, bind query parameters withdbBind() ordbBindArrow().This is required only if the query contains placeholderssuch as⁠?⁠ or⁠$1⁠, depending on the database backend.

3. Optionally, usedbColumnInfo() to retrieve the structure of the result setwithout retrieving actual data.

4. UsedbFetch() 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 setas a data frame.This step can be called multiple times.Only forward paging is supported, you need to cache previous pagesif you need to navigate backwards.

5. UsedbHasCompleted() to tell when you're done.This method returnsTRUE if no more rows are available for fetching.

6. Repeat the last four steps as necessary.

7. UsedbClearResult() to clean up the result set object.This step is mandatory even if no rows have been fetchedor if an error has occurred during the processing.It is good practice to useon.exit() orwithr::defer()to ensure that this step is always executed.

Failure modes

Attempting to query completion status for a result set cleared withdbClearResult() gives an error.

Specification

The completion status for a query is only guaranteed to be set toFALSE 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 isTRUE after trying to fetch only one row.

Similarly, for a query with a result set of length n,the return value is unspecified after fetching n rows,but the result value isTRUE after trying to fetch only one morerow.

See Also

Other DBIResult generics:DBIResult-class,dbBind(),dbClearResult(),dbColumnInfo(),dbFetch(),dbGetInfo(),dbGetRowCount(),dbGetRowsAffected(),dbGetStatement(),dbIsReadOnly(),dbIsValid(),dbQuoteLiteral(),dbQuoteString()

Other DBIResultArrow generics:DBIResultArrow-class,dbBind(),dbClearResult(),dbFetchArrow(),dbFetchArrowChunk(),dbIsValid()

Other data retrieval generics:dbBind(),dbClearResult(),dbFetch(),dbFetchArrow(),dbFetchArrowChunk(),dbGetQuery(),dbGetQueryArrow(),dbSendQuery(),dbSendQueryArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars)rs <- dbSendQuery(con, "SELECT * FROM mtcars")dbHasCompleted(rs)ret1 <- dbFetch(rs, 10)dbHasCompleted(rs)ret2 <- dbFetch(rs)dbHasCompleted(rs)dbClearResult(rs)dbDisconnect(con)

Is this DBMS object read only?

Description

This generic tests whether a database object is read only.

Usage

dbIsReadOnly(dbObj, ...)

Arguments

See Also

Other DBIDriver generics:DBIDriver-class,dbCanConnect(),dbConnect(),dbDataType(),dbDriver(),dbGetInfo(),dbIsValid(),dbListConnections()

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Other DBIResult generics:DBIResult-class,dbBind(),dbClearResult(),dbColumnInfo(),dbFetch(),dbGetInfo(),dbGetRowCount(),dbGetRowsAffected(),dbGetStatement(),dbHasCompleted(),dbIsValid(),dbQuoteLiteral(),dbQuoteString()

Other DBIConnector generics:DBIConnector-class,dbConnect(),dbDataType(),dbGetConnectArgs()

Examples

dbIsReadOnly(ANSI())

Is this DBMS object still valid?

Description

This generic tests whether a database object is still valid (i.e. it hasn'tbeen disconnected or cleared).

Usage

dbIsValid(dbObj, ...)

Arguments

Value

dbIsValid() returns a logical scalar,TRUE if the object specified bydbObj is valid,FALSE otherwise.ADBIConnection object is initially valid,and becomes invalid after disconnecting withdbDisconnect().For an invalid connection object (e.g., for some drivers if the objectis saved to a file and then restored), the method also returnsFALSE.ADBIResult object is valid after a call todbSendQuery(),and stays valid even after all rows have been fetched;only clearing it withdbClearResult() invalidates it.ADBIResult object is also valid after a call todbSendStatement(),and stays valid after querying the number of rows affected;only clearing it withdbClearResult() invalidates it.If the connection to the database system is dropped (e.g., due toconnectivity problems, server failure, etc.),dbIsValid() should returnFALSE. This is not tested automatically.

See Also

Other DBIDriver generics:DBIDriver-class,dbCanConnect(),dbConnect(),dbDataType(),dbDriver(),dbGetInfo(),dbIsReadOnly(),dbListConnections()

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Other DBIResult generics:DBIResult-class,dbBind(),dbClearResult(),dbColumnInfo(),dbFetch(),dbGetInfo(),dbGetRowCount(),dbGetRowsAffected(),dbGetStatement(),dbHasCompleted(),dbIsReadOnly(),dbQuoteLiteral(),dbQuoteString()

Other DBIResultArrow generics:DBIResultArrow-class,dbBind(),dbClearResult(),dbFetchArrow(),dbFetchArrowChunk(),dbHasCompleted()

Examples

dbIsValid(RSQLite::SQLite())con <- dbConnect(RSQLite::SQLite(), ":memory:")dbIsValid(con)rs <- dbSendQuery(con, "SELECT 1")dbIsValid(rs)dbClearResult(rs)dbIsValid(rs)dbDisconnect(con)dbIsValid(con)

List currently open connections

Description

DEPRECATED, drivers are no longer required to implement this method.Keep track of the connections you opened if you require a list.

Usage

dbListConnections(drv, ...)

Arguments

Value

a list

See Also

Other DBIDriver generics:DBIDriver-class,dbCanConnect(),dbConnect(),dbDataType(),dbDriver(),dbGetInfo(),dbIsReadOnly(),dbIsValid()

List field names of a remote table

Description

Returns the field names of a remote table as a character vector.

Usage

dbListFields(conn, name, ...)

Arguments

Value

dbListFields()returns a character vectorthat enumerates all fieldsin the table in the correct order.This also works for temporary tables if supported by the database.The returned names are suitable for quoting withdbQuoteIdentifier().

Failure modes

If the table does not exist, an error is raised.Invalid types for thename argument(e.g.,character of length not equal to one,or numeric)lead to an error.An error is also raised when calling this method for a closedor invalid connection.

Specification

Thename argument can be

• a string

• the return value ofdbQuoteIdentifier()

• a value from thetable column from the return value ofdbListObjects() whereis_prefix isFALSE

A column namedrow_names is treated like any other column.

See Also

dbColumnInfo() to get the type of the fields.

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars)dbListFields(con, "mtcars")dbDisconnect(con)

List remote objects

Description

Returns the names of remote objects accessible through this connectionas a data frame.This should include temporary objects, but not all database backends(in particularRMariaDB andRMySQL) support this.Compared todbListTables(), this method also enumerates tables and viewsin schemas, and returns fully qualified identifiers to access these objects.This allows exploration of all database objects available to the currentuser, including those that can only be accessed by giving the fullnamespace.

Usage

dbListObjects(conn, prefix = NULL, ...)

Arguments

Value

dbListObjects()returns a data framewith columnstable andis_prefix (in that order),optionally with other columns with a dot (.) prefix.Thetable column is of type list.Each object in this list is suitable for use as argument indbQuoteIdentifier().Theis_prefix column is a logical.This data frame contains one row for each object (schema, tableand view)accessible from the prefix (if passed) or from the global namespace(if prefix is omitted).Tables added withdbWriteTable() arepart of the data frame.As soon a table is removed from the database,it is also removed from the data frame of database objects.

The same applies to temporary objects if supported by the database.

The returned names are suitable for quoting withdbQuoteIdentifier().

Failure modes

An error is raised when calling this method for a closedor invalid connection.

Specification

Theprefix column indicates if thetable value refers to a tableor a prefix.For a call with the defaultprefix = NULL, thetablevalues that haveis_prefix == FALSE correspond to the tablesreturned fromdbListTables(),

Thetable object can be quoted withdbQuoteIdentifier().The result of quoting can be passed todbUnquoteIdentifier().(For backends it may be convenient to use theId class, but this isnot required.)

Values intable column that haveis_prefix == TRUE can bepassed as theprefix argument to another call todbListObjects().For the data frame returned from adbListObject() call with theprefix argument set, alltable values whereis_prefix isFALSE can be used in a call todbExistsTable() which returnsTRUE.

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbListObjects(con)dbWriteTable(con, "mtcars", mtcars)dbListObjects(con)dbDisconnect(con)

A list of all pending results

Description

DEPRECATED. DBI currenty supports only one open result set per connection,you need to keep track of the result sets you open if you need thisfunctionality.

Usage

dbListResults(conn, ...)

Arguments

Value

a list. If no results are active, an empty list. If onlya single result is active, a list with one element.

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

List remote tables

Description

Returns the unquoted names of remote tables accessible through thisconnection.This should include views and temporary objects, but not all database backends(in particularRMariaDB andRMySQL) support this.

Usage

dbListTables(conn, ...)

Arguments

Value

dbListTables()returns a character vectorthat enumerates all tablesand viewsin the database.Tables added withdbWriteTable() arepart of the list.As soon a table is removed from the database,it is also removed from the list of database tables.

The same applies to temporary tables if supported by the database.

The returned names are suitable for quoting withdbQuoteIdentifier().

Failure modes

An error is raised when calling this method for a closedor invalid connection.

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbListTables(con)dbWriteTable(con, "mtcars", mtcars)dbListTables(con)dbDisconnect(con)

Quote identifiers

Description

Call this method to generate a string that is suitable foruse in a query as a column or table name, to make sure that yougenerate valid SQL and protect against SQL injection attacks. The inverseoperation isdbUnquoteIdentifier().

Usage

dbQuoteIdentifier(conn, x, ...)

Arguments

Value

dbQuoteIdentifier() returns an object that can be coerced tocharacter,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 todbQuoteIdentifier()asxargument, it is returned unchanged.Passing objects of classSQL should also return them unchanged.(For backends it may be most convenient to returnSQL objectsto achieve this behavior, but this is not required.)

Failure modes

An error is raised if the input containsNA,but not for an empty string.

Specification

CallingdbGetQuery() for a query of the format⁠SELECT 1 AS ...⁠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⁠SELECT 1 AS ...⁠and⁠SELECT * FROM (SELECT 1) ...⁠.The method must use a quoting mechanism that is unambiguously differentfrom the quoting mechanism used for strings, so that a query like⁠SELECT ... FROM (SELECT 1 AS ...)⁠throws an error if the column names do not match.

The method can quote column names thatcontain special characters such as a space,a dot,a comma,or quotes used to mark stringsor identifiers,if the database supports this.In any case, checking the validity of the identifiershould be performed only when executing a query,and not bydbQuoteIdentifier().

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Examples

# Quoting ensures that arbitrary input is safe for use in a queryname <- "Robert'); DROP TABLE Students;--"dbQuoteIdentifier(ANSI(), name)# Use Id() to specify other components such as the schemaid_name <- Id(schema = "schema_name", table = "table_name")id_namedbQuoteIdentifier(ANSI(), id_name)# SQL vectors are always passed through as isvar_name <- SQL("select")var_namedbQuoteIdentifier(ANSI(), var_name)# This mechanism is used to prevent double escapingdbQuoteIdentifier(ANSI(), dbQuoteIdentifier(ANSI(), name))

Quote literal values

Description

Call these methods to generate a string that is suitable foruse in a query as a literal value of the correct type, to make sure that yougenerate valid SQL and protect against SQL injection attacks.

Usage

dbQuoteLiteral(conn, x, ...)

Arguments

Value

dbQuoteLiteral() returns an object that can be coerced tocharacter,of the same length as the input.For an emptyinteger,numeric,character,logical,date,time,or blob vector,this function returns a length-0 object.

When passing the returned object again todbQuoteLiteral()asxargument, it is returned unchanged.Passing objects of classSQL should also return them unchanged.(For backends it may be most convenient to returnSQL objectsto achieve this behavior, but this is not required.)

Failure modes

Passing a listfor thex argument raises an error.

Specification

The returned expression can be used in a⁠SELECT ...⁠ query,and the value ofdbGetQuery(paste0("SELECT ", dbQuoteLiteral(x)))[[1]]must be equal toxfor any scalarinteger,numeric,string,and logical.Ifx isNA, the result must merely satisfyis.na().The literals"NA" or"NULL" are not treated specially.

NA should be translated to an unquoted SQLNULL,so that the query⁠SELECT * FROM (SELECT 1) a WHERE ... IS NULL⁠returns one row.

See Also

Other DBIResult generics:DBIResult-class,dbBind(),dbClearResult(),dbColumnInfo(),dbFetch(),dbGetInfo(),dbGetRowCount(),dbGetRowsAffected(),dbGetStatement(),dbHasCompleted(),dbIsReadOnly(),dbIsValid(),dbQuoteString()

Examples

# Quoting ensures that arbitrary input is safe for use in a queryname <- "Robert'); DROP TABLE Students;--"dbQuoteLiteral(ANSI(), name)# NAs become NULLdbQuoteLiteral(ANSI(), c(1:3, NA))# Logicals become integers by defaultdbQuoteLiteral(ANSI(), c(TRUE, FALSE, NA))# Raw vectors become hex strings by defaultdbQuoteLiteral(ANSI(), list(as.raw(1:3), NULL))# SQL vectors are always passed through as isvar_name <- SQL("select")var_namedbQuoteLiteral(ANSI(), var_name)# This mechanism is used to prevent double escapingdbQuoteLiteral(ANSI(), dbQuoteLiteral(ANSI(), name))

Quote literal strings

Description

Call this method to generate a string that is suitable foruse in a query as a string literal, to make sure that yougenerate valid SQL and protect against SQL injection attacks.

Usage

dbQuoteString(conn, x, ...)

Arguments

Value

dbQuoteString() returns an object that can be coerced tocharacter,of the same length as the input.For an empty character vector this function returns a length-0 object.

When passing the returned object again todbQuoteString()asxargument, it is returned unchanged.Passing objects of classSQL should also return them unchanged.(For backends it may be most convenient to returnSQL objectsto achieve this behavior, but this is not required.)

Failure modes

Passing a numeric,integer,logical,or raw vector,or a listfor thex argument raises an error.

Specification

The returned expression can be used in a⁠SELECT ...⁠ query,and for any scalar characterx the value ofdbGetQuery(paste0("SELECT ", dbQuoteString(x)))[[1]]must be identical tox,even ifx containsspaces,tabs,quotes (singleor double),backticks,or newlines(in any combination)or is itself the result of adbQuoteString() call coerced back tocharacter (even repeatedly).Ifx isNA, the result must merely satisfyis.na().The strings"NA" or"NULL" are not treated specially.

NA should be translated to an unquoted SQLNULL,so that the query⁠SELECT * FROM (SELECT 1) a WHERE ... IS NULL⁠returns one row.

See Also

Other DBIResult generics:DBIResult-class,dbBind(),dbClearResult(),dbColumnInfo(),dbFetch(),dbGetInfo(),dbGetRowCount(),dbGetRowsAffected(),dbGetStatement(),dbHasCompleted(),dbIsReadOnly(),dbIsValid(),dbQuoteLiteral()

Examples

# Quoting ensures that arbitrary input is safe for use in a queryname <- "Robert'); DROP TABLE Students;--"dbQuoteString(ANSI(), name)# NAs become NULLdbQuoteString(ANSI(), c("x", NA))# SQL vectors are always passed through as isvar_name <- SQL("select")var_namedbQuoteString(ANSI(), var_name)# This mechanism is used to prevent double escapingdbQuoteString(ANSI(), dbQuoteString(ANSI(), name))

Read database tables as data frames

Description

Reads a database table to a data frame, optionally convertinga column to row names and converting the column names to validR identifiers.UsedbReadTableArrow() instead to obtain an Arrow object.

Usage

dbReadTable(conn, name, ...)

Arguments

Details

This function returns a data frame.UsedbReadTableArrow() to obtain an Arrow object.

Value

dbReadTable() returns a data frame that contains the complete datafrom the remote table, effectively the result of callingdbGetQuery() with⁠SELECT * FROM <name>⁠.

An empty table is returned as a data frame with zero rows.

The presence ofrownames depends on therow.names argument,seesqlColumnToRownames() for details:

• IfFALSE orNULL, the returned data frame doesn't have row names.

• IfTRUE, a column named "row_names" is converted to row names.

• IfNA, a column named "row_names" is converted to row names if it exists,otherwise no translation occurs.

• If a string, this specifies the name of the column in the remote tablethat contains the row names.

The default isrow.names = FALSE.

If the database supports identifiers with special characters,the columns in the returned data frame are converted to valid Ridentifiersif thecheck.names argument isTRUE,Ifcheck.names = FALSE, the returned table has non-syntactic column names without quotes.

Failure modes

An error is raised if the table does not exist.

An error is raised ifrow.names isTRUE and no "row_names" column exists,

An error is raised ifrow.names is set to a string and no corresponding column exists.

An error is raised when calling this method for a closedor invalid connection.An error is raisedifname cannot be processed withdbQuoteIdentifier()or if this results in a non-scalar.Unsupported values forrow.names andcheck.names(non-scalars,unsupported data types,NA forcheck.names)also raise an error.

Additional arguments

The following arguments are not part of thedbReadTable() generic(to improve compatibility across backends)but are part of the DBI specification:

• row.names (default:FALSE)

• check.names

They must be provided as named arguments.See the "Value" section for details on their usage.

Specification

Thename argument is processed as follows,to support databases that allow non-syntactic names for their objects:

• If an unquoted table name as string:dbReadTable() will do thequoting,perhaps by callingdbQuoteIdentifier(conn, x = name)

• If the result of a call todbQuoteIdentifier(): no more quoting is done

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars[1:10, ])dbReadTable(con, "mtcars")dbDisconnect(con)

Read database tables as Arrow objects

Description

Reads a database table as an Arrow object.UsedbReadTable() instead to obtain a data frame.

Usage

dbReadTableArrow(conn, name, ...)

Arguments

Details

This function returns an Arrow object.Convert it to a data frame withas.data.frame() orusedbReadTable() to obtain a data frame.

Value

dbReadTableArrow() returns an Arrow object that contains the complete datafrom the remote table, effectively the result of callingdbGetQueryArrow() with⁠SELECT * FROM <name>⁠.

An empty table is returned as an Arrow object with zero rows.

Failure modes

An error is raised if the table does not exist.

An error is raised when calling this method for a closedor invalid connection.An error is raisedifname cannot be processed withdbQuoteIdentifier() orif this results in a non-scalar.

Specification

Thename argument is processed as follows,to support databases that allow non-syntactic names for their objects:

• If an unquoted table name as string:dbReadTableArrow() will do thequoting,perhaps by callingdbQuoteIdentifier(conn, x = name)

• If the result of a call todbQuoteIdentifier(): no more quoting is done

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Examples

# Read data as Arrow tablecon <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars[1:10, ])dbReadTableArrow(con, "mtcars")dbDisconnect(con)

Remove a table from the database

Description

Remove a remote table (e.g., created bydbWriteTable())from the database.

Usage

dbRemoveTable(conn, name, ...)

Arguments

Value

dbRemoveTable() returnsTRUE, invisibly.

Failure modes

If the table does not exist, an error is raised.An attempt to remove a view with this function may result in an error.

An error is raised when calling this method for a closedor invalid connection.An error is also raisedifname cannot be processed withdbQuoteIdentifier() orif this results in a non-scalar.

Additional arguments

The following arguments are not part of thedbRemoveTable() generic(to improve compatibility across backends)but are part of the DBI specification:

• temporary (default:FALSE)

• fail_if_missing (default:TRUE)

These arguments must be provided as named arguments.

Iftemporary isTRUE, the call todbRemoveTable()will consider only temporary tables.Not all backends support this argument.In particular, permanent tables of the same name are left untouched.

Iffail_if_missing isFALSE, the call todbRemoveTable()succeeds if the table does not exist.

Specification

A table removed bydbRemoveTable() doesn't appear in the list of tablesreturned bydbListTables(),anddbExistsTable() returnsFALSE.The removal propagates immediately to other connections to the same database.This function can also be used to remove a temporary table.

Thename argument is processed as follows,to support databases that allow non-syntactic names for their objects:

• If an unquoted table name as string:dbRemoveTable() will do thequoting,perhaps by callingdbQuoteIdentifier(conn, x = name)

• If the result of a call todbQuoteIdentifier(): no more quoting is done

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbExistsTable(con, "iris")dbWriteTable(con, "iris", iris)dbExistsTable(con, "iris")dbRemoveTable(con, "iris")dbExistsTable(con, "iris")dbDisconnect(con)

Execute a query on a given database connection

Description

ThedbSendQuery() method only submits and synchronously executes theSQL query to the database engine. It doesnot extract anyrecords — for that you need to use thedbFetch() method, andthen you must calldbClearResult() when you finish fetching therecords you need.For interactive use, you should almost always preferdbGetQuery().UsedbSendQueryArrow() ordbGetQueryArrow() instead to retrieve the resultsas an Arrow object.

Usage

dbSendQuery(conn, statement, ...)

Arguments

Details

This method is forSELECT queries only. Some backends maysupport data manipulation queries through this method for compatibilityreasons. However, callers are strongly encouraged to usedbSendStatement() for data manipulation statements.

The query is submitted to the database server and the DBMS executes it,possibly generating vast amounts of data. Where these data liveis driver-specific: some drivers may choose to leave the output on the serverand transfer them piecemeal to R, others may transfer all the data to theclient – but not necessarily to the memory that R manages. See individualdrivers'dbSendQuery() documentation for details.

Value

dbSendQuery() returnsan S4 object that inherits fromDBIResult.The result set can be used withdbFetch() to extract records.Once you have finished using a result, make sure to clear itwithdbClearResult().

The data retrieval flow

This section gives a complete overview over the flowfor the execution of queries that return tabular data as data frames.

Most of this flow, except repeated calling ofdbBind() ordbBindArrow(),is implemented bydbGetQuery(), which should be sufficientunless you want to access the results in a paged wayor you have a parameterized query that you want to reuse.This flow requires an active connection established bydbConnect().See alsovignette("dbi-advanced") for a walkthrough.

1. UsedbSendQuery() to create a result set object of classDBIResult.

2. Optionally, bind query parameters withdbBind() ordbBindArrow().This is required only if the query contains placeholderssuch as⁠?⁠ or⁠$1⁠, depending on the database backend.

3. Optionally, usedbColumnInfo() to retrieve the structure of the result setwithout retrieving actual data.

4. UsedbFetch() 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 setas a data frame.This step can be called multiple times.Only forward paging is supported, you need to cache previous pagesif you need to navigate backwards.

5. UsedbHasCompleted() to tell when you're done.This method returnsTRUE if no more rows are available for fetching.

6. Repeat the last four steps as necessary.

7. UsedbClearResult() to clean up the result set object.This step is mandatory even if no rows have been fetchedor if an error has occurred during the processing.It is good practice to useon.exit() orwithr::defer()to ensure that this step is always executed.

Failure modes

An error is raised when issuing a query over a closedor invalid connection,or if the query is not a non-NA string.An error is also raised if the syntax of the query is invalidand all query parameters are given (by passing theparams argument)or theimmediate argument is set toTRUE.

Additional arguments

The following arguments are not part of thedbSendQuery() generic(to improve compatibility across backends)but are part of the DBI specification:

• params (default:NULL)

• immediate (default:NULL)

They must be provided as named arguments.See the "Specification" sections for details on their usage.

Specification

No warnings occur under normal conditions.When done, the DBIResult object must be cleared with a call todbClearResult().Failure to clear the result set leads to a warningwhen the connection is closed.

If the backend supports only one open result set per connection,issuing a second query invalidates an already open result setand raises a warning.The newly opened result set is validand must be cleared withdbClearResult().

Theparam argument allows passing query parameters, seedbBind() for details.

Specification for theimmediate argument

Theimmediate argument supports distinguishing between "direct"and "prepared" APIs offered by many database drivers.Passingimmediate = TRUE leads to immediate execution of thequery or statement, via the "direct" API (if supported by the driver).The defaultNULL means that the backend should choose whatever APImakes the most sense for the database, and (if relevant) tries theother API if the first attempt fails. A successful second attemptshould result in a message that suggests passing the correctimmediate argument.Examples for possible behaviors:

1. DBI backend defaults toimmediate = TRUE internally

   1. A query without parameters is passed: query is executed

   2. A query with parameters is passed:

      1. params not given: rejected immediately by the databasebecause of a syntax error in the query, the backend triesimmediate = FALSE (and gives a message)

      2. params given: query is executed usingimmediate = FALSE

2. DBI backend defaults toimmediate = FALSE internally

   1. A query without parameters is passed:

      1. simple query: query is executed

      2. "special" query (such as setting a config options): fails,the backend triesimmediate = TRUE (and gives a message)

   2. A query with parameters is passed:

      1. params not given: waiting for parameters viadbBind()

      2. params given: query is executed

See Also

For updates:dbSendStatement() anddbExecute().

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Other data retrieval generics:dbBind(),dbClearResult(),dbFetch(),dbFetchArrow(),dbFetchArrowChunk(),dbGetQuery(),dbGetQueryArrow(),dbHasCompleted(),dbSendQueryArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars)rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")dbFetch(rs)dbClearResult(rs)# Pass one set of values with the param argument:rs <- dbSendQuery(  con,  "SELECT * FROM mtcars WHERE cyl = ?",  params = list(4L))dbFetch(rs)dbClearResult(rs)# Pass multiple sets of values with dbBind():rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = ?")dbBind(rs, list(6L))dbFetch(rs)dbBind(rs, list(8L))dbFetch(rs)dbClearResult(rs)dbDisconnect(con)

Execute a query on a given database connection for retrieval via Arrow

Description

ThedbSendQueryArrow() method only submits and synchronously executes theSQL query to the database engine.It doesnot extract anyrecords — for that you need to use thedbFetchArrow() method, andthen you must calldbClearResult() when you finish fetching therecords you need.For interactive use, you should almost always preferdbGetQueryArrow().UsedbSendQuery() ordbGetQuery() instead to retrieve the resultsas a data frame.

Usage

dbSendQueryArrow(conn, statement, ...)

Arguments

Details

This method is forSELECT queries only. Some backends maysupport data manipulation queries through this method for compatibilityreasons. However, callers are strongly encouraged to usedbSendStatement() for data manipulation statements.

Value

dbSendQueryArrow() returnsan S4 object that inherits fromDBIResultArrow.The result set can be used withdbFetchArrow() to extract records.Once you have finished using a result, make sure to clear itwithdbClearResult().

The data retrieval flow for Arrow streams

This section gives a complete overview over the flowfor the execution of queries that return tabular data as an Arrow stream.

Most of this flow, except repeated calling ofdbBindArrow() ordbBind(),is implemented bydbGetQueryArrow(),which should be sufficientunless you have a parameterized query that you want to reuse.This flow requires an active connection established bydbConnect().See alsovignette("dbi-advanced") for a walkthrough.

1. UsedbSendQueryArrow() to create a result set object of classDBIResultArrow.

2. Optionally, bind query parameters withdbBindArrow() ordbBind().This is required only if the query contains placeholderssuch as⁠?⁠ or⁠$1⁠, depending on the database backend.

3. UsedbFetchArrow() to get a data stream.

4. Repeat the last two steps as necessary.

5. UsedbClearResult() to clean up the result set object.This step is mandatory even if no rows have been fetchedor if an error has occurred during the processing.It is good practice to useon.exit() orwithr::defer()to ensure that this step is always executed.

Failure modes

An error is raised when issuing a query over a closedor invalid connection,or if the query is not a non-NA string.An error is also raised if the syntax of the query is invalidand all query parameters are given (by passing theparams argument)or theimmediate argument is set toTRUE.

Additional arguments

The following arguments are not part of thedbSendQueryArrow() generic(to improve compatibility across backends)but are part of the DBI specification:

• params (default:NULL)

• immediate (default:NULL)

They must be provided as named arguments.See the "Specification" sections for details on their usage.

Specification

No warnings occur under normal conditions.When done, the DBIResult object must be cleared with a call todbClearResult().Failure to clear the result set leads to a warningwhen the connection is closed.

If the backend supports only one open result set per connection,issuing a second query invalidates an already open result setand raises a warning.The newly opened result set is validand must be cleared withdbClearResult().

Theparam argument allows passing query parameters, seedbBind() for details.

Specification for theimmediate argument

Theimmediate argument supports distinguishing between "direct"and "prepared" APIs offered by many database drivers.Passingimmediate = TRUE leads to immediate execution of thequery or statement, via the "direct" API (if supported by the driver).The defaultNULL means that the backend should choose whatever APImakes the most sense for the database, and (if relevant) tries theother API if the first attempt fails. A successful second attemptshould result in a message that suggests passing the correctimmediate argument.Examples for possible behaviors:

1. DBI backend defaults toimmediate = TRUE internally

   1. A query without parameters is passed: query is executed

   2. A query with parameters is passed:

      1. params not given: rejected immediately by the databasebecause of a syntax error in the query, the backend triesimmediate = FALSE (and gives a message)

      2. params given: query is executed usingimmediate = FALSE

2. DBI backend defaults toimmediate = FALSE internally

   1. A query without parameters is passed:

      1. simple query: query is executed

      2. "special" query (such as setting a config options): fails,the backend triesimmediate = TRUE (and gives a message)

   2. A query with parameters is passed:

      1. params not given: waiting for parameters viadbBind()

      2. params given: query is executed

See Also

For updates:dbSendStatement() anddbExecute().

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Other data retrieval generics:dbBind(),dbClearResult(),dbFetch(),dbFetchArrow(),dbFetchArrowChunk(),dbGetQuery(),dbGetQueryArrow(),dbHasCompleted(),dbSendQuery()

Examples

# Retrieve data as arrow tablecon <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars)rs <- dbSendQueryArrow(con, "SELECT * FROM mtcars WHERE cyl = 4")dbFetchArrow(rs)dbClearResult(rs)dbDisconnect(con)

Execute a data manipulation statement on a given database connection

Description

ThedbSendStatement() method only submits and synchronously executes theSQL data manipulation statement (e.g.,UPDATE,DELETE,⁠INSERT INTO⁠,⁠DROP TABLE⁠, ...) to the database engine. To querythe number of affected rows, calldbGetRowsAffected() on thereturned result object. You must also calldbClearResult() afterthat. For interactive use, you should almost always preferdbExecute().

Usage

dbSendStatement(conn, statement, ...)

Arguments

Details

dbSendStatement() comes with a default implementation that simplyforwards todbSendQuery(), to support backends that onlyimplement the latter.

Value

dbSendStatement() returnsan S4 object that inherits fromDBIResult.The result set can be used withdbGetRowsAffected() todetermine the number of rows affected by the query.Once you have finished using a result, make sure to clear itwithdbClearResult().

The command execution flow

This section gives a complete overview over the flowfor the execution of SQL statements that have side effectssuch as stored procedures, inserting or deleting data,or setting database or connection options.Most of this flow, except repeated calling ofdbBindArrow(),is implemented bydbExecute(), which should be sufficientfor non-parameterized queries.This flow requires an active connection established bydbConnect().See alsovignette("dbi-advanced") for a walkthrough.

1. UsedbSendStatement() to create a result set object of classDBIResult.For some queries you need to passimmediate = TRUE.

2. Optionally, bind query parameters withdbBind() ordbBindArrow().This is required only if the query contains placeholderssuch as⁠?⁠ or⁠$1⁠, depending on the database backend.

3. Optionally, usedbGetRowsAffected() to retrieve the numberof rows affected by the query.

4. Repeat the last two steps as necessary.

5. UsedbClearResult() to clean up the result set object.This step is mandatory even if no rows have been fetchedor if an error has occurred during the processing.It is good practice to useon.exit() orwithr::defer()to ensure that this step is always executed.

Failure modes

An error is raised when issuing a statement over a closedor invalid connection,or if the statement is not a non-NA string.An error is also raised if the syntax of the query is invalidand all query parameters are given (by passing theparams argument)or theimmediate argument is set toTRUE.

Additional arguments

The following arguments are not part of thedbSendStatement() generic(to improve compatibility across backends)but are part of the DBI specification:

• params (default:NULL)

• immediate (default:NULL)

They must be provided as named arguments.See the "Specification" sections for details on their usage.

Specification

No warnings occur under normal conditions.When done, the DBIResult object must be cleared with a call todbClearResult().Failure to clear the result set leads to a warningwhen the connection is closed.If the backend supports only one open result set per connection,issuing a second query invalidates an already open result setand raises a warning.The newly opened result set is validand must be cleared withdbClearResult().

Theparam argument allows passing query parameters, seedbBind() for details.

Specification for theimmediate argument

Theimmediate argument supports distinguishing between "direct"and "prepared" APIs offered by many database drivers.Passingimmediate = TRUE leads to immediate execution of thequery or statement, via the "direct" API (if supported by the driver).The defaultNULL means that the backend should choose whatever APImakes the most sense for the database, and (if relevant) tries theother API if the first attempt fails. A successful second attemptshould result in a message that suggests passing the correctimmediate argument.Examples for possible behaviors:

1. DBI backend defaults toimmediate = TRUE internally

   1. A query without parameters is passed: query is executed

   2. A query with parameters is passed:

      1. params not given: rejected immediately by the databasebecause of a syntax error in the query, the backend triesimmediate = FALSE (and gives a message)

      2. params given: query is executed usingimmediate = FALSE

2. DBI backend defaults toimmediate = FALSE internally

   1. A query without parameters is passed:

      1. simple query: query is executed

      2. "special" query (such as setting a config options): fails,the backend triesimmediate = TRUE (and gives a message)

   2. A query with parameters is passed:

      1. params not given: waiting for parameters viadbBind()

      2. params given: query is executed

See Also

For queries:dbSendQuery() anddbGetQuery().

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbUnquoteIdentifier(),dbWriteTable(),dbWriteTableArrow()

Other command execution generics:dbBind(),dbClearResult(),dbExecute(),dbGetRowsAffected()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "cars", head(cars, 3))rs <- dbSendStatement(  con,  "INSERT INTO cars (speed, dist) VALUES (1, 1), (2, 2), (3, 3)")dbHasCompleted(rs)dbGetRowsAffected(rs)dbClearResult(rs)dbReadTable(con, "cars")   # there are now 6 rows# Pass one set of values directly using the param argument:rs <- dbSendStatement(  con,  "INSERT INTO cars (speed, dist) VALUES (?, ?)",  params = list(4L, 5L))dbClearResult(rs)# Pass multiple sets of values using dbBind():rs <- dbSendStatement(  con,  "INSERT INTO cars (speed, dist) VALUES (?, ?)")dbBind(rs, list(5:6, 6:7))dbBind(rs, list(7L, 8L))dbClearResult(rs)dbReadTable(con, "cars")   # there are now 10 rowsdbDisconnect(con)

Set data mappings between an DBMS and R.

Description

This generic is deprecated since no working implementation was ever produced.

Usage

dbSetDataMappings(res, flds, ...)

Arguments

Details

Sets one or more conversion functions to handle the translation of DBMS datatypes to R objects. This is only needed for non-primitive data, since allDBI drivers handle the common base types (integers, numeric, strings, etc.)

The details on conversion functions (e.g., arguments, whether they can invokeinitializers and/or destructors) have not been specified.

Unquote identifiers

Description

Call this method to convert aSQL object created bydbQuoteIdentifier()back to a list ofId objects.

Usage

dbUnquoteIdentifier(conn, x, ...)

Arguments

Value

dbUnquoteIdentifier() returns a list of objectsof the same length as the input.For an empty vector, this function returns a length-0 object.The names of the input argument are preserved in the output.Ifx is a value returned bydbUnquoteIdentifier(),callingdbUnquoteIdentifier(..., dbQuoteIdentifier(..., x))returnslist(x).Ifx is an object of classId,callingdbUnquoteIdentifier(..., x) returnslist(x).(For backends it may be most convenient to returnId objectsto achieve this behavior, but this is not required.)

Plain character vectors can also be passed todbUnquoteIdentifier().

Failure modes

An error is raised if a character vectors with a missing value is passedas thex argument.

Specification

For any character vector of length one, quoting (withdbQuoteIdentifier())then unquoting then quoting the first element is identical to just quoting.This is also true for strings thatcontain special characters such as a space,a dot,a comma,or quotes used to mark stringsor identifiers,if the database supports this.

Unquoting simple strings (consisting of only letters) wrapped withSQL() andthen quoting viadbQuoteIdentifier() gives the same result as justquoting the string.Similarly, unquoting expressions of the formSQL("schema.table")and then quoting gives the same result as quoting the identifierconstructed byId("schema", "table").

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbWriteTable(),dbWriteTableArrow()

Examples

# Unquoting allows to understand the structure of a# possibly complex quoted identifierdbUnquoteIdentifier(  ANSI(),  SQL(c('"Catalog"."Schema"."Table"', '"Schema"."Table"', '"UnqualifiedTable"')))# The returned object is always a list,# also for Id objectsdbUnquoteIdentifier(ANSI(), Id("Catalog", "Schema", "Table"))# Quoting and unquoting are inversesdbQuoteIdentifier(  ANSI(),  dbUnquoteIdentifier(ANSI(), SQL("UnqualifiedTable"))[[1]])dbQuoteIdentifier(  ANSI(),  dbUnquoteIdentifier(ANSI(), Id("Schema", "Table"))[[1]])

Self-contained SQL transactions

Description

Given thattransactions are implemented, this functionallows you to pass in code that is run in a transaction.The default method ofdbWithTransaction() callsdbBegin()before executing the code,anddbCommit() after successful completion,ordbRollback() in case of an error.The advantage isthat you don't have to remember to dodbBegin() anddbCommit() ordbRollback() – that is all taken care of.The special functiondbBreak() allows an early exit with rollback,it can be called only insidedbWithTransaction().

Usage

dbWithTransaction(conn, code, ...)dbBreak()

Arguments

Details

DBI implementsdbWithTransaction(), backends should need to override thisgeneric only if they implement specialized handling.

Value

dbWithTransaction() returns the value of the executed code.

Failure modes

Failure to initiate the transaction(e.g., if the connection is closedor invalidof ifdbBegin() has been called already)gives an error.

Specification

dbWithTransaction() initiates a transaction withdbBegin(), executesthe code given in thecode argument, and commits the transaction withdbCommit().If the code raises an error, the transaction is instead aborted withdbRollback(), and the error is propagated.If the code callsdbBreak(), execution of the code stops and thetransaction is silently aborted.All side effects caused by the code(such as the creation of new variables)propagate to the calling environment.

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "cash", data.frame(amount = 100))dbWriteTable(con, "account", data.frame(amount = 2000))# All operations are carried out as logical unit:dbWithTransaction(  con,  {    withdrawal <- 300    dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))    dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))  })# The code is executed as if in the current environment:withdrawal# The changes are committed to the database after successful execution:dbReadTable(con, "cash")dbReadTable(con, "account")# Rolling back with dbBreak():dbWithTransaction(  con,  {    withdrawal <- 5000    dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))    dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))    if (dbReadTable(con, "account")$amount < 0) {      dbBreak()    }  })# These changes were not committed to the database:dbReadTable(con, "cash")dbReadTable(con, "account")dbDisconnect(con)

Copy data frames to database tables

Description

Writes, overwrites or appends a data frame to a database table, optionallyconverting row names to a column and specifying SQL data types for fields.

Usage

dbWriteTable(conn, name, value, ...)

Arguments

Details

This function expects a data frame.UsedbWriteTableArrow() to write an Arrow object.

This function is useful if you want to create and load a table at the same time.UsedbAppendTable() ordbAppendTableArrow() for appending data to an existingtable,dbCreateTable() ordbCreateTableArrow() for creating a table,anddbExistsTable() anddbRemoveTable() for overwriting tables.

DBI only standardizes writing data frames withdbWriteTable().Some backends might implement methods that can consume CSV filesor other data formats.For details, see the documentation for the individual methods.

Value

dbWriteTable() returnsTRUE, invisibly.

Failure modes

If the table exists, and bothappend andoverwrite arguments are unset,orappend = TRUE and the data frame with the new data has differentcolumn names,an error is raised; the remote table remains unchanged.

An error is raised when calling this method for a closedor invalid connection.An error is also raisedifname cannot be processed withdbQuoteIdentifier() orif this results in a non-scalar.Invalid values for the additional argumentsrow.names,overwrite,append,field.types, andtemporary(non-scalars,unsupported data types,NA,incompatible values,duplicateor missing names,incompatible columns)also raise an error.

Additional arguments

The following arguments are not part of thedbWriteTable() generic(to improve compatibility across backends)but are part of the DBI specification:

• row.names (default:FALSE)

• overwrite (default:FALSE)

• append (default:FALSE)

• field.types (default:NULL)

• temporary (default:FALSE)

They must be provided as named arguments.See the "Specification" and "Value" sections for details on their usage.

Specification

Thename argument is processed as follows,to support databases that allow non-syntactic names for their objects:

• If an unquoted table name as string:dbWriteTable() will do the quoting,perhaps by callingdbQuoteIdentifier(conn, x = name)

• If the result of a call todbQuoteIdentifier(): no more quoting is done

Thevalue argument must be a data framewith a subset of the columns of the existing table ifappend = TRUE.The order of the columns does not matter withappend = TRUE.

If theoverwrite argument isTRUE, an existing table of the same namewill be overwritten.This argument doesn't change behavior if the table does not exist yet.

If theappend argument isTRUE, the rows in an existing table arepreserved, and the new data are appended.If the table doesn't exist yet, it is created.

If thetemporary argument isTRUE, the table is not available in asecond 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.

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 namesand column names.

The following data types must be supported at least,and be read identically withdbReadTable():

• integer

• numeric(the behavior forInf andNaN is not specified)

• logical

• NA as NULL

• 64-bit values (using"bigint" as field type); the result can be

  • converted to a numeric, which may lose precision,

  • converted a character vector, which gives the full decimalrepresentation

  • written to another table and read again unchanged

• character (in both UTF-8and native encodings),supporting empty stringsbefore and after a non-empty string

• factor (returned as character)

• list of raw(if supported by the database)

• objects of typeblob::blob(if supported by the database)

• date(if supported by the database;returned asDate),also for dates prior to 1970 or 1900 or after 2038

• time(if supported by the database;returned as objects that inherit fromdifftime)

• timestamp(if supported by the database;returned asPOSIXctrespecting the time zone but not necessarily preserving theinput time zone),also for timestamps prior to 1970 or 1900 or after 2038respecting the time zone but not necessarily preserving theinput time zone)

Mixing column types in the same table is supported.

Thefield.types argument must be a named character vector with at mostone entry for each column.It indicates the SQL data type to be used for a new column.If a column is missed fromfield.types, the type is inferredfrom the input data withdbDataType().

The interpretation ofrownames depends on therow.names argument,seesqlRownamesToColumn() for details:

• IfFALSE orNULL, row names are ignored.

• IfTRUE, row names are converted to a column named "row_names",even if the input data frame only has natural row names from 1 tonrow(...).

• IfNA, 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.

• If a string, this specifies the name of the column in the remote tablethat contains the row names,even if the input data frame only has natural row names.

The default isrow.names = FALSE.

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTableArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTable(con, "mtcars", mtcars[1:5, ])dbReadTable(con, "mtcars")dbWriteTable(con, "mtcars", mtcars[6:10, ], append = TRUE)dbReadTable(con, "mtcars")dbWriteTable(con, "mtcars", mtcars[1:10, ], overwrite = TRUE)dbReadTable(con, "mtcars")# No row namesdbWriteTable(con, "mtcars", mtcars[1:10, ], overwrite = TRUE, row.names = FALSE)dbReadTable(con, "mtcars")

Copy Arrow objects to database tables

Description

Writes, overwrites or appends an Arrow object to a database table.

Usage

dbWriteTableArrow(conn, name, value, ...)

Arguments

Details

This function expects an Arrow object.Convert a data frame to an Arrow object withnanoarrow::as_nanoarrow_array_stream() orusedbWriteTable() to write a data frame.

This function is useful if you want to create and load a table at the same time.UsedbAppendTableArrow() for appending data to an existingtable,dbCreateTableArrow() for creating a table and specifying field types,anddbRemoveTable() for overwriting tables.

Value

dbWriteTableArrow() returnsTRUE, invisibly.

Failure modes

If the table exists, and bothappend andoverwrite arguments are unset,orappend = TRUE and the data frame with the new data has differentcolumn names,an error is raised; the remote table remains unchanged.

An error is raised when calling this method for a closedor invalid connection.An error is also raisedifname cannot be processed withdbQuoteIdentifier() orif this results in a non-scalar.Invalid values for the additional argumentsoverwrite,append, andtemporary(non-scalars,unsupported data types,NA,incompatible values,incompatible columns)also raise an error.

Additional arguments

The following arguments are not part of thedbWriteTableArrow() generic(to improve compatibility across backends)but are part of the DBI specification:

• overwrite (default:FALSE)

• append (default:FALSE)

• temporary (default:FALSE)

They must be provided as named arguments.See the "Specification" and "Value" sections for details on their usage.

Specification

Thename argument is processed as follows,to support databases that allow non-syntactic names for their objects:

• If an unquoted table name as string:dbWriteTableArrow() will do the quoting,perhaps by callingdbQuoteIdentifier(conn, x = name)

• If the result of a call todbQuoteIdentifier(): no more quoting is done

Thevalue argument must be a data framewith a subset of the columns of the existing table ifappend = TRUE.The order of the columns does not matter withappend = TRUE.

If theoverwrite argument isTRUE, an existing table of the same namewill be overwritten.This argument doesn't change behavior if the table does not exist yet.

If theappend argument isTRUE, the rows in an existing table arepreserved, and the new data are appended.If the table doesn't exist yet, it is created.

If thetemporary argument isTRUE, the table is not available in asecond 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.

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 namesand column names.

The following data types must be supported at least,and be read identically withdbReadTable():

• integer

• numeric(the behavior forInf andNaN is not specified)

• logical

• NA as NULL

• 64-bit values (using"bigint" as field type); the result can be

  • converted to a numeric, which may lose precision,

  • converted a character vector, which gives the full decimalrepresentation

  • written to another table and read again unchanged

• character (in both UTF-8and native encodings),supporting empty stringsbefore and after a non-empty string

• factor (possibly returned as character)

• objects of typeblob::blob(if supported by the database)

• date(if supported by the database;returned asDate),also for dates prior to 1970 or 1900 or after 2038

• time(if supported by the database;returned as objects that inherit fromdifftime)

• timestamp(if supported by the database;returned asPOSIXctrespecting the time zone but not necessarily preserving theinput time zone),also for timestamps prior to 1970 or 1900 or after 2038respecting the time zone but not necessarily preserving theinput time zone)

Mixing column types in the same table is supported.

See Also

Other DBIConnection generics:DBIConnection-class,dbAppendTable(),dbAppendTableArrow(),dbCreateTable(),dbCreateTableArrow(),dbDataType(),dbDisconnect(),dbExecute(),dbExistsTable(),dbGetException(),dbGetInfo(),dbGetQuery(),dbGetQueryArrow(),dbIsReadOnly(),dbIsValid(),dbListFields(),dbListObjects(),dbListResults(),dbListTables(),dbQuoteIdentifier(),dbReadTable(),dbReadTableArrow(),dbRemoveTable(),dbSendQuery(),dbSendQueryArrow(),dbSendStatement(),dbUnquoteIdentifier(),dbWriteTable()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")dbWriteTableArrow(con, "mtcars", nanoarrow::as_nanoarrow_array_stream(mtcars[1:5, ]))dbReadTable(con, "mtcars")dbDisconnect(con)

Internal page for hidden aliases

Description

For S4 methods that require a documentation entry but only clutter the index.

Usage

## S4 method for signature 'DBIObject'SQLKeywords(dbObj, ...)## S4 method for signature 'missing'SQLKeywords(dbObj, ...)## S4 method for signature 'DBIConnection'dbAppendTableArrow(conn, name, value, ...)## S4 method for signature 'DBIConnection'dbAppendTable(conn, name, value, ..., row.names = NULL)## S4 method for signature 'DBIResult'dbBindArrow(res, params, ...)## S4 method for signature 'DBIResultArrowDefault'dbBindArrow(res, params, ...)## S4 method for signature 'DBIResultArrow'dbBind(res, params, ...)## S4 method for signature 'DBIDriver'dbCanConnect(drv, ...)## S4 method for signature 'DBIResultArrow'dbClearResult(res, ...)## S4 method for signature 'DBIConnector'dbConnect(drv, ...)## S4 method for signature 'DBIConnection'dbCreateTableArrow(conn, name, value, ..., temporary = FALSE)## S4 method for signature 'DBIConnection'dbCreateTable(conn, name, fields, ..., row.names = NULL, temporary = FALSE)## S4 method for signature 'DBIConnector'dbDataType(dbObj, obj, ...)## S4 method for signature 'DBIObject'dbDataType(dbObj, obj, ...)## S4 method for signature 'character'dbDriver(drvName, ...)## S4 method for signature 'DBIConnection,character'dbExecute(conn, statement, ...)## S4 method for signature 'DBIConnection,Id'dbExistsTable(conn, name, ...)## S4 method for signature 'DBIResultArrow'dbFetchArrowChunk(res, ...)## S4 method for signature 'DBIResultArrow'dbFetchArrow(res, ...)## S4 method for signature 'DBIResult'dbFetch(res, n = -1, ...)## S4 method for signature 'DBIResultArrow'dbFetch(res, n = -1, ...)## S4 method for signature 'DBIConnector'dbGetConnectArgs(drv, eval = TRUE, ...)## S4 method for signature 'DBIResult'dbGetInfo(dbObj, ...)## S4 method for signature 'DBIResultArrow'dbGetInfo(dbObj, ...)## S4 method for signature 'DBIConnection'dbGetQueryArrow(conn, statement, ...)## S4 method for signature 'DBIConnection,character'dbGetQuery(conn, statement, ..., n = -1L)## S4 method for signature 'DBIResultArrow'dbGetRowCount(res, ...)## S4 method for signature 'DBIResultArrow'dbGetRowsAffected(res, ...)## S4 method for signature 'DBIResultArrow'dbGetStatement(res, ...)## S4 method for signature 'DBIResultArrow'dbHasCompleted(res, ...)## S4 method for signature 'DBIConnector'dbIsReadOnly(dbObj, ...)## S4 method for signature 'DBIObject'dbIsReadOnly(dbObj, ...)## S4 method for signature 'DBIResultArrowDefault'dbIsValid(dbObj, ...)## S4 method for signature 'DBIConnection,Id'dbListFields(conn, name, ...)## S4 method for signature 'DBIConnection,character'dbListFields(conn, name, ...)## S4 method for signature 'DBIConnection'dbListObjects(conn, prefix = NULL, ...)## S4 method for signature 'DBIConnection,ANY'dbQuoteIdentifier(conn, x, ...)## S4 method for signature 'DBIConnection,character'dbQuoteIdentifier(conn, x, ...)## S4 method for signature 'DBIConnection,SQL'dbQuoteIdentifier(conn, x, ...)## S4 method for signature 'DBIConnection,Id'dbQuoteIdentifier(conn, x, ...)## S4 method for signature 'DBIConnection'dbQuoteLiteral(conn, x, ...)## S4 method for signature 'DBIConnection,ANY'dbQuoteString(conn, x, ...)## S4 method for signature 'DBIConnection,character'dbQuoteString(conn, x, ...)## S4 method for signature 'DBIConnection,SQL'dbQuoteString(conn, x, ...)## S4 method for signature 'DBIConnection'dbReadTableArrow(conn, name, ...)## S4 method for signature 'DBIConnection,Id'dbReadTable(conn, name, ...)## S4 method for signature 'DBIConnection,character'dbReadTable(conn, name, ..., row.names = FALSE, check.names = TRUE)## S4 method for signature 'DBIConnection,Id'dbRemoveTable(conn, name, ...)## S4 method for signature 'DBIConnection'dbSendQueryArrow(conn, statement, params = NULL, ...)## S4 method for signature 'DBIConnection,character'dbSendStatement(conn, statement, ...)## S4 method for signature 'DBIConnection'dbUnquoteIdentifier(conn, x, ...)## S4 method for signature 'DBIConnection'dbWithTransaction(conn, code)## S4 method for signature 'DBIConnection'dbWriteTableArrow(  conn,  name,  value,  append = FALSE,  overwrite = FALSE,  ...,  temporary = FALSE)## S4 method for signature 'DBIConnection,Id'dbWriteTable(conn, name, value, ...)## S4 method for signature 'DBIObject,character'isSQLKeyword(  dbObj,  name,  keywords = .SQL92Keywords,  case = c("lower", "upper", "any")[3],  ...)## S4 method for signature 'DBIObject,character'make.db.names(  dbObj,  snames,  keywords = .SQL92Keywords,  unique = TRUE,  allow.keywords = TRUE,  ...)## S4 method for signature 'AnsiConnection'show(object)## S4 method for signature 'DBIConnection'show(object)## S4 method for signature 'DBIConnector'show(object)## S4 method for signature 'DBIDriver'show(object)## S4 method for signature 'DBIResult'show(object)## S4 method for signature 'Id'show(object)## S4 method for signature 'SQL'show(object)## S4 method for signature 'DBIConnection'sqlAppendTable(con, table, values, row.names = NA, ...)## S4 method for signature 'DBIConnection'sqlCreateTable(con, table, fields, row.names = NA, temporary = FALSE, ...)## S4 method for signature 'DBIConnection'sqlData(con, value, row.names = NA, ...)## S4 method for signature 'DBIConnection'sqlInterpolate(conn, sql, ..., .dots = list())## S4 method for signature 'DBIConnection'sqlParseVariables(conn, sql, ...)

Arguments

Make R identifiers into legal SQL identifiers

Description

These methods are DEPRECATED. Please usedbQuoteIdentifier()(or possiblydbQuoteString()) instead.

Usage

make.db.names.default(  snames,  keywords = .SQL92Keywords,  unique = TRUE,  allow.keywords = TRUE)isSQLKeyword.default(  name,  keywords = .SQL92Keywords,  case = c("lower", "upper", "any")[3])isSQLKeyword(  dbObj,  name,  keywords = .SQL92Keywords,  case = c("lower", "upper", "any")[3],  ...)make.db.names(  dbObj,  snames,  keywords = .SQL92Keywords,  unique = TRUE,  allow.keywords = TRUE,  ...)

Arguments

Details

The algorithm inmake.db.names first invokesmake.names andthen replaces each occurrence of a dot. by an underscore⁠_⁠. Ifallow.keywords isFALSE and identifiers collide with SQLkeywords, a small integer is appended to the identifier in the form of"_n".

The set of SQL keywords is stored in the character vector.SQL92Keywords and reflects the SQL ANSI/ISO standard as documentedin "X/Open SQL and RDA", 1994, ISBN 1-872630-68-8. Users can easilyoverride or update this vector.

Value

make.db.names returns a character vector of legal SQLidentifiers corresponding to itssnames argument.

SQLKeywords returns a character vector of all known keywords for thedatabase-engine associated withdbObj.

isSQLKeyword returns a logical vector parallel toname.