Getting C variables from R?

Question

Here's my problem. I want to know how many open RODBC connections I have in my R session. Perhaps this problem is related Show all open RODBC connections.

The thing is, I won't be able to use this if I do not assign my connections to a variable i.e. I do odbcConnect() instead of channel<-odbcConnect() as there would be no environmental variables created.

I've looked at the C code for the package RODBC and there seems to be a variable nChannels which contains an int of the number of open connections. Am I able to call this variable from R, or even using C functions?

How does R environments come into play here? Or is it totally unrelated? Any explanation will be appreciated as I'm extremely new in this area. Please advice if my logic in approaching this is wrong as well.

Answer 1

If users are submitting their own code, the solution is simple: provide your own function odbcConnect which internally logs the call and then dispatches to the {RODBC} package. That is, don’t give users direct access to the RODBC package.

This works as long as you either provide the setup for the user (i.e. users are never expected to call library(RODBC), nor RODBC::odbcConnect).

But if this isn’t the case, i.e. if users interact directly with the RODBC package, you’ll need to go one step further: you need to modify the RODBC package yourself. You can either do this by patching the function at runtime, but this is fairly advanced (and brittle; it breaks if RODBC upgrades and changes its implementation). Here’s a minimal example:

log_connect_expr = quote(assign('.odbc_connections', .GlobalEnv$.odbc_connections + 1, .GlobalEnv))
log_close_expr = quote(assign('.odbc_connections', .GlobalEnv$.odbc_connections - 1, .GlobalEnv))

# Inject expression into a function body at a given position, counting either
# from the beginning or from the end (if `pos` < 0).
insert_in_body = function (body, expr, pos) {
    body = as.list(body)
    if (pos < 0) pos = length(body) + pos + 1
    all_indices = seq_along(body)
    before_indices = all_indices < pos
    after_indices = all_indices >= pos
    as.call(c(body[before_indices], expr, body[after_indices]))
}

# Insert log calls into function bodies, after any error checking.

local({
    odbcDriverConnect = RODBC::odbcDriverConnect
    body(odbcDriverConnect) = insert_in_body(body(odbcDriverConnect), log_connect_expr, -1)
    odbcClose = RODBC::odbcClose
    body(odbcClose) = insert_in_body(body(odbcClose), log_close_expr, 4)

    assignInNamespace('odbcDriverConnect', odbcDriverConnect, getNamespace('RODBC'))
    assignInNamespace('odbcClose', odbcClose, getNamespace('RODBC'))
})

# Initialize counter
.GlobalEnv$.odbc_connections = 0

Alternative, just download the RODBC source code, modify it, build the package and install that locally. All these solutions are hacky, but better than rummaging around in the C internals of RODBC.

---

Reading the internal RODBC connection status isn’t practical. First off, the variable nChannels doesn’t seem to be an accurate count of the number of open connections; it’s more like an upper bound, and closing a connection does not decrease the counter.

But even if it were accurate, you cannot read it anyway because the variable is declared static, and therefore not exported.