Show all open RODBC connections

Question

Does anyone know how to do this? showConnections won't list any open connections from odbcConnect.

Quiddity

Answer 1

You can narrow down your search in the following way, which will return all variables of your environment currently of the class RODBC.

envVariables<-ls()
bools<-sapply(envVariables, function(string){
        class(get(string))=="RODBC"
    })

rodbcObj<-envVariables[bools]

Closed connections are still of the class RODBC though, so there is still a little work to be done here.

We can define a function, using trycatch, that will try to get the connection info of the associated RODBC object. If it is an open connection, then this command will run fine, and we return the string of the variable name.

If the RODBC object is not an open connection, this will throw an error, which we catch and, in the way I've implemented, return NA. You could return any number of things here.

openConns<-function(string){

   tryCatch({
            result<-odbcGetInfo(get(string))
            string
        }, error = function(e){
            NA
        })
}

We then remove the return value that corresponds to the error. In my case, NA, so I do na.omit on the return.

na.omit(sapply(rodbcObj, openConns))

Or alternatively

result<-sapply(rodbcObj, openConns)
result[!is.na(result)]

Any questions or comments on it let me know

-DMT

Bedew

To check the connection status of an RODBC "channel" you could call the private function RODBC:::odbcValidChannel (three colons!!!) which you can expect to be stable since it is used widely within RODBC (I don't know why such an important function has been made public in RODBC). The package RODBCext has a similar function but also private within the package. – Trioxide