can I use without .Renviron file?

[dramacloak]

Not in the current version. I agree that it would be a useful enhancement though.

[BenGlicksberg]

Sorry for the late reply @jkylearmstrong. Yes I will add that as a feature request for next iteration. Thank you

[jkylearmstrong]

We have postgre and HIVE back end with OMOP formatted data - I can make connection and query data:

> library(DatabaseConnector)
> connectionDetails <- DatabaseConnector::createConnectionDetails(dbms="postgresql",
+                                                                 user = 'user',
+                                                                 password = 'password',
+                                                                 server = "host/server",
+                                                                 port =5432)
> 
> conn <- connect(connectionDetails)
Connecting using PostgreSQL driver
> 
> querySql(conn,"SELECT COUNT(*) FROM person")
  COUNT
1 22026

I just need a way to pass my connection into functions.

[jkylearmstrong]

I am reviewing the git - zzz.R

when I try with this connection:

con <- DatabaseConnector::connect(dbms = tolower(Sys.getenv("driver")),
                                  server = Sys.getenv("host"),
                                  user = Sys.getenv("username"),
                                  password = Sys.getenv("password"),
                                  port = Sys.getenv("port"))

I can connect to postgre but again I have my .Renviron file set as:

driver="postgresql"
username = 'user'
password = 'password'
host = "host/server"
port =5432

[BenGlicksberg]

Hi @jkylearmstrong I'm sorry I'm a little confused here. What do you mean you need a way to pass your connection into functions? When you load ROMOP, it should already make the connection. Are you not able to able to create an .Renviron file? Are you getting an error message? You shouldn't have to connect to anything once it's correctly loaded.

As stated above, the current version of this package doesn't allow for manual connections, but i will be working on it for the next overhaul with CDM V6.

[jkylearmstrong]

I can create the .Renviron file. I do get an error message when attempting to load package - there are two issues as far as I can tell:

CONNECTION

(1) the connection string in zzz.R is different than how I connect to postgre sql.

VALIDATED CONNECTION STRING

the connection I give in the post above is different than what you have in zzz.R.

When I use the following I can connect to postgre:

connectionDetails <- DatabaseConnector::createConnectionDetails(dbms="postgresql",
                                                                user = 'my_user',
                                                                password = 'my_pass',
                                                                server = "host/db",
                                                                port =5432)

conn <- connect(connectionDetails)

If I try to use the string as it is in zzz.R. eg if I try:

connectionDetails <- DatabaseConnector::createConnectionDetails(dbms="postgresql",
                                                                user = 'my_user',
                                                                password = 'my_pass',
                                                                server = "host",
                                                                schema = "db",
                                                                port =5432)

conn <- connect(connectionDetails)

Then I get error:

Error in connect(dbms = connectionDetails$dbms, user = connectionDetails$user,  : 
  Error: database name not included in server string but is required for PostgreSQL. Please specify server as <host>/<database> 

CDM

(2) We have OMOP V 6 CDM so it should fail checkOMOPtables() in zzz.R however, only general error message is given to me.

[BenGlicksberg]

Thank you for the explanation and apologies again for the difficulties you're having @jkylearmstrong! I should have made this more clear in the documentation regarding formatting for non-MySQL connections. I will also include this in the next update.

Can you please try the following?

host should be host/database, i.e.

host = "localhost/omop"

dbname should be schema, i.e.

dbname = "public"

[jkylearmstrong]

i got an error:

Connecting using PostgreSQL driver
Error in rJava::.jcall(statement, "V", "close") : 
  org.postgresql.util.PSQLException: ERROR: syntax error at end of input
  Position: 21

[BenGlicksberg]

Sorry you're still having issues... I think I might have misread your original comment. Did you mean you are having issues of connecting using DatabaseConnector?

connectionDetails <- DatabaseConnector::createConnectionDetails(dbms="postgresql", user = 'my_user', password = 'my_pass', server = "host", schema = "db", port =5432)

conn <- connect(connectionDetails)

---

From OHDSI/DatabaseConnector page, it says the connectionDetails are as such:

connectionDetails <- createConnectionDetails(dbms="postgresql", server="localhost", user="root", password="blah", schema="cdm_v4")

Could you please try this and verify it works in DatabaseConnector?

connectionDetails <- createConnectionDetails( dbms="postgresql", server="localhost", user="root", password="blah", schema="cdm_v4")

Please use the following details as they indicate:

    • ‘server’. This field contains the host name of the server and
      the database holding the relevant schemas: <host>/<database>

    • ‘schema’. The schema containing the tables.

So in your example schema should NOT be DB. Server should be localhost/DB.