OHDSI Home | Forums | Wiki | Github

Problem connecting to Postgres database using Database Connector

Developers
#1

I’ve been having some trouble connecting my postgres db to Achilles, and tracked down my woes to the OHDSI database connector.

I’m currently using R 3.2.0 and Java 1.8.0_45
I’ve followed the instructions listed on the github page. When I execute:

connectionDetails <- createConnectionDetails(dbms=“postgresql”, server=“localhost/odhsi”, port=“5432”, user=“snkasthu”, password=“duh”, schema=“odhsi”)
conn <- connect(connectionDetails)

I get:

Connecting using PostgreSQL driver

However, when I try to execute a command, such as:

dbGetQueryPostgreSql(conn, query = “select * from person”)

I get:

Error in rJava::.jcall(s, “Ljava/sql/ResultSet;”, “executeQuery”, as.character(query)[1]) :
org.postgresql.util.PSQLException: ERROR: relation “person” does not exist
Position: 15

The full error report reads as:

DBMS:
postgresql

Error:
org.postgresql.util.PSQLException: ERROR: relation “person” does not exist
Position: 22

SQL:
SELECT COUNT(*) FROM person

R version:
R version 3.2.0 (2015-04-16)

Platform:
x86_64-apple-darwin13.4.0

Attached base packages:

  • stats
  • graphics
  • grDevices
  • utils
  • datasets
  • methods
  • base

Other attached packages:

  • Achilles (1.0)
  • RPostgreSQL (0.4)
  • rjson (0.2.15)
  • DatabaseConnector (1.2.1)
  • RJDBC (0.2-5)
  • rJava (0.9-6)
  • DBI (0.3.1)
  • SqlRender (1.1.2)
  • devtools (1.8.0)
#2

Just to make sure: both your database and your CDM schema within that database are called ‘ohdsi’?

I found that Postgres has some problems with uppercase in schema names. So if for example your schema name is ‘OHDSI’, then calling SET SEARCH_PATH TO ohdsi; (PostgreSQL equivalent of USE ohdsi;) will not throw an error, but will also not change the search path, leading to errors as yours. Oddly, this is even the case if you use SET SEARCH_PATH TO OHDSI; ,so with the correct case.

Best to always use lowercase in schema and table names on Postgres.

#3

Hi Martijn,

Sorry, this may be a n00b response… I may have misunderstood either you or the instructions that were given on the wiki.
What we did was convert our database into a fully compliant postgres CDM schema. So basically, We don’t have a database and schema as two separate entities - the CDM schema (named ODHSI) contains the 39 tables with our data. Um… isn’t this what we were supposed to do? :frowning:

#4

On PostgreSQL you will always have both a database and a schema, even if you’re not aware of it. Below a screenshot of my Postgres environment, showing the ohdsi database with several schemas including the cdm4_sim schema.

And you’ll really need to rename the CDM schema to ‘ohdsi’ (lowercase), or else PostgreSQL will not work properly.

#5

Hi @schuemie,

Thank you! One lives and learns :smile:

Looking at my own environment, I found the ohdsi database (actually, I had to rename it from odhsi to ohdsi). However, I had just one schema named ‘public’, which I took to be the cdm schema.
And so, I changed my command to

connectionDetails ← createConnectionDetails(dbms=“postgresql”, server=“localhost/ohdsi”, port=“5432”, user=“snkasthu”, password=“pswrd”, schema=“public”)

And it worked!
Next, I tried to execute

achillesResults ← achilles(connectionDetails, “cdm4_inst”, “results”)

Now, cdm4_inst is supposed to be the CDM data and results are supposed to be the target results . And so, I used the following command -

achillesResults ← achilles(connectionDetails, “ohdsi”, “ohdsi”)

Because my CDM data lives in the ohdsi table, and I wanted the results to go into it too.

However, I seem to be getting:

| | 0%Error executing SQL: Error in .local(conn, statement, …): execute JDBC update query failed in dbSendUpdate (ERROR: relation “ohdsi.care_site” does not exist
Position: 279)

I tried changing this to point the results into a brand new table, but the same error still persists.

Am I specifying these firleds correctly? something seems to be amiss :frowning:

#6

The different database platforms have different definitions for ‘database’ and ‘schema’, making things a bit complicated. In your case (PostgreSQL), the cdmDatabaseSchema parameter of the achilles function should contain the schema name. So I think this should work for you:

achillesResults <- achilles(connectionDetails, "public", "public")
#7

Oh, thank you!

So I did try that. This time, i’m faced with:

Executing multiple queries. This could take a while
| | 0%Error executing SQL: Error in .local(conn, statement, …): execute JDBC update query failed in dbSendUpdate (ERROR: column “organization_id” does not exist
Position: 110)

So I took a look at the public schema. It has the 39 tables that it got from the CDM. However, I note that the care_site table doesn’t contain an organization_id.

I looked up [github]/OHDSI/CommonDataModel/blob/master/PostgreSQL/OMOP%20CDM%20ddl%20-%20PostgreSQL.sql, and sure enough, this script doesn’t contain organization_id either.

Am I trying to use the wrong version of Achillies? My error report goes:

DBMS:
postgresql

Error:
execute JDBC update query failed in dbSendUpdate (ERROR: column “organization_id” does not exist
Position: 110)

