[R] RODBC connection die - using more than 1 Rgui/Rcmdr

[jaey.ahn]

First of all, I apologize for misleading you by mentioning irrelevant error
message. Forget about the error message I stated in the first place. I
should have clearly stated in the first place. No error message was shown at
the moment of the problem. Without any warnings and error message shown, my
entire network connection was killed. 
Under the circumstance that my computer was thoroughly disconnected from the
network,  
I tried to re-connect through RODBC and I was left with the error message as
a result. 
Thus, as you mentioned in the first reply, yes I admit that error message is
a matter of Oracle. not RODBC, since it explains that no connection is
available to the database. 

Second, I suspect that it only matters with RODBC and Oracle. I've not
checked with other databases. 
Thus only users with oracle access might be able to handle this problem.
Sorry that I am not able to test in various environments. The following may
be the example to reproduce the same error. Only person with Oracle is
applicable. 

   Let's assume I have a Oracle database "DB1". 
   Within "DB1", I have three tables with identical structure.(to make the
example simple). 
   ("TB1","TB2","TB3") 
   TB1 has data as follows.    
   <<TB1>> 
   Col1 
   1,2,3,4,5,6,7,8,9,..

   Col2 
   A,B,C,D,E,F,G,H,... 
 
   Col3 
   a,b,c,d,e,f,g,h,... 

   The table contain hugh amounts of rows. So it will take a while to copy
entire table to TB2 or either TB3. (at least 20 secs)   

   What I try to demonstrate next is to reproduce the disconnection error 
   when conducting sqlSave command with more than 1 Rgui with RODBC.

   library(RODBC) 
   initdb = function() {	
	odbcCloseAll()
	Con <- odbcConnect(dsn="DB1",uid="AA",pwd="ABC",case="oracle")
	assign('Con', Con, env =.GlobalEnv) 
   }

1. Open Rgui (1)
   initdb() 
   qry <- "select * from TB1"
   tb1 <- sqlQuery(Con, qry)  

2. Open another Rgui(2)  
   initdb() 
   qry <- "select * from TB1"
   tb1 <- sqlQuery(Con, qry)  


3. When both commands above is done completely, 
type the following command in each Rgui appropriately. 

Rgui(1):   sqlSave(Con,tb1, "TB2", append=TRUE, rownames= FALSE) 
Rgui(2):   sqlSave(Con,tb1, "TB3", append=TRUE, rownames= FALSE) 
           

The operating moments for both command should be overlapped more than 20
seconds, and 
you will face the same problem as I did. (That's why I doubt the stability
of RODBC with Oracle database). 

* if you didn't see the problem, try to enlarge the data amount
   or 
   open more Rgui's and apply for the same instruction as above. 


I was just wondering whether someone out there might encounter the same
problem, and how they deal with the problem. 


If there's anyone who could enlighten me, your help will be greatly
appreciated. Thank you in advance. 





Duncan Murdoch-2 wrote:
> 
> On 05/01/2009 9:26 PM, jaey.ahn wrote:
>> Yes, I am aware of that. 
>> RODBC connection is automatically shutting down without any warnings or
>> error message. (That's why I jotted down Oracle error message when trying
>> to
>> reconnect). 
>> 
>> I don't think it's a matter of Oracle since I've tested many query works
>> simultaneously with multiple windows.
>> 
>> I suspect RODBC's stability with multiple windows. 
> 
> That would be a bug, so you should put together a simple example to 
> reproduce it.  Avoid Rcmdr, try to just use Rgui and RODBC.  The big 
> problem will be the database:  if your example requires Oracle, only 
> someone with Oracle will be able to debug it, so try to put together a 
> generic example that works with any database with an ODBC driver.
> 
> I still suspect Oracle here (it did say "internal error" after all), so 
> please do demonstrate it on some other database before you conclude it's 
> an RODBC problem.
> 
> Duncan Murdoch
> 
> ______________________________________________
> R-help at r-project.org 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.
> 
> 

-- 
View this message in context: http://www.nabble.com/RODBC-connection-die---using-more-than-1-Rgui-Rcmdr-tp21301543p21322836.html
Sent from the R help mailing list archive at Nabble.com.