DBI-connection: DBIConnection methods.

In pool: Object Pooling

Description

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 signature 'Pool'
dbSendQuery(conn, statement, ...)

## S4 method for signature 'Pool,ANY'
dbSendStatement(conn, statement, ...)

## S4 method for signature 'Pool,character'
dbGetQuery(conn, statement, ...)

## S4 method for signature 'Pool,character'
dbExecute(conn, statement, ...)

## S4 method for signature 'Pool'
dbListResults(conn, ...)

## S4 method for signature 'Pool,character'
dbListFields(conn, name, ...)

## S4 method for signature 'Pool'
dbListTables(conn, ...)

## S4 method for signature 'Pool'
dbListObjects(conn, prefix = NULL, ...)

## S4 method for signature 'Pool,character'
dbReadTable(conn, name, ...)

## S4 method for signature 'Pool,ANY'
dbWriteTable(conn, name, value, ...)

## S4 method for signature 'Pool'
dbCreateTable(conn, name, fields, ..., row.names = NULL, temporary = FALSE)

## S4 method for signature 'Pool'
dbAppendTable(conn, name, value, ..., row.names = NULL)

## S4 method for signature 'Pool,ANY'
dbExistsTable(conn, name, ...)

## S4 method for signature 'Pool,ANY'
dbRemoveTable(conn, name, ...)

## S4 method for signature '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)

• DBI::dbAppendTable()

• DBI::dbCreateTable()

• DBI::dbGetQuery()

• DBI::dbExecute()

• DBI::dbIsReadOnly()

• DBI::dbListFields()

• DBI::dbListObjects()

• DBI::dbListResults()

• DBI::dbListTables()

• DBI::dbReadTable()

• DBI::dbWriteTable()

• DBI::dbExistsTable()

• DBI::dbRemoveTable()

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")
}

pool documentation built on Jan. 15, 2021, 3:31 p.m.