Initiation of SQL database connections in R6 rocker class

Nikolaus Pawlowski

Database connection

There are different ways to open a connection and to get data.

Prepare database with a table

db <- rocker::newDB() # New database handling object
#> dctr | New object
db$setupSQLite(dbname = tempfile()) # Setup SQLite database
#> Dctr | Driver load RSQLite
db$connect() # Open connection
#> DCtr | Database connected
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
db$disconnect() # Close connection
#> Dctr | Database disconnected

Example 1

Get query with automatic connection / disconnection

output <- db$getQuery("SELECT * FROM mtcars;") # Get query
#> 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

db$connect() # Open connection
#> DCtr | Database connected
output1 <- db$getQuery("SELECT * FROM mtcars;") # Get query 1
#> 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
output2 <- db$getQuery("SELECT * FROM mtcars;", 15) # Get query 2
#> 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
db$disconnect() # Close connection
#> Dctr | Database disconnected

Example 3

Function getQuery() is a combination of functions sendQuery(), fetch() and clearResult().

db$connect() # Open connection
#> DCtr | Database connected
db$sendQuery("SELECT * FROM mtcars;") # Send query
#> DCtR | Send query 21 characters
output <- db$fetch() # Fetch result
#> DCtR | Fetch rows all -> Received 32 rows, 11 columns, 4824 bytes
db$clearResult() # Clean up result
#> DCtr | Clear result
db$disconnect() # Close connection
#> Dctr | Database disconnected

Clean up

db$unloadDriver() # Reset database handling object
#> dctr | Driver unload RSQLite