Why am I getting warnings about closing unused RODBC handles?

Question

I use RODBC with R and knitr to do some reporting using various production databases. In some of these reports I'm running multiplie queries against multiple databases.

Each of my queries is carried out in a function of the form:

get.total.orders <- function(db.connex.string, start.date, end.date){
    db.connex <- odbcDriverConnect(db.connex.string)
    ord.qry <- sprintf("SELECT ord_OrderReference AS 'order.ref',
ord_RegisterDate as 'register.date'
FROM Orders
WHERE ord_RegisterDate >= '%s' AND ord_RegisterDate < '%s'",
                       start.date, end.date)
    orders <- sqlQuery(db.connex, ord.qry)
    odbcClose(db.connex)
    return(orders)
}

Note that the ODBC channel is opened and closed in this function, and that only a single, simple query is run between the opening and closing of the channel.

Nonetheless, when I run the report more than once (e.g. when developing the report), I receive warnings such as the following:

Warning: closing unused RODBC handle 41

The more times I run the report, the higher the number of the handle reported in the error becomes.

Why, if I'm opening and closing the channel in the query function, am I being left with open, 'unused' RODBC handles?

More importantly, how can I avoid this issue?

Answer 1

I would avoid it like this using on.exit:

get.total.orders <- function(db.connex.string, start.date, end.date){
   db.connex <- odbcDriverConnect(db.connex.string)
   on.exit(odbcClose(db.connex))  # <-----------------------   change here
   ord.qry <- sprintf("SELECT ord_OrderReference AS 'order.ref',
      ord_RegisterDate as 'register.date'
         FROM Orders
         WHERE ord_RegisterDate >= '%s' AND ord_RegisterDate < '%s'",
         start.date, end.date)
 orders <- sqlQuery(db.connex, ord.qry)
 return(orders)
 }

This way, the connection will be closed even if there is an error. See also ?on.exit.

[EDIT]

The above presumes that the handle was not closed because there was an error executing the query. If the query was ok but the handle just wasn't closed then I have no idea. odbcClose returns 0 if succeeded so you might check that.

[EDIT2]

As others have pointed out, this is probably nothing to worry about - on the other hand, it would still be interesting to figure out why the connection is not closed if you explicitly tell it to close. Maybe it is just a matter of milliseconds and the query is not yet finished when the result is being assigned. This doesn't make much sense to me as if the result is assigned to orders then what else is there to be about the database? But maybe there is something. In that case, one might try to give it some more time eg.

#...
orders <- sqlQuery(db.connex, ord.qry)
orders # or force(orders) - to just evaluate the result once more
Sys.sleep(0.01)  # give it 10 milliseconds
orders # or return(orders) - to return the result
# presuming on.exit as before - so odbcClose will happen here too
}

This sounds really stupid but I wouldn't be too surprised if it would actually work.

Another idea is that if you are using Rstudio then you may get some phantom error messages as it happens for example, when using plot with a non-existing graphical parameter for the fist time, and then with no errors on the second time.

plot(1, bimbo=2)  # here you get some warnings as bimbo is not a graphical parameter
plot(2)   # nothing wrong here but RStudio replays the previous warnings

Maybe something similar happens with db handlers -- if that is the case, it would be instructive to see if you get the same warnings in both RStudio and console (Rgui or Rterm in windows or running R in a terminal in linux). That of course applies if you are using Rstudio.

And finally, you might try posting this on r-help as Brian Ripley (one of the authors of RODBC) is there but not here.

So as you see, I don't have a real answer and if it takes too much effort to figure it out, I would recommend not worrying about it :)

Answer 2

The function odbcClose() will fail if there are open transactions on a connection. This connection will remain open in this case.