pool (version 1.0.4)

DBI-wrap: DBI methods (simple wrappers)

Description

These pool method for DBI generics methods check out a connection (with poolCheckout()), re-call the generic, then return the connection to the pool (with poolReturn()). See DBI-custom for DBI methods that do not work with pool objects.

Usage

# S4 method for Pool
dbDataType(dbObj, obj, ...)
# S4 method for Pool,ANY
dbGetQuery(conn, statement, ...)
# S4 method for Pool,ANY
dbExecute(conn, statement, ...)
# S4 method for Pool,ANY
dbListFields(conn, name, ...)
# S4 method for Pool
dbListTables(conn, ...)
# S4 method for Pool
dbListObjects(conn, prefix = NULL, ...)
# S4 method for Pool,ANY
dbReadTable(conn, name, ...)
# S4 method for Pool,ANY
dbWriteTable(conn, name, value, ...)
# S4 method for Pool
dbCreateTable(conn, name, fields, ..., row.names = NULL, temporary = FALSE)
# S4 method for Pool
dbAppendTable(conn, name, value, ..., row.names = NULL)
# S4 method for Pool,ANY
dbExistsTable(conn, name, ...)
# S4 method for Pool,ANY
dbRemoveTable(conn, name, ...)
# S4 method for Pool
dbIsReadOnly(dbObj, ...)
# S4 method for Pool
sqlData(con, value, row.names = NA, ...)
# S4 method for Pool
sqlCreateTable(con, table, fields, row.names = NA, temporary = FALSE, ...)
# S4 method for Pool
sqlAppendTable(con, table, values, row.names = NA, ...)
# S4 method for Pool
sqlInterpolate(conn, sql, ..., .dots = list())
# S4 method for Pool
sqlParseVariables(conn, sql, ...)
# S4 method for Pool,ANY
dbQuoteIdentifier(conn, x, ...)
# S4 method for Pool
dbUnquoteIdentifier(conn, x, ...)
# S4 method for Pool
dbQuoteLiteral(conn, x, ...)
# S4 method for Pool,ANY
dbQuoteString(conn, x, ...)
# S4 method for Pool
dbAppendTableArrow(conn, name, value, ...)
# S4 method for Pool
dbCreateTableArrow(conn, name, value, ..., temporary = FALSE)
# S4 method for Pool
dbGetQueryArrow(conn, statement, ...)
# S4 method for Pool
dbReadTableArrow(conn, name, ...)
# S4 method for Pool
dbSendQueryArrow(conn, statement, ...)
# S4 method for Pool
dbWriteTableArrow(conn, name, value, ...)

Arguments

dbObj

A DBI Driver][DBI::DBIDriver-class] or DBI Connection.

obj

An R object whose SQL type we want to determine.

...

Other arguments passed on to methods.

conn

A DBI Connection.

statement

a character string containing SQL.

name

The table name, passed on to dbQuoteIdentifier(). Options are:

• a character string with the unquoted DBMS table name, e.g. "table_name",

• a call to Id() with components to the fully qualified table name, e.g. Id(schema = "my_schema", table = "table_name")

• a call to SQL() with the quoted and fully qualified table name given verbatim, e.g. SQL('"my_schema"."table_name"')

prefix

A fully qualified path in the database's namespace, or NULL. This argument will be processed with dbUnquoteIdentifier(). If given the method will return all objects accessible through this prefix.

value

A data.frame (or coercible to data.frame).

fields

Either a character vector or a data frame.

A named character vector: Names are column names, values are types. Names are escaped with dbQuoteIdentifier(). Field types are unescaped.

A data frame: field types are generated using dbDataType().

row.names

Must be NULL.

temporary

If TRUE, will generate a temporary table.

con

A database connection.

table

The table name, passed on to dbQuoteIdentifier(). Options are:

• a character string with the unquoted DBMS table name, e.g. "table_name",

• a call to Id() with components to the fully qualified table name, e.g. Id(schema = "my_schema", table = "table_name")

• a call to SQL() with the quoted and fully qualified table name given verbatim, e.g. SQL('"my_schema"."table_name"')

values

A data frame. Factors will be converted to character vectors. Character vectors will be escaped with dbQuoteString().

sql

A SQL string containing variables to interpolate. Variables must start with a question mark and can be any valid R identifier, i.e. it must start with a letter or ., and be followed by a letter, digit, . or _.

.dots

A list of named arguments to interpolate.

x

A character vector, SQL or Id object to quote as identifier.

Examples

mtcars1 <- mtcars[ c(1:16), ] # first half of the mtcars dataset
mtcars2 <- mtcars[-c(1:16), ] # second half of the mtcars dataset

pool <- dbPool(RSQLite::SQLite())

# write the mtcars1 table into the database
dbWriteTable(pool, "mtcars", mtcars1, row.names = TRUE)

# list the current tables in the database
dbListTables(pool)

# read the "mtcars" table from the database (only 16 rows)
dbReadTable(pool, "mtcars")

# append mtcars2 to the "mtcars" table already in the database
dbWriteTable(pool, "mtcars", mtcars2, row.names = TRUE, append = TRUE)

# read the "mtcars" table from the database (all 32 rows)
dbReadTable(pool, "mtcars")

# get the names of the columns in the databases's table
dbListFields(pool, "mtcars")

# use dbExecute to change the "mpg" and "cyl" values of the 1st row
dbExecute(pool,
  paste(
    "UPDATE mtcars",
    "SET mpg = '22.0', cyl = '10'",
    "WHERE row_names = 'Mazda RX4'"
  )
)

# read the 1st row of "mtcars" table to confirm the previous change
dbGetQuery(pool, "SELECT * FROM mtcars WHERE row_names = 'Mazda RX4'")

# drop the "mtcars" table from the database
dbRemoveTable(pool, "mtcars")

# list the current tables in the database
dbListTables(pool)

poolClose(pool)