Disconnect DBI / RSQLite within a function in R

Question

I'm building a package for internal usage and attempting to abstract away all possible database interaction from the users. I need to connect to the database and disconnect from the database within the function (I think). However, the disconnect does not work.

`my_func = function(){
    con = DBI::dbConnect(RSQLite::SQLite(), 'db_location.sqlite')
    r = DBI::dbSendQuery("SELECT * ...")
    dat = DBI::dbFetch(r)
    DBI::dbDisconnect(con)
    return(dat)
}`

If you call the function: MY_LIBRARY::my_func()

Data is returned but the connection does not terminate and a warning is displayed.

`Warning message:
In connection_release(conn@ptr) :
    There are 1 result in use. The connection will be released when 
    they are closed`

Judicative

Do you need dbClearResult(r)? You might prefer DBI::dbGetQuery instead of dbSendQuery/dbFetch, as it clears the result automatically. – Pillage

Answer 1

SQL queries are typically a three-step process (ignoring connection management):

1. send the query, accept the returned "result" object
2. fetch the result using the "result" object
3. clear the "result" object

The third step is important, as uncleared it represents resources that are being held for that query. Some database connections do not permit multiple simultaneous uncleared results, just one query at a time.

This has historically been done with:

res <- dbSendQuery(con, "SELECT ...")
dat <- dbFetch(res)
dbClearResult(res)

Some time ago (don't know the version), DBI provided a robust convenience function that changes all three of those lines into a single line of code:

dat <- dbGetQuery(con, "SELECT ...")

This function is not appropriate when the query is not returning data, such as UPDATE or INSERT, in which case you should use either dbSendStatement (optionally followed by dbGetRowsAffected) or dbExecute (which automatically calls dbGetRowsAffected).

You should not use dbGetQuery when sending data-returning queries when you are using SQL parameterization (to mitigate sql injection). Instead, you'd return to using dbSendQuery, dbBind (for the parameters), dbFetch, and dbClearResult.

Pillage

Thanks for the detailed reply. I'd like to add that recent DBI supports a params argument to dbQuery() for parametrized queries, this will call dbBind() under the hood. – Globetrotter

Thanks @krlmlr, I wondered about that and thought you might pipe up. Great! – Pillage