OHDSI Home | Forums | Wiki | Github

Database Connector - Issue with Postgresql?

Developers
#1

I am trying to establish a connection to my database using an R package called DatabaseConnector

I am trying the below code

connectionDetails <- DatabaseConnector::createConnectionDetails(dbms = "postgresql", 
                                                                user = "xxx", 
                                                                password = "xxx", 
                                                                server = "localhost/xxx", 
                                                                port = "9763")

When I execute the above command, I get the below error

Error: The folder location pathToDriver = '  ' does not exist. Please set the folder to the location containing the JDBC driver. You can download most drivers using the `downloadJdbcDrivers()` function.

But till few days back, it was working fine and there was no issue with any driver location. I didn’t make any changes to the driver’s location etc. Not sure why this issue is being raised now. Can I seek your help, please?

Therefore, I tried the below options.

a) I downloaded the jar file and keyed in the downloaded location as a path in the pathToDriver argument of the ConnectionDetails function as shown below. However, this doesn’t solve the issue

pathToDriver = "C:\\Users\\test\\Downloads\\postgresql-42.2.19.jar")

b) I set an environment variable as suggested in the doc here. So, I have an environment variable as suggested which is DATABASECONNECTOR_JAR_FOLDER. This points to the jar file that I downloaded (Postgresql-42.2.19.jar)

I get the below error as well

Error in download.file(url = paste0(baseUrl, driverName), destfile = paste(pathToDriver,  : 
  cannot open destfile 'C:\Users\test\Downloads\postgresql-42.2.19.jar/postgresqlV42.2.18.zip', reason 'No such file or directory'
In addition: Warning messages:
1: The folder location 'C:\Users\test\Downloads\postgresql-42.2.19.jar' does not exist. Attempting to create. 
2: In dir.create(pathToDriver, recursive = TRUE) :
  'C:\Users\test\Downloads\postgresql-42.2.19.jar' already exists

c) Later, I tried using the downloadJdbcDrivers() function with pathToDriver updated to my jar file location but it still doesn’t resolve the issue

Can I seek your help please?

#2

Is the folder:

C:\\Users\\test\\Downloads\\postgresql-42.2.19.jar

or:

C:\\Users\\test\\Downloads\\
#3

Hi @Chris_Knoll ,

under downloads folder, i have the jar file…(2nd option)

#4

Ok, can you confirm if you’re using the second option as the value as path to driver? Maybe it thinks your .jar file is the folder, and it can’t open the folder.

1 Like
#5

Yes, that fixed the issue. You are awesome as usual

#6

Hi @Chris_Knoll, I’m having a similar problem with sql server. I see from

that the createConnectionDetails function is getting the pathtoDriver from sys.getenv(“DATABASECONNECTOR_JAR_FOLDER”). However, when I look at the output of sys.getenv, there is no “databaseconnector_jar_folder”. The path to the .dll files are in sys.getenv(“path”).

I see the instruction to set the databaseconnector_jar_folder using the .Renviron file. But not sure how to set this. Could you possibly offer an explanation on how to do this? thanks!

#7

A huge thanks to @aostropolets for walking me through the fix. Going to jot down the steps so I can use this post next time I need to remember how to do this! :slight_smile:

  1. download same version of .jar and .dll files for JDBC driver of choice
  2. place both in chosen local folder
  3. Add that chosen path to user and system variables in environmental variables
  4. use sys.setenv(“DATABASECONNECTOR_JAR_FOLDER” = ‘my local path’) and
  5. sys.setenv(“PATH_TO_AUTH_DLL” = ‘my local path’) in active Rstudio session
  6. Make 4) and 5) permanent by using: usethis::edit_r_environ() in R session and adding the folowing: “PATH_TO_AUTH_DLL” = “my local path”
#8

Thanks @thomasfalconer!

Note that the DLL Thomas mentioned is only needed for those using Windows integrated authentication for SQL Server.

I highly recommend not downloading just any version of the JDBC drivers for PostgreSQL, Oracle, SQL Server, and RedShift, but only using the ones downloaded by the downloadJdbcDrivers() function. We have not tested the HADES packages with any other versions. If you do use other versions, we cannot provide any support, and you’re on your own.

Note that the original issue raised by @Akshay was caused by the fact that the pathToDriver needs to point to a folder, not a file. I’ll make it so the next version of DatabaseConnector throws an informative error message when that happens.

#9

For those using Windows integrated authentication, please download the exact DLL found here (v9.2.0). This corresponds to the correct version of the driver used by DatabaseConnector.

Since a couple of days there is a new version of the SQL Server driver (v9.2.1), and the DLL of that version does not work with JDBC driver v9.2.0. (Thanks to @aostropolets for figuring this out).

#10

I just got a new computer at CUIMC and ran into the same issue 3 years later and referenced this post for the fix…amazing! :slight_smile:

t