[R] remote connection to an Oracle database - using RODBC - RMySQL..?

[Marc Schwartz]

On Nov 21, 2012, at 1:52 PM, Raffaello Vardavas <r_vardavas at hotmail.com> wrote:

> 
> Dear users,
> 
> I can access an database oracle database using sql developer. This was done by importing an xml file that contains the login details - username, password and specifies that it uses the KERBEROS_AUTHENTICATION.
> 
> I'm trying to connect R -  so that it can access this database - do sql queries and convert the resulting tables into dataframes. 
> 
> I am a novice in SQL and database access - but a friend provided me with the following approach:
> 
> 
> library(DBI)
> library(RMySQL)
> drvr<-dbDriver("MySQL") #Or another driver, say from the RODBC package?
> acon<-dbConnect(drvr, user="ENTER_USERID", dbname="ENTER_NAME", host="ENTER_HOST",
>                port=1521,password=NULL) #password maybe non-null?
> cmds<-dbSendQuery(acon,statement="YOUR SQL QUERY HERE")
> yourdata<-fetch(cmds, n=-1) #Collects all rows and columns of data requested query.
> 
> I have provided this info changing the relevant info in the dbConnect command and provided the password. However this doesn't work. I suspect because in this command there is not specification of the encryption of the password (i.e., KERBEROS_AUTHENTICATION)
> 
> 
> When I look at the details of the connection in SQL developer - what is specified is the follow:
> 
> connection name, username, password (that I cannot see), hostname, port and the SID.
> 
> Note that although the password here cannot be seen - 
> I believe it is computed by the longer password displaced the the xml file I use to set up the connection with sql developer using the KERBEROS_AUTHENTICATION.
> 
> Any ideas on how to proceed.
> 
> Please help.
> 
> Thank you.
> 
> Raff.


Several comments:

1. Future posts on this subject should be made to R-SIG-DB, not here. More info:

  https://stat.ethz.ch/mailman/listinfo/r-sig-db

2. Why would you expect to use an R package and driver for MySQL when attempting to access an Oracle server?

3. There is a good starting point on this subject generally in the R Data Import/Export manual:

  http://cran.r-project.org/doc/manuals/r-release/R-data.html#Relational-databases

4. I would recommend using RODBC, which is what I use. You will of course need to have an ODBC driver for Oracle installed on your system and properly configured. You may need to get that from Oracle or other parties depending upon your OS which is unstated here. You may also need to get assistance with that process from your SysAdmin or DBAdmin.

5. If you use RODBC, there is additional, quite good information in the package vignette, which is accessible by using:

  vignette("RODBC")

post package installation.

6. I don't have any experience using Kerberos authentication on my Oracle server here, so you may have to follow up on the R-SIG-DB list on that point. A search of the archives did not reveal anything material on that point.

7. Alternatives to RODBC would include ROracle and RJDBC via CRAN.

Regards,

Marc Schwartz