How does R handle closing of data base connections

Question

If I create a data base connection within a function, the connection objects gets destroyed when the function finished executing. Does this reliably close the data base connection, or would it better to to close it manually first?

Why I need to know this:

I am working on a package that creates data base connections on the fly with either RODBC or RJDBC as backend. I designed my function interfaces so that you can pass in either username and password, or a connection object. Usually, when I pass in a connection object I do not want the connection to be closed on the termination of the function, while when I pass in username and password I want it to be closed.

If I do not have to worry about open connections, it would simplify things a lot for me and also save me a lot of headaches.

Answer & More:

I markded Benjamin's answer as the answer since it gives good advice, though actually what I was looking for is more Marek's comment that (paraphrased:) connections can be open after the connection object is destroyed and there is no way to access them from R any more.

I ended up going for a solution that involves creating an R6 class and defining finalize() method that closes the connection (it's a more powerful than on.exit()), but that is beyond the scope of this Question.

I'm just struggling with `RJDBC` which does not close connection even when object was removed. You can close connection by `dbDisconnect(name)`, but there is no way (AFAIK) to close it when you lost handle (`dbListConnection` won't work). — Marek, Aug 30 '17 at 11:11
Side note: I switch from `RODBC` to `odbc` (https://cran.r-project.org/package=odbc) I recommend to look on it. — Marek, Aug 30 '17 at 11:12
A pity. Sadly, I have to use RJDBC :(. That would actually already answer my question more or less. — Stefan F, Aug 30 '17 at 15:52

Benjamin · Accepted Answer · 2017-08-30T12:03:31.050

I write a lot of functions that create ODBC connections when they are called. My standard practice is:

conn <- RODBC::odbcDriverConnect(...)
on.exit(RODBC::odbcClose(conn))

By putting the creation of the object and the code for its closing next to each other, I know that the connection will be closed when the function is finished. Using on.exit has the added advantage of closing the connection even if the function stops on an error.

Edit:

In the problem as your edit has described it, I think the same pattern is relevant. You need to declare on.exit in a manner that it only gets called if you don't provide the connection object.

db_function <- function(conn = NULL, uid, pwd){
  if (is.null(conn)){
    conn <- RODBC::odbcDriverConnect(...) # Build conn with uid and pwd
    on.exit(RODBC::odbcClose(conn))
  }
}

A trivial example to show by-passing on.exit

test_fun <- function(on_exit = FALSE){
  if (on_exit) on.exit(print("hello world"))

  "Look at me"
}

test_fun()

test_fun(TRUE)

This is indeed what I usually do, however, my current use case is a bit more complicated. I do not always want the connection to be closed on termination of the function. I added some clarifications to the initial question. — Stefan F, Aug 30 '17 at 10:46

How does R handle closing of data base connections

1 Answers1

Edit: