OHDSI Home | Forums | Wiki | Github

Aquilles R connection to Oracle

Developers
#1

Hello all, I am having issues connecting to my OMOP database with R. I am on a Mac computer.

So I do:

connectionDetails<-createConnectionDetails(dbms="oracle", user="my_omop_db", password="my_omop_pass", server="omopserver.ohdsi.org:1521/omop.ohdsi.org")
conn <- connect(connectionDetails)

and I get:
Error in rJava::.jcall(drv@jdrv, “Ljava/sql/Connection;”, “connect”, as.character(url)[1], :
java.sql.SQLRecoverableException: IO Error: Invalid connection string format, a valid format is: “host:port:sid”

However when I do it more “from scratch”:

library(RJDBC)
drv= JDBC("oracle.jdbc.OracleDriver", classPath="/Users/myuser/oracle/instantclient_11_2/ojdbc5.jar", "")
con = dbConnect (drv, "jdbc:oracle:thin:@omopserver.ohdsi.org:1521/omop.ohdsi.org", "my_omop_db", "my_omop_pass")
GetQuery=(con, "select * from MEASUREMENT")

It works fine…

So… what am I missing?

Thanks in advance.

Emanuel Villa

#2

Hi @Emanuel_Villa,

There are two ways to set up the connection:

  1. Specify the JDBC connection string.
  2. Provide the parts needed to build the connection string, like server name, port, etc., and have the package create the connection string for you.

What you’re doing seems to somewhere in between. Based on your examples, I think either this

connectionDetails<-createConnectionDetails(dbms="oracle", user="my_omop_db", password="my_omop_pass", server="omopserver.ohdsi.org", port=1521, schema="omop.ohdsi.org")

or this

connectionDetails<-createConnectionDetails(dbms="oracle", user="my_omop_db", password="my_omop_pass", connectionString="jdbc:oracle:thin:@omopserver.ohdsi.org:1521/omop.ohdsi.org")

should work.

1 Like
#3

Yes… thank you.

Option 2 worked like a charm.

thanks so much for the prompt response.

EV

t