[R-sig-DB] Intermittent connectivity issues for RJDBC / Oracle

[Denis Mukhin]

David,

Have you considered using ROracle instead? It is also DBI based. ROracle uses native interfaces (OCI) so it should be much faster (although I have not run any comparisons). It is also actively maintained by Oracle so if you have any problems/concerns we should be able to address them fairly quickly.

Denis

-----Original Message-----
From: David [mailto:david using serendipityscience.com] 
Sent: Thursday, October 04, 2012 4:45 PM
To: r-sig-db using r-project.org
Subject: [R-sig-DB] Intermittent connectivity issues for RJDBC / Oracle

I will forward more details like versions of xyz when i can, but any thoughts on what I can focus on to debug the problem described below?

Thank you, David
---------- Forwarded message ----------
From: "dh_ss" <david using serendipityscience.com>
Date: Oct 4, 2012 1:29 AM
Subject: [R] Intermittent connectivity issues for JDBC / Oracle
To: <r-help using r-project.org>

I have been experiencing issues with an R script hanging when connecting to an Oracle database.  To help debug, I found a function here in the nabble forum and made a small test script:

  #!/usr/bin/Rscript --no-restore --no-save --no-init-file
  library("RJDBC")
  drv<-JDBC("oracle.jdbc.OracleDriver","/home/oracle/lib/ojdbc6.jar", "'")

  dbCheck = function(url, user='', pwd='') {
    j = .jcall("java/sql/DriverManager", "Ljava/sql/Connection;",
  "getConnection", url, user, pwd, check=FALSE)
    x = .jgetEx()
    .jcheck()
    x
  }
  dbCheck("jdbc:oracle:thin:@192.168.3.10:1521:psrndpt1","myuser","mypass")


When I run the file - sometimes it works, returning NULL, and sometimes it hangs for between 1 and 2.5 minutes before throwing an error.  The database is new and stable, on the same network - with no connection issues from any other clients.  This machine isn't currently doing anything else except for running this script.  Output from running it several times looks like this:

R using ss-dev:~/scripts$ ./dbCheck.R
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
NULL
R using ss-dev:~/scripts$ ./dbCheck.R
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
NULL
R using ss-dev:~/scripts$ ./dbCheck.R
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
NULL
R using ss-dev:~/scripts$ ./dbCheck.R
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
NULL
R using ss-dev:~/scripts$ ./dbCheck.R
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
NULL
R using ss-dev:~/scripts$ ./dbCheck.R
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
NULL
R using ss-dev:~/scripts$ ./dbCheck.R
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
Error in .jcall("java/sql/DriverManager", "Ljava/sql/Connection;", "getConnection",  :
  ignoring SIGPIPE signal
Calls: dbCheck -> .jcall -> .External
Execution halted
R using ss-dev:~/scripts$ ./dbCheck.R
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
Error in .jcall("java/sql/DriverManager", "Ljava/sql/Connection;", "getConnection",  :
  ignoring SIGPIPE signal
Calls: dbCheck -> .jcall -> .External
Execution halted
R using ss-dev:~/scripts$ ./dbCheck.R
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
NULL
R using ss-dev:~/scripts$ ./dbCheck.R
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
Error in .jcall("java/sql/DriverManager", "Ljava/sql/Connection;", "getConnection",  :
  ignoring SIGPIPE signal
Calls: dbCheck -> .jcall -> .External
Execution halted


The times that it returns NULL, it returns immediately.  When it throws the error, it is a varying amount of time between roughly 1 and 2.5 minutes.
Both machines are not busy at all.

Any ideas would greatly appreciated.  Thanks.



--
View this message in context:
http://r.789695.n4.nabble.com/Intermittent-connectivity-issues-for-JDBC-Oracle-tp4644960.html
Sent from the R help mailing list archive at Nabble.com.

______________________________________________
R-help using 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.

	[[alternative HTML version deleted]]

_______________________________________________
R-sig-DB mailing list -- R Special Interest Group R-sig-DB using r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db