[Rd] RJDBC

[Simon Urbanek]

Jose,

On Mar 5, 2007, at 7:39 AM, Jose Sierra wrote:

> I need help.
>
> I'm trying to connect with an Oracle DBMS and MySQL DBMS, I'm using
> RJDBC package.
>
> My code is the next:
>
> library('rJava')
> library('DBI')
> library('RJDBC')
>
> //Mysql
> drv <-
> JDBC("com.mysql.jdbc.Driver","C:\\Temporal\\mysql-connector- 
> java-3.0.9-stable-bin.jar","'")
>
> conn <- dbConnect(drv, "jdbc:mysql://localhost:3306/bd", "user",
> "password")
>
> //Oracle
> drv <-
> JDBC("oracle.jdbc.driver.OracleDriver","C:\\Temporal\ 
> \classes12.jar","'")
> conn <-
> dbConnect 
> (drv,"jdbc:oracle:thin:@192.168.1.70:1521:SDS22","user","password")
>
>
> R always returns for oracle
> "Error en .local(drv, ...) : Unable to connect JDBC to
> jdbc:oracle:thin:@192.168.1.70:1521:SDS22"
> and for mysql
> "Error en .local(drv, ...) : Unable to connect JDBC to
> jdbc:mysql://localhost:3306/bd"
>

It is very likely a problem with your DB connection or  
authentication. I have tested it with MySQL (I don't have Oracle) and  
it worked without problems. Please test it step-by-step to make sure  
that your DB is configured properly.

Unfortunately RJDBC doesn't pass non-fatal SQL errors back to the  
user, so connection problems are not easy to debug, but you can try  
this to show the connection problem:

dbCheck = function(url, user='', pwd='') {
  j = .jcall("java/sql/DriverManager", "Ljava/sql/Connection;",  
"getConnection", url, user, pwd, check=FALSE)
  x = .jgetEx()
  .jcheck()
  x
}

and use it like this:

 > dbCheck("jdbc:mysql://localhost/foo","user","pwd")
[1] "Java-Object{java.sql.SQLException: Access denied for user  
'user'@'localhost' (using password: YES)}"
 >
 > dbCheck("jdbc:mysql://localhost/foo","root","")
[1] "Java-Object{java.sql.SQLException: Unknown database 'foo'}"
 > dbCheck("jdbc:mysql://bar/foo","root","")

I'll see if I can provide some more helpful response in the dbConnect  
for the next release..


> Another question:
> I try to compile ROracle and RMysql for windows but i need Rdll.lib  
> and  it need R.exp.

No, you don't need those files, because R is linked directly. Simply  
follow the instructions for building R packages on Windows.

Cheers,
Simon