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!
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]]])
}
}