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?

Turbosupercharger

Are you sure the odbcClose succeeded? – Unpractical

@Unpractical No, how would one go about checking this? – Turbosupercharger

Wrap a print call around it. It returns a value invisibly: TRUE for success, and FALSE for failure. – Unpractical

I'll try if(odbcClose(db.connex)!=TRUE) warning("ODBC channel not closed") to see if I can't catch it when it happens. – Turbosupercharger

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 :)

Magbie

good to know the on.exit() trick, but in this case the queries are completing successfully. – Turbosupercharger

Answer 2

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

Triphthong

isn't the processing serial, though? How would execution reach odbcClose() if there were still transactions (presumably from the sqlQuery() call) on the connection? Regardless, how can I avoid this issue? – Turbosupercharger

The odd thing is that I have executed your code exactly as is (of course, by using a similar query on one of my tables), and it does not give me any warning about closing unused handles.. However, I ran this query multiple times on the same database.. – Triphthong

The warnings come later, if you evaluate the code multiple times. Apparently there's a garbage collection routine that closes unused connections after a while. Unfortunately, AFAIK, there's no way to list all open connections to quickly audit whether the connection was closed/which connections remain open. – Turbosupercharger

Yes, unused connections are indeed closed by gc(), sometimes it happens when you call the function, sometimes later. The warning is nothing to worry about, however. – Triphthong

@mdsumner showConnections(all=TRUE) does not list RODBC connections. – Turbosupercharger