R help - Sep 2006 - RODBC Connections closed automatically in background

I am having a problem with RODBC's connections. It appears that
my connection to the database is closed by R automatically before
I am done with it.

Here is my code:

foo <- function(dsn) {
  db <- odbcConnect(dsn)
  odbcSetAutoCommit(db, FALSE)
  data <- someDatabaseOperation(db)
  data2 <- someLongCalculation(data)
  anotherDatabaseOperation(db, data2) # This often fails b/c the db is no longer
open.
  odbcClose(db)
}

I see some output:

  Warning: closing unused RODBC handle 9
  Warning: [RODBC] Error SQLDisconnect
  Warning: [RODBC] Error SQLFreeconnect
  Warning: [RODBC] Error in SQLFreeEnv
  Error in odbcGetErrMsg(channel) : first argument is not an open RODBC channel

I suspect that during the call to someLongCalculation(), R
considers the database connection as "unused", and therefore,
closes the connection, which prevents me from using the
connection in the call to anotherDatabaseOperation().

What causes the database connection to close? What can I do to
prevent the connection from closing implicitly?

I am using R 2.3.1 on Windows with the latest version of RODBC,
connnectin to SQL Server 2000.

Thanks in advance.

Usually I try to do all data import before I do long calculations. A
simple workaround for your problem would be:

foo <- function(dsn) {
  db <- odbcConnect(dsn)
  odbcSetAutoCommit(db, FALSE)
  data <- someDatabaseOperation(db)
  odbcClose(db)
  data2 <- someLongCalculation(data)
  db <- odbcConnect(dsn)
  odbcSetAutoCommit(db, FALSE)
  anotherDatabaseOperation(db, data2)
  odbcClose(db)
}

An other options is to include the odbcConnect and odbcClose into
someLongCalculation() and anotherDatabaseOperation()

Cheers,

Thierry

-----Oorspronkelijk bericht-----
Van: r-help-bounces op stat.math.ethz.ch
[mailto:r-help-bounces op stat.math.ethz.ch] Namens Jay Z
Verzonden: woensdag 20 september 2006 0:12
Aan: R-help op stat.math.ethz.ch
Onderwerp: [R] RODBC Connections closed automatically in background

I am having a problem with RODBC's connections. It appears that
my connection to the database is closed by R automatically before
I am done with it.

Here is my code:

foo <- function(dsn) {
  db <- odbcConnect(dsn)
  odbcSetAutoCommit(db, FALSE)
  data <- someDatabaseOperation(db)
  data2 <- someLongCalculation(data)
  anotherDatabaseOperation(db, data2) # This often fails b/c the db is
no longer open.
  odbcClose(db)
}

I see some output:

  Warning: closing unused RODBC handle 9
  Warning: [RODBC] Error SQLDisconnect
  Warning: [RODBC] Error SQLFreeconnect
  Warning: [RODBC] Error in SQLFreeEnv
  Error in odbcGetErrMsg(channel) : first argument is not an open RODBC
channel

I suspect that during the call to someLongCalculation(), R
considers the database connection as "unused", and therefore,
closes the connection, which prevents me from using the
connection in the call to anotherDatabaseOperation().

What causes the database connection to close? What can I do to
prevent the connection from closing implicitly?

I am using R 2.3.1 on Windows with the latest version of RODBC,
connnectin to SQL Server 2000.

Thanks in advance.

______________________________________________
R-help op stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.