R help - May 2001 - RODBC: closing databases

Dear all,

platform i386-pc-mingw32
arch     x86            
os       Win32          
system   x86, Win32     
status                  
major    1              
minor    2.3            
year     2001           
month    04             
day      26             
language R

I wrote the following to import tables in different database formats
(Access, Visual FoxPro, etc.):

getTable <- function(Type, Table){
  if(!is.element("package:RODBC", search())){
    cat("Loading RODBC package", "\n")
    invisible(library(RODBC))
    }
  Channel <- odbcConnect(Type)
  query <- paste("select * from", Table)
  tab <- sqlQuery(Channel, query)
  odbcClose(Channel)
  tab
  }

Then something like:

CaPds <- getTable(Type = "VFP", Table = "CaPds")

does the job: the data.frame is quickly and properly obtained. However,
when I go back to the database management system and try to update the
original table, I get an error message saying that I cannot access the
file. I have to close R to be able to update the table.

How do I close an open table ?

Best regards,

Renaud

-- 
Dr Renaud Lancelot, v?t?rinaire
CIRAD, D?partement Elevage et M?decine V?t?rinaire (CIRAD-Emvt)
Programme Productions Animales
http://www.cirad.fr/presentation/programmes/prod-ani.shtml

ISRA-LNERV                      tel    (221) 832 49 02
BP 2057 Dakar-Hann              fax    (221) 821 18 79 (CIRAD)
Senegal                         e-mail renaud.lancelot at cirad.fr
-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at
stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._

On Mon, May 14, 2001 at 03:32:46PM +0000, Renaud Lancelot
wrote:
> Dear all,
> 
> platform i386-pc-mingw32
> arch     x86            
> os       Win32          
> system   x86, Win32     
> status                  
> major    1              
> minor    2.3            
> year     2001           
> month    04             
> day      26             
> language R
> 
> I wrote the following to import tables in different database formats
> (Access, Visual FoxPro, etc.):
> 
> getTable <- function(Type, Table){
>   if(!is.element("package:RODBC", search())){
>     cat("Loading RODBC package", "\n")
>     invisible(library(RODBC))
>     }
>   Channel <- odbcConnect(Type)
>   query <- paste("select * from", Table)
>   tab <- sqlQuery(Channel, query)
>   odbcClose(Channel)
>   tab
>   }
> 
> Then something like:
> 
> CaPds <- getTable(Type = "VFP", Table = "CaPds")
> 
> does the job: the data.frame is quickly and properly obtained. However,
> when I go back to the database management system and try to update the
> original table, I get an error message saying that I cannot access the
> file. I have to close R to be able to update the table.
> 
> How do I close an open table ?

Hi,

You are supposed to use odbcClose, just like you did.  This just calls
SQLDisconnect together with a bit of cleanup and reinitialisation, so there
is not much scope for an RODBC bug here (although I have thought that before
and been wrong ... :-) )

I did a little test with R on linux:
> odbcConnect("test","xx","secret")
[1] 0
> system ("netstat")
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 athome.laps:netbios-ssn dell.lapsley:1107       ESTABLISHED 
Active UNIX domain sockets (w/o servers)
Proto RefCnt Flags       Type       State         I-Node Path
unix  11     [ ]         DGRAM                    1309   /dev/log
unix  3      [ ]         STREAM     CONNECTED     11625 
/var/lib/mysql/mysql.sock
unix  3      [ ]         STREAM     CONNECTED     11624  
unix  3      [ ]         STREAM     CONNECTED     11613  /tmp/.X11-unix/X0

 
<---------------------------snipped------------------------>
> odbcClose(0)
[1] 1
> system("netstat")
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 athome.laps:netbios-ssn dell.lapsley:1107       ESTABLISHED 
Active UNIX domain sockets (w/o servers)
Proto RefCnt Flags       Type       State         I-Node Path
unix  11     [ ]         DGRAM                    1309   /dev/log
unix  3      [ ]         STREAM     CONNECTED     11613  /tmp/.X11-unix/X0
       <------------------snipped------------------------>

As you can see the open connection to the musql socket has 
vanished, as it is supposed to.

I think that there may be a problem with the ODBC driver supplied by your
RDBMS vendor, which is not closing the file in response to
the disconnect request.  Perhaps you should contact them and ask
for a bug-fix.  

BTW, the design does seem to take a rather primitive view on table locking
if a simple SQLConnect prevents updates while it persists.  mysql locking
is not terribly sophisticated but you can certainly do updates while
odbc or other sessions are connected. 


Regards,

Michael
-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at
stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._