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
#> dctr | New object
db$.drv # Empty driver
#> NULL
db$.con # Empty connection
#> NULL
db$.res # Empty result
#> NULL

DBIDriver-class

db$setupSQLite() # Setup SQLite database
#> Dctr | Driver load RSQLite
db$.drv # 'DBI' DBIDriver-class
#> <SQLiteDriver>

db$getInfoDrv() # 'rocker' class function
#> Dctr | Driver info 2.3.9 (driver.version), 3.47.1 (client.version)
#> $driver.version
#> [1] '2.3.9'
#> 
#> $client.version
#> [1] '3.47.1'

DBI::dbGetInfo(db$.drv) # Direct usage of 'DBI' function on 'rocker' class
#> $driver.version
#> [1] '2.3.9'
#> 
#> $client.version
#> [1] '3.47.1'

RSQLite::dbGetInfo(db$.drv) # Direct usage of driver package, 'RSQLite', function on 'rocker' class
#> $driver.version
#> [1] '2.3.9'
#> 
#> $client.version
#> [1] '3.47.1'

DBIConnection-class

db$connect() # Open connection
#> DCtr | Database connected
db$.con # 'DBI' DBIConnection-class
#> <SQLiteConnection>
#>   Path: :memory:
#>   Extensions: TRUE

db$getInfoCon() # 'rocker' class function
#> DCtr | Connection info 3.47.1 (db.version), :memory: (dbname), NA (username), NA (host), NA (port)
#> $db.version
#> [1] "3.47.1"
#> 
#> $dbname
#> [1] ":memory:"
#> 
#> $username
#> [1] NA
#> 
#> $host
#> [1] NA
#> 
#> $port
#> [1] NA

DBI::dbGetInfo(db$.con) # Direct usage of 'DBI' function on 'rocker' class
#> $db.version
#> [1] "3.47.1"
#> 
#> $dbname
#> [1] ":memory:"
#> 
#> $username
#> [1] NA
#> 
#> $host
#> [1] NA
#> 
#> $port
#> [1] NA

RSQLite::dbGetInfo(db$.con) # Direct usage of driver package, 'RSQLite', function on 'rocker' class
#> $db.version
#> [1] "3.47.1"
#> 
#> $dbname
#> [1] ":memory:"
#> 
#> $username
#> [1] NA
#> 
#> $host
#> [1] NA
#> 
#> $port
#> [1] NA

Prepare table

db$writeTable("mtcars", mtcars) # Create table for testing
#> DCtr | Write table mtcars columns mpg, cyl, disp, hp, drat, wt, qsec, vs, am, gear, carb rows 32

DBIResult-class

db$sendQuery("SELECT * FROM mtcars;") # Send query
#> DCtR | Send query 21 characters
db$.res # 'DBI' DBIResult-class
#> <SQLiteResult>
#>   SQL  SELECT * FROM mtcars;
#>   ROWS Fetched: 0 [incomplete]
#>        Changed: 0

db$getInfoRes() # 'rocker' class function
#> DCtR | Result info SELECT * FROM mtcars; (statement), 0 (row.count), 0 (rows.affected), FALSE (has.completed)
#> $statement
#> [1] "SELECT * FROM mtcars;"
#> 
#> $row.count
#> [1] 0
#> 
#> $rows.affected
#> [1] 0
#> 
#> $has.completed
#> [1] FALSE

DBI::dbGetInfo(db$.res) # Direct usage of 'DBI' function on 'rocker' class
#> $statement
#> [1] "SELECT * FROM mtcars;"
#> 
#> $row.count
#> [1] 0
#> 
#> $rows.affected
#> [1] 0
#> 
#> $has.completed
#> [1] FALSE

RSQLite::dbGetInfo(db$.res) # Direct usage of driver package, 'RSQLite', function on 'rocker' class
#> $statement
#> [1] "SELECT * FROM mtcars;"
#> 
#> $row.count
#> [1] 0
#> 
#> $rows.affected
#> [1] 0
#> 
#> $has.completed
#> [1] FALSE

Clean up

db$clearResult() # Clean up result
#> DCtr | Clear result
db$.res # Empty result
#> NULL
db$disconnect() # Close connection
#> Dctr | Database disconnected
db$.con # Empty connection
#> NULL
db$unloadDriver() # Reset database handling object
#> dctr | Driver unload RSQLite
db$.drv # Empty driver
#> NULL

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:"
)
#> [1] TRUE
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
#> [1] TRUE
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

- DBI objects
- DBI functions in rocker
  - List of functions