The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.

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

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.