As a convenience, Pool implements DBIConnection methods; calling any implemented
DBI method directly on a Pool object will result in a connection being checked
out (with poolCheckout()
), the operation being performed on that connection,
and the connection being returned to the pool (with poolReturn()
).
Usage
# S4 method for Pool
dbSendQuery(conn, statement, ...)
# S4 method for Pool,ANY
dbSendStatement(conn, statement, ...)
# S4 method for Pool,character
dbGetQuery(conn, statement, ...)
# S4 method for Pool,character
dbExecute(conn, statement, ...)
# S4 method for Pool
dbListResults(conn, ...)
# S4 method for Pool,character
dbListFields(conn, name, ...)
# S4 method for Pool
dbListTables(conn, ...)
# S4 method for Pool
dbListObjects(conn, prefix = NULL, ...)
# S4 method for Pool,character
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, ...)
Arguments
- conn, dbObj
A Pool object, as returned from
dbPool()
.- statement, name, value, prefix, fields, row.names, temporary, ...
See DBI documentation.
Details
Pool cannot implement the DBI::dbSendQuery()
and DBI::dbSendStatement()
methods because they both return live ResultSet objects. This is incompatible
with the Pool model, because once a connection is returned to the pool, using
an existing ResultSet object could give erroneous results, throw an error, or
even crash the entire R process. In most cases, DBI::dbGetQuery()
and
DBI::dbExecute()
can be used instead. If you really need the control that
dbSendQuery
gives you (for example, to process a large table in chunks)
then use poolCheckout()
to get a real connection object (and don't forget
to return it to the pool using poolReturn()
afterwards).
See also
For the original documentation, see:
DBI::dbSendQuery()
(not implemented by Pool)DBI::dbSendStatement()
(not implemented by Pool)
Examples
if (requireNamespace("RSQLite", quietly = TRUE)) {
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(), dbname = ":memory:")
# 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)
} else {
message("Please install the 'RSQLite' package to run this example")
}