In rocker: Database Interface Class

DBI objects

rocker class encapsulates the DBI objects driver, connection and result. If required, these objects can be directly used with DBI functions. However, it is recommended to use this option with care! Direct usage of DBI functions, may disrupt proper function of rocker class. Many DBI functions are implemented in rocker class. Whenever possible, use the rocker class functions.

Prepare object

db <- rocker::newDB() # New database handling object
db$.drv # Empty driver
db$.con # Empty connection
db$.res # Empty result

DBIDriver-class

db$setupSQLite() # Setup SQLite database
db$.drv # 'DBI' DBIDriver-class

db$getInfoDrv() # 'rocker' class function

DBI::dbGetInfo(db$.drv) # Direct usage of 'DBI' function on 'rocker' class

RSQLite::dbGetInfo(db$.drv) # Direct usage of driver package, 'RSQLite', function on 'rocker' class

DBIConnection-class

db$connect() # Open connection
db$.con # 'DBI' DBIConnection-class

db$getInfoCon() # 'rocker' class function

DBI::dbGetInfo(db$.con) # Direct usage of 'DBI' function on 'rocker' class

RSQLite::dbGetInfo(db$.con) # Direct usage of driver package, 'RSQLite', function on 'rocker' class

Prepare table

db$writeTable("mtcars", mtcars) # Create table for testing

DBIResult-class

db$sendQuery("SELECT * FROM mtcars;") # Send query
db$.res # 'DBI' DBIResult-class

db$getInfoRes() # 'rocker' class function

DBI::dbGetInfo(db$.res) # Direct usage of 'DBI' function on 'rocker' class

RSQLite::dbGetInfo(db$.res) # Direct usage of driver package, 'RSQLite', function on 'rocker' class

Clean up

db$clearResult() # Clean up result
db$.res # Empty result
db$disconnect() # Close connection
db$.con # Empty connection
db$unloadDriver() # Reset database handling object
db$.drv # Empty driver

DBI functions in rocker

Generally, rocker function names are related to DBI function names. In rocker functions, the leading db is removed.

In DBI most functions need to be supplied with a driver ^(drv)^, connection ^(conn)^ or result ^(res)^ object. In rocker, functions automatically access the corresponding objects ^(.drv,^ ^.con^ ^and^ ^.res)^ stored in the class.

DBI example

drv <- RSQLite::SQLite() # SQLite driver
DBI::dbCanConnect( # Test parameter
  drv = drv,
  dbname = ":memory:"
)
con <- DBI::dbConnect( # Open connection
  drv = drv,
  dbname = ":memory:"
)
DBI::dbWriteTable(con, "mtcars", mtcars) # Create table for testing
res <- DBI::dbSendQuery(con, "SELECT * FROM mtcars;") # Send query
output <- DBI::dbFetch(res) # Fetch result
DBI::dbClearResult(res) # Clean up result
DBI::dbDisconnect(con) # Close connection
DBI::dbUnloadDriver(drv) # Unload driver

rocker example

db <- rocker::newDB(verbose = FALSE) # New database handling object
db$setupDriver( # Setup SQLite database
  drv = RSQLite::SQLite(),
  dbname = ":memory:"
)
db$canConnect() # Test parameter
db$connect() # Open connection
db$writeTable("mtcars", mtcars) # Create table for testing
db$sendQuery("SELECT * FROM mtcars;") # Send query
output <- db$fetch() # Fetch result
db$clearResult() # Clean up result
db$disconnect() # Close connection
db$unloadDriver() # Reset database handling object

List of functions

| rocker function | Corresponding DBI function | DBI object used | Comment | | --- | --- | --- | --- | | initialize() | none | none | | | print() | none | none | | | setupDriver() | none | driver from appropriate package | Usually, parameters provided to dbConnect() in DBI are provided to setupDriver() in rocker | | setupPostgreSQL() | none | none | RPostgres::Postgres() is used with rocker function setupDriver() | | setupMariaDB() | none | none | RMariaDB::MariaDB() is used with rocker function setupDriver() | | setupSQLite() | none | none | RSQLite::SQLite() is used with rocker function setupDriver() | | unloadDriver() | dbUnloadDriver() | driver | | | canConnect() | dbCanConnect() | driver | Usually, parameters provided to dbCanConnect() in DBI are provided to setupDriver() in rocker | | connect() | dbConnect() | driver | Usually, parameters provided to dbConnect() in DBI are provided to setupDriver() in rocker | | disconnect() | dbDisconnect() | connection | | | sendQuery() | dbSendQuery() | connection | | | getQuery() | Is not using dbGetQuery(), but has the same function | connection | Especially, combination of rocker functions sendQuery(), fetch() and clearResult() | | sendStatement() | dbSendStatement() | connection | | | execute() | Is not using dbExecute(), but has the same function | connection | Especially, combination of rocker functions sendStatement() and clearResult() | | fetch() | dbFetch() | result | | | hasCompleted() | dbHasCompleted() | result | | | getRowsAffected() | dbGetRowsAffected() | result | | | getRowCount() | dbGetRowCount() | result | | | columnInfo() | dbColumnInfo() | result | | | getStatement() | dbGetStatement() | result | | | clearResult() | dbClearResult() | result | | | begin() | dbBegin() | connection | | | commit() | dbCommit() | connection | | | rollback() | dbRollback() | connection | | | getInfoDrv() | dbGetInfo() | driver | | | getInfoCon() | dbGetInfo() | connection | | | getInfoRes() | dbGetInfo() | result | | | isValidDrv() | dbIsValid() | driver | | | isValidCon() | dbIsValid() | connection | | | isValidRes() | dbIsValid() | result | | | createTable() | dbCreateTable() | connection | | | appendTable() | dbAppendTable() | connection | | | writeTable() | dbWriteTable | connection | | | readTable() | dbReadTable | connection | | | removeTable() | dbRemoveTable() | connection | | | existsTable() | dbExistsTable() | connection | | | listFields() | dbListFields() | connection | | | listObjects() | dbListObjects() | connection | | | listTables() | dbListTables() | connection | |