OHDSI Home | Forums | Wiki | Github

Error seen trying to connect Strategus to Postgres CDM

Developers
#1

I’m working on creating a Strategus study using PostgreSql for my CDM, specifically, the demo_cdm distributed with Broadsea.

It looks like maybe the query isn’t correctly being converted to a PostgreSql flavor somewhere in the code that is eventually called by Strategus::storeConnectionDetails(). I’m guessing this because of the syntax error for “show tables in demo_cdm”

I’m using:
Strategus 0.1.0
DatabaseConnector 6.2.4

This is what I'm using to create the connection:
  rtn <- DatabaseConnector::createConnectionDetails(
    dbms = "postgresql",
    connectionString = "jdbc:postgresql://127.0.0.1:5432/postgres?user=postgres&password=mypass&currentSchema=demo_cdm",
    pathToDriver = "./etc/database/postgres/42.3.3"
  )
  return(rtn)

This is the error message I’m getting:

> # excecute this for each connectionDetails/ConnectionDetailsReference you are going to use
> Strategus::storeConnectionDetails(
+   connectionDetail .... [TRUNCATED] 

> # --- start test of connectionDetails -----------------------------------------------------------
> testConnection <- DatabaseConnector::connect(con .... [TRUNCATED] 
Connecting using PostgreSQL driver

> testConnection 
<DatabaseConnectorConnection> 127.0.0.1
> DatabaseConnector::querySql(testConnection, "show tables in demo_cdm")
Error in `.createErrorReport()`:
! Error executing SQL:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "in"
  Position: 13
An error report has been created at  C:/_YES/workspace/ergasia/errorReportSql.txt

Contents of erroReportSql.txt is shown below:

DBMS:
postgresql

Error:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "in"
  Position: 13

SQL:
show tables in demo_cdm

R version:
R version 4.2.3 (2023-03-15 ucrt)

Platform:
x86_64-w64-mingw32

Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base

Other attached packages:
- R6 (2.5.1)
#2

I’m not familiar with the query you are using in the code above. Can you try the following with your connection:

DatabaseConnector::getTableNames(connection = testConnection, databaseSchema = "demo-cdm")

This will provide you with a list of the tables in the demo-cdm schema which is what I presume your query is attempting to do (and what Strategus will do to verify that your CDM tables exist ahead of running the study).

t