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
<- rocker::newDB() # New database handling object
db #> dctr | New object
$.drv # Empty driver
db#> NULL
$.con # Empty connection
db#> NULL
$.res # Empty result
db#> NULL
$setupSQLite() # Setup SQLite database
db#> Dctr | Driver load RSQLite
$.drv # 'DBI' DBIDriver-class
db#> <SQLiteDriver>
$getInfoDrv() # 'rocker' class function
db#> Dctr | Driver info 2.2.16 (driver.version), 3.39.2 (client.version)
#> $driver.version
#> [1] '2.2.16'
#>
#> $client.version
#> [1] '3.39.2'
::dbGetInfo(db$.drv) # Direct usage of 'DBI' function on 'rocker' class
DBI#> $driver.version
#> [1] '2.2.16'
#>
#> $client.version
#> [1] '3.39.2'
::dbGetInfo(db$.drv) # Direct usage of driver package, 'RSQLite', function on 'rocker' class
RSQLite#> $driver.version
#> [1] '2.2.16'
#>
#> $client.version
#> [1] '3.39.2'
$connect() # Open connection
db#> DCtr | Database connected
$.con # 'DBI' DBIConnection-class
db#> <SQLiteConnection>
#> Path: :memory:
#> Extensions: TRUE
$getInfoCon() # 'rocker' class function
db#> DCtr | Connection info 3.39.2 (db.version), :memory: (dbname), NA (username), NA (host), NA (port)
#> $db.version
#> [1] "3.39.2"
#>
#> $dbname
#> [1] ":memory:"
#>
#> $username
#> [1] NA
#>
#> $host
#> [1] NA
#>
#> $port
#> [1] NA
::dbGetInfo(db$.con) # Direct usage of 'DBI' function on 'rocker' class
DBI#> $db.version
#> [1] "3.39.2"
#>
#> $dbname
#> [1] ":memory:"
#>
#> $username
#> [1] NA
#>
#> $host
#> [1] NA
#>
#> $port
#> [1] NA
::dbGetInfo(db$.con) # Direct usage of driver package, 'RSQLite', function on 'rocker' class
RSQLite#> $db.version
#> [1] "3.39.2"
#>
#> $dbname
#> [1] ":memory:"
#>
#> $username
#> [1] NA
#>
#> $host
#> [1] NA
#>
#> $port
#> [1] NA
Prepare table
$writeTable("mtcars", mtcars) # Create table for testing
db#> DCtr | Write table mtcars columns mpg, cyl, disp, hp, drat, wt, qsec, vs, am, gear, carb rows 32
$sendQuery("SELECT * FROM mtcars;") # Send query
db#> DCtR | Send query 21 characters
$.res # 'DBI' DBIResult-class
db#> <SQLiteResult>
#> SQL SELECT * FROM mtcars;
#> ROWS Fetched: 0 [incomplete]
#> Changed: 0
$getInfoRes() # 'rocker' class function
db#> 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
::dbGetInfo(db$.res) # Direct usage of 'DBI' function on 'rocker' class
DBI#> $statement
#> [1] "SELECT * FROM mtcars;"
#>
#> $row.count
#> [1] 0
#>
#> $rows.affected
#> [1] 0
#>
#> $has.completed
#> [1] FALSE
::dbGetInfo(db$.res) # Direct usage of driver package, 'RSQLite', function on 'rocker' class
RSQLite#> $statement
#> [1] "SELECT * FROM mtcars;"
#>
#> $row.count
#> [1] 0
#>
#> $rows.affected
#> [1] 0
#>
#> $has.completed
#> [1] FALSE
Clean up
$clearResult() # Clean up result
db#> DCtr | Clear result
$.res # Empty result
db#> NULL
$disconnect() # Close connection
db#> Dctr | Database disconnected
$.con # Empty connection
db#> NULL
$unloadDriver() # Reset database handling object
db#> dctr | Driver unload RSQLite
$.drv # Empty driver
db#> NULL
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
<- RSQLite::SQLite() # SQLite driver
drv ::dbCanConnect( # Test parameter
DBIdrv = drv,
dbname = ":memory:"
)#> [1] TRUE
<- DBI::dbConnect( # Open connection
con drv = drv,
dbname = ":memory:"
)::dbWriteTable(con, "mtcars", mtcars) # Create table for testing
DBI<- DBI::dbSendQuery(con, "SELECT * FROM mtcars;") # Send query
res <- DBI::dbFetch(res) # Fetch result
output ::dbClearResult(res) # Clean up result
DBI::dbDisconnect(con) # Close connection
DBI::dbUnloadDriver(drv) # Unload driver DBI
rocker example
<- rocker::newDB(verbose = FALSE) # New database handling object
db $setupDriver( # Setup SQLite database
dbdrv = RSQLite::SQLite(),
dbname = ":memory:"
)$canConnect() # Test parameter
db#> [1] TRUE
$connect() # Open connection
db$writeTable("mtcars", mtcars) # Create table for testing
db$sendQuery("SELECT * FROM mtcars;") # Send query
db<- db$fetch() # Fetch result
output $clearResult() # Clean up result
db$disconnect() # Close connection
db$unloadDriver() # Reset database handling object db
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 |