OHDSI Home | Forums | Wiki | Github

Connection Error from RStudio to Redshift database while DQD Execution

Developers
#1

Hi,

I am trying to use the DQD tool using RStudio with Redshift by following the instructions from the link here.

At first I created a connection with the Redshift to see if the JDBC connection works or not. Post connection established, I queried one of the Synthea tables and it worked fine as I could see the results from the SELECT query. Below are the details on how I created the connection and SUCCESSFULLY queried tables:

P.S. I am using this driver “RedshiftJDBC42-no-awssdk-1.2.50.1077.jar”. Please note that the directory does not contain any other Redshift driver hence no conflict can arise for the error encountered.

driver <- JDBC("", "/jdbcdrivers/RedshiftJDBC42-no-awssdk-1.2.50.1077.jar", identifier.quote="`")

conn <- dbConnect(driver,"jdbc:redshift://abccluster-xxxx.zzzz.ap-southeast-1.redshift.amazon.com/<myDBName>","<Username>","<Password>")

However, when I am using the same connection details for the DQD tool, its giving the below error:

Error in rJava::.jcall (jdbcDriver,"Ljava/sql/Connection;","connect", : java.sql.SQLException: [Amazon](100021) Error setting default driver property values)

Just to confirm, I also ran the below command and I am getting the same above error:

connection <- DatabaseConnector::connect(connectionDetails = connectionDetails)

Please help me on how to resolve the issue and if I need to perform any further steps.

Thanks,
Rana

#2

Resolved this issue

#3

I do not see the port defined. Here is an example of my setup connect DQD with RedShift

connectionDetails ← DatabaseConnector::createConnectionDetails
(dbms = “redshift”,
user = “unserName”,
password = “password”,
server = “redShift_instance.us-east-1.redshift.amazonaws.com/databaseName”
port=“5439”,
pathToDriver=“C:/Program Files/Java/jdbc_drivers”
);

test connection

conn = connect(connectionDetails)
querySql(conn, “select count(*) from hvm_cdm5_1p.person”)
disconnect(conn)

#4

Thanks a lot @DTorok. Yes, I have resolved this issue by removing the jdbc:redshift:// and mentioning the correct url of the driver that I was using. This really helped.

t