How to avoid duplicate JDBC Connection created by RJDBC dbConnect

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

The implementation of dbConnect in RJDBC version 0.1-5 thru 0.2-2 (http://www.rforge.net/RJDBC/news.html) erroneously creates two (duplicate) connections. The RJDBC version currently in use can be obtained by entering citation("RJDBC") at the R command prompt.

RJDBC dbDisconnect only closes one of the two connections, leaving the duplicate connection orphaned. The issue has been reported at https://github.com/s-u/RJDBC/commit/c6a0907822d6bcfe003f4de38bd4c65ae7c261aa#commitcomment-4772766 and https://github.com/s-u/RJDBC/issues/1.

There are two workarounds until RJDBC dbConnect is fixed:

  1. Avoid RJDBC dbConnect and dbDisconnect. Invoke JDBC DriverManager.getConnection() and JDBC Connection.close() directly.
    drv = JDBC("com.teradata.jdbc.TeraDriver","c:\terajdbc\terajdbc4.jar;c:\terajdbc\tdgssconfig.jar")
    con = .jcall("java/sql/DriverManager","Ljava/sql/Connection;","getConnection", "jdbc:teradata://system","user","password")
    s = .jcall(con, "Ljava/sql/Statement;", "createStatement")
    rs = .jcall(s, "Ljava/sql/ResultSet;", "executeQuery", "SELECT SessionNo,TRIM(UserName),LogonSource FROM DBC.SessionInfo ORDER BY SessionNo")
    md = .jcall(rs, "Ljava/sql/ResultSetMetaData;", "getMetaData")
    jr = new("JDBCResult", jr=rs, md=md, stat=s, pull=.jnull())
    fetch(jr, -1)
    .jcall(rs,"V","close")
    .jcall(s,"V","close")
    .jcall(con,"V","close")

  2. Overwrite RJDBC dbConnect with the expected fix.
    setMethod("dbConnect", "JDBCDriver", def=function(drv, url, user='', password='', ...) {
    jc <- .jcall("java/sql/DriverManager","Ljava/sql/Connection;","getConnection", as.character(url)[1], as.character(user)[1], as.character(password)[1], check=FALSE)
    if (is.jnull(jc) && !is.jnull(drv@jdrv)) {
    # ok one reason for this to fail is its interaction with rJava's
    # class loader. In that case we try to load the driver directly.
    oex <- .jgetEx(TRUE)
    p <- .jnew("java/util/Properties")
    if (length(user)==1 && nchar(user)) .jcall(p,"Ljava/lang/Object;","setProperty","user",user)
    if (length(password)==1 && nchar(password)) .jcall(p,"Ljava/lang/Object;","setProperty","password",password)
    l <- list(...)
    if (length(names(l))) for (n in names(l)) .jcall(p, "Ljava/lang/Object;", "setProperty", n, as.character(l[[n]]))
    jc <- .jcall(drv@jdrv, "Ljava/sql/Connection;", "connect", as.character(url)[1], p)
    }
    .verify.JDBC.result(jc, "Unable to connect JDBC to ",url)
    new("JDBCConnection", jc=jc, identifier.quote=drv@identifier.quote)},
    valueClass="JDBCConnection")

    .verify.JDBC.result <- function (result, ...) {
    if (is.jnull(result)) {
    x <- .jgetEx(TRUE)
    if (is.jnull(x))
    stop(...)
    else
    stop(...," (",.jcall(x, "S", "getMessage"),")")
    }
    }

    drv = JDBC("com.teradata.jdbc.TeraDriver","c:\terajdbc\terajdbc4.jar;c:\terajdbc\tdgssconfig.jar")
    con = dbConnect(drv,"jdbc:teradata://system","user","password")
    dbGetQuery(con,"SELECT SessionNo,TRIM(UserName),LogonSource FROM DBC.SessionInfo ORDER BY SessionNo")
    dbDisconnect(con)