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`

Do you need `dbClearResult(r)`? You might prefer `DBI::dbGetQuery` instead of `dbSendQuery`/`dbFetch`, as it clears the result automatically. — r2evans, Jul 06 '18 at 16:03

score 14 · Accepted Answer · answered Jul 06 '18 at 16:17

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

send the query, accept the returned "result" object
fetch the result using the "result" object
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.

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. — krlmlr, Jul 07 '18 at 14:11
Thanks @krlmlr, I wondered about that and thought you might pipe up. Great! — r2evans, Jul 07 '18 at 20:22

Disconnect DBI / RSQLite within a function in R

1 Answers1