[R] RODBC: closing databases

Michael Lapsley ml at lapsley.ukshells.co.uk
Tue May 15 22:52:13 CEST 2001


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
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._



More information about the R-help mailing list