SQL:
insert into TableCheck (tablename)
select ‘care_site’
from (
SELECT
care_site_id,
location_id,
organization_id,
place_of_service_concept_id,
care_site_source_value,
place_of_service_source_value,
row_number() over (order by care_site_id) rn
FROM
public.care_site
) CARE_SITE
WHERE rn = 1

R version:
R version 3.2.0 (2015-04-16)

Platform:
x86_64-apple-darwin13.4.0

Attached base packages:

  • stats
  • graphics
  • grDevices
  • utils
  • datasets
  • methods
  • base

Other attached packages:

  • Achilles (1.0)
  • rjson (0.2.15)
  • DatabaseConnector (1.2.1)
  • RJDBC (0.2-5)
  • rJava (0.9-6)
  • DBI (0.3.1)
  • SqlRender (1.1.2)
  • devtools (1.8.0)
#8

I suspect you may be running ACHILLES for CDMv4, whereas you’re working
with CDMv5. When you call ACHILLES, you can specify a parameter to be v5,
which should clear up this issue.

#9

Hi @Patrick_Ryan, thank you! :smile:
It seems that the documentation is not very clear on this part, and I cant seem to find the command that you’re speaking of. Is it something that goes in the library(Achilies) command, or the call to achillesResults ← achilles(connectionDetails, “public”, “public”) ?

#10

Suranga,

Here is my command history:

Create Postgress Connection:
pgConnectionDetails <- createConnectionDetails(dbms=“postgresql”, user=“wstephens”,password="*****", server=“localhost/cdmv5”, port=“5432”, schema=“public”)

Run Achilles in CDM v5 mode
achillesResults <- achilles (pgConnectionDetails,“public”, “public”, “”, “”, TRUE, 5, “5”, TRUE)

Bill

#11

Thank you @wstephens!

I edited your command to match my database as follows,

achillesResults <- achilles (connectionDetails,“public”, “public”, “”, “”, TRUE, 5,“5”, TRUE)
However, this gave me the following error:
Error in achilles(connectionDetails, “public”, “public”, “”, “”, TRUE, :
Error: Invalid CDM Version number, use 4 or 5

And so, I edited it to specifically mention the parameters as follows -

achillesResults <- achilles (connectionDetails,cdmDatabaseSchema=“public”, resultsDatabaseSchema=“public”, sourceName="", “”, TRUE, smallcellcount=5, cdmVersion=“5”, TRUE)

This worked, and gave me:

Connecting using PostgreSQL driver
Executing multiple queries. This could take a while
|======================================================================| 100%
Analysis took 0.28 secs
Done. Achilles results can now be found in public
Executing Achilles Heel. This could take a while
|======================================================================| 100%
Analysis took 0.0509 secs
Done. Achilles Heel results can now be found in public

It also created several new tables (achilles_analysis, acillles_heel_results etc.) in the public schema.
Now the problem is, these tables are empty! could it be because I specify smallcellcount=5?

#12

Yes, I would expect that is the reason (the sub-second execution time suggests the amount of data is small, leading to low cell counts).

You can try setting it to 1 and see what happens

#13

Thank you for helping me through this, @schuemie and @wstephens :sunny:
I tried using:

achillesResults ← achilles (connectionDetails,cdmDatabaseSchema=“public”, resultsDatabaseSchema=“public”, sourceName="", “”, TRUE, smallcellcount=1, cdmVersion=“5”, TRUE)

Again, this executed in milliseconds, and yet, the database lies empty. So apparently it didn’t work. There are no apparent errors.
So i’m wondering, my person and observation_period tables do contain data ( > 5000 rows). Could it be because the other CDM tables are empty? or perhaps because I only executed the CDM create tables script, and not the scripts for constrained nor indexes?

I must confess that i’m rather clueless here :frowning:

#14

Maybe the first parameter for which you do not specify the name is assumed to be the analysisIds parameter, telling Achilles to nothing. Could you try:

achillesResults <- achilles (connectionDetails, cdmDatabaseSchema="public", resultsDatabaseSchema="public", sourceName="", smallcellcount=1, cdmVersion="5")
#15

Quick update: while working on this, I discovered and reported: https://github.com/OHDSI/Vocabulary-v5.0/issues/51

#16

It is highly recommended that you do not run the code in the Vocabulary repository yourself. Instead, you can download the vocabulary from the Athena website.

#17

Hi @schuemie, sorry, I am using the downloaded vocab :blush:
I ran into a problem using the cpt4.jar to get the cpt4 codes, which is why I reported an issue under the github page.

#18

@Suranga

I have to agree with @schuemie here. CPT4 is included in the standard conceptual terminologies available from Athena. If your are using this downloaded data set you should not have to download CTP separately.

Bill

#19

Oh apologies, I just re-read the email that I received with the link to download the vocab.
Apparently, CPT-4 comes with the vocab, but we need to use the cpt4.jar to download the descriptions for each.

The email I received reads:
"OHDSI does not have a distribution license to ship CPT-4 codes together with the descriptions. Therefore, we provide you with a utility that downloads the descriptions separately and merges them together with everything else. After unpacking, simply open a command line in the directory you unpacked all the files into and run “java -jar cpt4.jar”

So yes, you’re right, sorry :smile:

#20

@suranga:

Exactly. Unfortunately, we need to do this silly extra little workaround. We tried hard to point that out to the vendor, but they have their reasons why it has to be that way.

t