R6 class interface for handling relational database connections using DBI package as backend. The class allows handling of connections to e.g. PostgreSQL, MariaDB and SQLite. The purpose is having an intuitive object allowing straightforward handling of SQL databases.
Installation of current released version from CRAN
install.packages("rocker")
Installation of current development version from GitHub
install.packages("devtools")
::install_github("nikolaus77/rocker") devtools
Create new rocker database handling object
Option 1
<- rocker::newDB() # New database handling object
db #> dctr | New object
Option 2
<- rocker::rocker$new() # New database handling object
db #> dctr | New object
Terminal output
Controlling terminal output
<- rocker::newDB(verbose = TRUE) # New database handling object
db #> dctr | New object
$setupPostgreSQL()
db#> Dctr | Driver load RPostgres
$unloadDriver()
db#> dctr | Driver unload RPostgres
$verbose <- FALSE # Terminal output off
db$setupPostgreSQL()
db$unloadDriver() db
$verbose <- TRUE # Terminal output on (default)
db$setupPostgreSQL()
db#> Dctr | Driver load RPostgres
$unloadDriver()
db#> dctr | Driver unload RPostgres
Structure of terminal output
Dctr | Driver load RSQLite
D = Driver (D = loaded, d = not set)
c = Connection (C = opened, c = closed)
t = Transation (T = active, t = no tranastion)
r = Result (R = available, r = no result)
Driver load RSQLite = Message text
Optional object ID
Optionally, rocker object can be labeled with an ID. This can be helpful in case terminal output of multiple rocker objects need to be distinguished.
<- rocker::newDB(id = "myDB 1") # New database handling object with ID
db1 #> myDB 1 | dctr | New object id myDB 1
<- rocker::newDB(id = "myDB 2") # New database handling object with ID
db2 #> myDB 2 | dctr | New object id myDB 2
$setupPostgreSQL()
db1#> myDB 1 | Dctr | Driver load RPostgres
$setupMariaDB()
db2#> myDB 2 | Dctr | Driver load RMariaDB
$unloadDriver()
db1#> myDB 1 | dctr | Driver unload RPostgres
$unloadDriver()
db2#> myDB 2 | dctr | Driver unload RMariaDB
$id <- NULL # Remove ID
db1$setupSQLite()
db1#> Dctr | Driver load RSQLite
$unloadDriver()
db1#> dctr | Driver unload RSQLite
$id <- "newID 1" # Add new ID
db1$setupSQLite()
db1#> newID 1 | Dctr | Driver load RSQLite
$unloadDriver()
db1#> newID 1 | dctr | Driver unload RSQLite
Object properties
Object properties are stored in the info field and can be displayed by print function.
<- rocker::newDB() # New database handling object
db #> dctr | New object
$setupPostgreSQL()
db#> Dctr | Driver load RPostgres
$info
db#> $package
#> [1] "RPostgres"
#>
#> $host
#> [1] "127.0.0.1"
#>
#> $port
#> [1] "5432"
#>
#> $dbname
#> [1] "mydb"
db#> object
#> id null
#> verbose true
#> validateQuery null
#> database
#> package RPostgres
#> host 127.0.0.1
#> port 5432
#> dbname mydb
#> status
#> driver true
#> connection false
#> transaction false
#> result false
$print()
db#> object
#> id null
#> verbose true
#> validateQuery null
#> database
#> package RPostgres
#> host 127.0.0.1
#> port 5432
#> dbname mydb
#> status
#> driver true
#> connection false
#> transaction false
#> result false
print(db)
#> object
#> id null
#> verbose true
#> validateQuery null
#> database
#> package RPostgres
#> host 127.0.0.1
#> port 5432
#> dbname mydb
#> status
#> driver true
#> connection false
#> transaction false
#> result false
$unloadDriver()
db#> dctr | Driver unload RPostgres
Connection validation – Is the earlier opened database connection still open?
<- rocker::newDB() # New database handling object
db #> dctr | New object
$setupSQLite()
db#> Dctr | Driver load RSQLite
$print()
db#> object
#> id null
#> verbose true
#> validateQuery null
#> database
#> package RSQLite
#> dbname :memory:
#> status
#> driver true
#> connection false
#> transaction false
#> result false
During connection setup, a validateQuery is looked up automatically.
$connect()
db#> DCtr | Database connected
$print()
db#> object
#> id null
#> verbose true
#> validateQuery SELECT 1
#> database
#> package RSQLite
#> dbname :memory:
#> status
#> driver true
#> connection true
#> transaction false
#> result false
Discovered validateQuery
$validateQuery
db#> [1] "SELECT 1"
Validate connection
$validateCon()
db#> DCtr | Connection valid true
#> [1] TRUE
If required, validateQuery can be defined manually.
$validateQuery <- "SELECT 2"
db$validateCon()
db#> DCtr | Connection valid true
#> [1] TRUE
$print()
db#> object
#> id null
#> verbose true
#> validateQuery SELECT 2
#> database
#> package RSQLite
#> dbname :memory:
#> status
#> driver true
#> connection true
#> transaction false
#> result false
Clean up
$disconnect()
db#> Dctr | Database disconnected
$validateCon()
db#> Dctr | Connection valid false
#> [1] FALSE
$unloadDriver()
db#> dctr | Driver unload RSQLite
The listed packages are required for some functions of rocker.
RSQLite package for handling of SQLite database connections. It is required for the setupSQLite() function of rocker class.
install.packages("RSQLite")
Setup database
Option 1
<- rocker::newDB() # New database handling object
db #> dctr | New object
$setupSQLite( # Setup SQLite database
dbdbname = ":memory:"
)#> Dctr | Driver load RSQLite
$unloadDriver() # Reset database handling object
db#> dctr | Driver unload RSQLite
Option 2
<- rocker::newDB() # New database handling object
db #> dctr | New object
$setupDriver( # Setup SQLite database
dbdrv = RSQLite::SQLite(),
dbname = ":memory:"
)#> Dctr | Driver load RSQLite
$unloadDriver() # Reset database handling object
db#> dctr | Driver unload RSQLite
RPostgres package for handling of PostgreSQL database connections. It is required for the setupPostgreSQL() function of rocker class.
install.packages("RPostgres")
Setup database
Option 1
<- rocker::newDB() # New database handling object
db #> dctr | New object
$setupPostgreSQL( # Setup PostgreSQL database
dbhost = "127.0.0.1", port = "5432", dbname = "mydb",
user = "postgres", password = "password"
)#> Dctr | Driver load RPostgres
$unloadDriver() # Reset database handling object
db#> dctr | Driver unload RPostgres
Option 2
<- rocker::newDB() # New database handling object
db #> dctr | New object
$setupDriver( # Setup PostgreSQL database
dbdrv = RPostgres::Postgres(),
host = "127.0.0.1", port = "5432", dbname = "mydb",
user = "postgres", password = "password"
)#> Dctr | Driver load RPostgres
$unloadDriver() # Reset database handling object
db#> dctr | Driver unload RPostgres
RMariaDB package for handling of MariaDB and MySQL database connections. It is required for the setupMariaDB() function of rocker class.
install.packages("RMariaDB")
Setup database
Option 1
<- rocker::newDB() # New database handling object
db #> dctr | New object
$setupMariaDB( # Setup MariaDB database
dbhost = "127.0.0.1", port = "3306", dbname = "mydb",
user = "root", password = "password"
)#> Dctr | Driver load RMariaDB
$unloadDriver() # Reset database handling object
db#> dctr | Driver unload RMariaDB
Option 2
<- rocker::newDB() # New database handling object
db #> dctr | New object
$setupDriver( # Setup MariaDB database
dbdrv = RMariaDB::MariaDB(),
host = "127.0.0.1", port = "3306", dbname = "mydb",
user = "root", password = "password"
)#> Dctr | Driver load RMariaDB
$unloadDriver() # Reset database handling object
db#> dctr | Driver unload RMariaDB
The crayon package is required for colored terminal output. If missing terminal output is monochrome.
install.packages("crayon")
There are different ways to open a connection and to get data.
Prepare database with a table
<- rocker::newDB() # New database handling object
db #> dctr | New object
$setupSQLite(dbname = tempfile()) # Setup SQLite database
db#> Dctr | Driver load RSQLite
$connect() # Open connection
db#> DCtr | Database connected
$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
$disconnect() # Close connection
db#> Dctr | Database disconnected
Example 1
Get query with automatic connection / disconnection
<- db$getQuery("SELECT * FROM mtcars;") # Get query
output #> DCtr | Database connected
#> DCtR | Send query 21 characters
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes
#> DCtR | Rows fetched 32
#> DCtR | Has completed yes
#> DCtr | Clear result
#> Dctr | Database disconnected
Example 2
Get query with manual connection / disconnection
$connect() # Open connection
db#> DCtr | Database connected
<- db$getQuery("SELECT * FROM mtcars;") # Get query 1
output1 #> DCtR | Send query 21 characters
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes
#> DCtR | Rows fetched 32
#> DCtR | Has completed yes
#> DCtr | Clear result
<- db$getQuery("SELECT * FROM mtcars;", 15) # Get query 2
output2 #> DCtR | Send query 21 characters
#> DCtR | Fetch rows 15 -> Received 15 rows, 11 columns, 3416 bytes
#> DCtR | Rows fetched 15
#> DCtR | Has completed no
#> DCtR | Fetch rows 15 -> Received 15 rows, 11 columns, 3416 bytes
#> DCtR | Rows fetched 30
#> DCtR | Has completed no
#> DCtR | Fetch rows 15 -> Received 2 rows, 11 columns, 2184 bytes
#> DCtR | Rows fetched 32
#> DCtR | Has completed yes
#> DCtr | Clear result
$disconnect() # Close connection
db#> Dctr | Database disconnected
Example 3
Function getQuery() is a combination of functions sendQuery(), fetch() and clearResult().
$connect() # Open connection
db#> DCtr | Database connected
$sendQuery("SELECT * FROM mtcars;") # Send query
db#> DCtR | Send query 21 characters
<- db$fetch() # Fetch result
output #> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes
$clearResult() # Clean up result
db#> DCtr | Clear result
$disconnect() # Close connection
db#> Dctr | Database disconnected
Clean up
$unloadDriver() # Reset database handling object
db#> dctr | Driver unload RSQLite
Some efforts were undertaken to encrypt and to protect the password in the private area of the class. The class stores the password hidden and inaccessible. Please let me know, in case you discover a way how to access the password!
<- rocker::newDB() # New database handling object
db #> dctr | New object
$setupDriver( # Setup PostgreSQL database with stored password (password and user are hidden - default behavior)
db::Postgres(),
RPostgreshost = "127.0.0.1", port = "5432", dbname = "mydb",
user = "postgres", password = "password",
protect = c("password", "user")
)#> Dctr | Driver load RPostgres
$connect() # Open connection 1; Password is stored in the class and does not need to be provided.
db#> DCtr | Database connected
<- db$getQuery("SELECT * FROM mtcars;") # Get query 1
output1 #> DCtR | Send query 21 characters
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes
#> DCtR | Rows fetched 32
#> DCtR | Has completed yes
#> DCtr | Clear result
$disconnect() # Close connection 1
db#> Dctr | Database disconnected
$connect() # Open connection 2; Password is stored in the class and does not need to be provided.
db#> DCtr | Database connected
<- db$getQuery("SELECT * FROM mtcars;") # Get query 2
output2 #> DCtR | Send query 21 characters
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes
#> DCtR | Rows fetched 32
#> DCtR | Has completed yes
#> DCtr | Clear result
$disconnect() # Close connection 2
db#> Dctr | Database disconnected
$unloadDriver() # Reset database handling object
db#> dctr | Driver unload RPostgres
In case you do not want to store the password in the class, you will need to provide it each time a connection is opened.
<- rocker::newDB() # New database handling object
db #> dctr | New object
$setupDriver( # Setup PostgreSQL database without stored password
db::Postgres(),
RPostgreshost = "127.0.0.1", port = "5432", dbname = "mydb",
user = "postgres"
)#> Dctr | Driver load RPostgres
$connect(password = "password") # Open connection 1; Password needs to be provided.
db#> DCtr | Database connected
<- db$getQuery("SELECT * FROM mtcars;") # Get query 1
output1 #> DCtR | Send query 21 characters
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes
#> DCtR | Rows fetched 32
#> DCtR | Has completed yes
#> DCtr | Clear result
$disconnect() # Close connection 1
db#> Dctr | Database disconnected
$connect(password = "password") # Open connection 2; Password needs to be provided.
db#> DCtr | Database connected
<- db$getQuery("SELECT * FROM mtcars;") # Get query 2
output2 #> DCtR | Send query 21 characters
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes
#> DCtR | Rows fetched 32
#> DCtR | Has completed yes
#> DCtr | Clear result
$disconnect() # Close connection 2
db#> Dctr | Database disconnected
$unloadDriver() # Reset database handling object
db#> dctr | Driver unload RPostgres
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.9 (driver.version), 3.37.0 (client.version)
#> $driver.version
#> [1] '2.2.9'
#>
#> $client.version
#> [1] '3.37.0'
::dbGetInfo(db$.drv) # Direct usage of 'DBI' function on 'rocker' class
DBI#> $driver.version
#> [1] '2.2.9'
#>
#> $client.version
#> [1] '3.37.0'
::dbGetInfo(db$.drv) # Direct usage of driver package, 'RSQLite', function on 'rocker' class
RSQLite#> $driver.version
#> [1] '2.2.9'
#>
#> $client.version
#> [1] '3.37.0'
$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.37.0 (db.version), :memory: (dbname), NA (username), NA (host), NA (port)
#> $db.version
#> [1] "3.37.0"
#>
#> $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.37.0"
#>
#> $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.37.0"
#>
#> $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 |
Setup database and a table with 32 rows.
<- rocker::newDB() # New database handling object
db #> dctr | New object
$setupSQLite() # Setup SQLite database
db#> Dctr | Driver load RSQLite
$connect() # Open connection
db#> DCtr | Database connected
$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
<- db$getQuery("SELECT * FROM mtcars;") # Get query -> 32 rows
output #> DCtR | Send query 21 characters
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes
#> DCtR | Rows fetched 32
#> DCtR | Has completed yes
#> DCtr | Clear result
$transaction # Transaction indicator
db#> [1] FALSE
Starting with a table with 32 rows, begin transaction 1. Delete 15 rows and commit transaction. Operations results in a table with 17 rows.
$begin() # Start transaction 1
db#> DCTr | Transaction begin
$transaction # Transaction indicator
db#> [1] TRUE
<- db$execute("DELETE FROM mtcars WHERE gear = 3;") # Modify table -> 15 rows
AFFECTED #> DCTR | Send statement 34 characters
#> DCTR | Rows affected 15
#> DCTr | Clear result
$commit() # Commit transaction 1
db#> DCtr | Transaction commit
$transaction # Transaction indicator
db#> [1] FALSE
<- db$getQuery("SELECT * FROM mtcars;") # Get query -> 17 rows
output #> DCtR | Send query 21 characters
#> DCtR | Fetch rows all -> Received 17 rows, 11 columns, 3504 bytes
#> DCtR | Rows fetched 17
#> DCtR | Has completed yes
#> DCtr | Clear result
Starting with a table with 17 rows, begin transaction 2. Delete 5 rows and rollback transaction. Operations results in a table with 17 rows.
$begin() # Start transaction 2
db#> DCTr | Transaction begin
$transaction # Transaction indicator
db#> [1] TRUE
<- db$execute("DELETE FROM mtcars WHERE gear = 5;") # Modify table -> 5 rows
AFFECTED #> DCTR | Send statement 34 characters
#> DCTR | Rows affected 5
#> DCTr | Clear result
<- db$getQuery("SELECT * FROM mtcars;") # Get query -> 12 rows
output #> DCTR | Send query 21 characters
#> DCTR | Fetch rows all -> Received 12 rows, 11 columns, 3416 bytes
#> DCTR | Rows fetched 12
#> DCTR | Has completed yes
#> DCTr | Clear result
$rollback() # Rollback transaction 2
db#> DCtr | Transaction rollback
$transaction # Transaction indicator
db#> [1] FALSE
<- db$getQuery("SELECT * FROM mtcars;") # Get query -> 17 rows
output #> DCtR | Send query 21 characters
#> DCtR | Fetch rows all -> Received 17 rows, 11 columns, 3504 bytes
#> DCtR | Rows fetched 17
#> DCtR | Has completed yes
#> DCtr | Clear result
Clean up
$disconnect() # Close connection
db#> Dctr | Database disconnected
$unloadDriver() # Reset database handling object
db#> dctr | Driver unload RSQLite
Although rocker is a R6 class, functions can be also accesses in classical S3 way.
S3 example
library(rocker)
<- newDB()
db #> dctr | New object
setupDriver(db, drv = RSQLite::SQLite(), dbname = ":memory:")
#> Dctr | Driver load RSQLite
connect(db)
#> DCtr | Database connected
writeTable(db, "mtcars", mtcars)
#> DCtr | Write table mtcars columns mpg, cyl, disp, hp, drat, wt, qsec, vs, am, gear, carb rows 32
sendQuery(db, "SELECT * FROM mtcars;")
#> DCtR | Send query 21 characters
<- fetch(db)
output #> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes
clearResult(db)
#> DCtr | Clear result
disconnect(db)
#> Dctr | Database disconnected
unloadDriver(db)
#> dctr | Driver unload RSQLite
R6 example
<- rocker::newDB()
db #> dctr | New object
$setupDriver(drv = RSQLite::SQLite(), dbname = ":memory:")
db#> Dctr | Driver load RSQLite
$connect()
db#> DCtr | Database connected
$writeTable("mtcars", mtcars)
db#> DCtr | Write table mtcars columns mpg, cyl, disp, hp, drat, wt, qsec, vs, am, gear, carb rows 32
$sendQuery("SELECT * FROM mtcars;")
db#> DCtR | Send query 21 characters
<- db$fetch()
output #> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes
$clearResult()
db#> DCtr | Clear result
$disconnect()
db#> Dctr | Database disconnected
$unloadDriver()
db#> dctr | Driver unload RSQLite
Please read the documentation of rocker class.
help("rocker-package")
help("rocker-R6-class")
help("rocker-S3-functions")
help("rocker-README")
Reading of DBI package documentation is recommended.