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 run multi-user queries on multiple databases.

Each of my requests is executed as a function:

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 open and closed in this function and that only one simple request is made between opening and closing the channel.

However, when I run a report several times (for example, when developing a report), I get warnings, such as:

 Warning: closing unused RODBC handle 41 

The more times I run the report, the higher the number of the processed message will be.

Why, if I open and close the channel in the query function, am I left with open "unused" RODBC handles?

More importantly, how can I avoid this problem?

Answer 1

I would avoid 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) } 

Thus, the connection will be closed even if there is an error. See Also ?on.exit .

[EDIT]

The above assumes that the handle was not closed because there was an error executing the request. If the request was approved, but the handle simply was not closed, then I have no idea. odbcClose returns 0 if it succeeded, so you can check it out.

[EDIT2]

As others have noted, there’s probably nothing to worry about - on the other hand, it would be interesting to find out why the connection is not closed, if you directly say that it closes. Maybe it's just milliseconds, and the query is not yet complete when the result is assigned. It doesn't really matter to me, as if the result is assigned to orders , but what else is needed for the database? But maybe there is something. In this case, you can try to give it some more time, for example.

 #... 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 } 

It sounds very stupid, but I won’t be surprised if it really works.

Another idea is that if you use Rstudio, you can get phantom error messages, for example, when using plot with a nonexistent graphic parameter for the first time, and then error free for 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 like this happens with db handlers - if so, it would be instructive to see if you get the same warnings in both RStudio and the console (Rgui or Rterm on Windows or on R in a terminal on Linux), Of course, it applies if you use Rstudio.

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

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

Answer 2

The odbcClose() function will fail if there are open transactions in the connection. In this case, this connection will remain open.