3

We've adopted RJDBC for it's speed (over RODBC) but are running into issues with ensuring that all open database connections are closed at the end of an R session. The issue is that after batch runs we'll often have 100+ sleeping DB connections. Our server is running Microsoft SQL Server 2012.

The connection string is of the form:

drv <<- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver", "C:/Program Files/Microsoft JDBC Driver 4.2 for SQL Server/sqljdbc_4.2/enu/sqljdbc42.jar")
dbConnection <<- dbConnect(drv, "jdbc:sqlserver://s26",integratedSecurity=TRUE,databaseName="XXXXXX")

How do I go about ensuring that all active DB connections are closed? I found a function which seems like it would work if I were using RMySQL, but I'm not. The RJDBC documentation also does not describe any method of listing connections so they can be closed. Also, the common DBI function dbListConnections() does not seem to work for RJDBC.

Your help is apppreciated!

Community
  • 1
  • 1
Abe
  • 156
  • 5
  • 17
  • i have not used RJDBC( not sure why you think its faster ) what I do in my case is I use in RODBC with sql server 2012 on.exit(odbcClose('')) and it pretty much ensures there is no open connections – Bg1850 Nov 03 '15 at 00:17
  • Hmmm, thanks for your input @Bg1850. I'm thinking of moving to RODBC since RJDBC's dbWriteTable functionality and lack of connection tracking are proving quite troublesome. I've heard both that RODBC is slow and RJDBC is fast, and vice versa, but at this point functionality is more important than speed. Some of the newer packages like rsqlserver are even faster, but much less robust. Thanks again. If I do move to RODBC, I'll use your contribution. Thanks! – Abe Nov 03 '15 at 18:34

1 Answers1

4

I know this question was asked a while ago, but I'm answering it in case someone else was looking for a solution.

You can check if a global variable is a JDBC Connection and close it.
Here is a sample code of what I do:

    var <- as.list(.GlobalEnv)
    var_names <- names(var)

    for (i in seq_along(var_names)){
        if (class(var[[var_names[i]]]) == "JDBCConnection"){
            dbDisconnect(var[[var_names[i]]])
        }
    }
creativename
  • 398
  • 2
  • 15