org.postgresql.util.PSQLException: ERROR: syntax error at or near "@"

[tlecarrour-ee]

When I run DQD v2.1.2 against a PostgreSQL CDM, I don't get any .json result file, but 3 error files:

• CONCEPT_plausibleGender_NA_CONDITION_CONCEPT_ID.txt
• CONCEPT_plausibleUnitConceptIds_NA_MEASUREMENT_CONCEPT_ID.txt
• CONCEPT_plausibleGender_NA_PROCEDURE_CONCEPT_ID.txt

The first one contains:

DBMS:
postgresql

Error:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "@"
  Position: 322

SQL:
/*********
CONCEPT LEVEL check:
PLAUSIBLE_GENDER - number of records of a given concept which occur in person with implausible gender for that concept
Parameters used in this template:
cdmDatabaseSchema = omop_cdm
cdmTableName = @cdmTableName
cdmFieldName = CONDITION_CONCEPT_ID
conceptId = 45772671
plausibleGender = Male
**********/
SELECT
  num_violated_rows,
    CASE
        WHEN denominator.num_rows = 0 THEN 0
        ELSE 1.0*num_violated_rows/denominator.num_rows
    END AS pct_violated_rows,
    denominator.num_rows AS num_denominator_rows
FROM
(
    SELECT
      COUNT(*) AS num_violated_rows
    FROM
    (
        /*violatedRowsBegin*/
        SELECT cdmTable.*
        FROM omop_cdm.@cdmTableName cdmTable
            INNER JOIN omop_cdm.person p
            ON cdmTable.person_id = p.person_id
        WHERE cdmTable.CONDITION_CONCEPT_ID = 45772671
          AND p.gender_concept_id <> 8507
        /*violatedRowsEnd*/
    ) violated_rows
) violated_row_count,
(
    SELECT
      COUNT(*) AS num_rows
    FROM omop_cdm.@cdmTableName cdmTable
    WHERE CONDITION_CONCEPT_ID = 45772671
) denominator
;

The log file contains a lot (thousands) of identical messages and ends with:

# …
2023-04-27 09:32:39 [Main thread]   ERROR   DataQualityDashboard    3   [Level: CONCEPT] [Check: plausibleUnitConceptIds] [CDM Table: NA] [CDM Field: MEASUREMENT_CONCEPT_ID] Error executing SQL: org.postgresql.util.PSQLException: ERROR: syntax error at or near "@"   Position: 320 An error report has been created at  /home/ohdsi/output/dqd/errors/CONCEPT_plausibleUnitConceptIds_NA_MEASUREMENT_CONCEPT_ID.txt
2023-04-27 09:32:39 [Main thread]   ERROR   DataQualityDashboard    3   [Level: CONCEPT] [Check: plausibleUnitConceptIds] [CDM Table: NA] [CDM Field: MEASUREMENT_CONCEPT_ID] Error executing SQL: org.postgresql.util.PSQLException: ERROR: syntax error at or near "@"   Position: 320 An error report has been created at  /home/ohdsi/output/dqd/errors/CONCEPT_plausibleUnitConceptIds_NA_MEASUREMENT_CONCEPT_ID.txt
2023-04-27 09:32:39 [Main thread]   ERROR   DataQualityDashboard    3   [Level: CONCEPT] [Check: plausibleUnitConceptIds] [CDM Table: NA] [CDM Field: MEASUREMENT_CONCEPT_ID] Error executing SQL: org.postgresql.util.PSQLException: ERROR: syntax error at or near "@"   Position: 320 An error report has been created at  /home/ohdsi/output/dqd/errors/CONCEPT_plausibleUnitConceptIds_NA_MEASUREMENT_CONCEPT_ID.txt
2023-04-27 09:32:50 [Main thread]   FATAL   DataQualityDashboard    $<- replacement has 0 rows, data has 1

To disable them, I had to remove the 3 lines matching the conceptId from OMOP_CDMv5.4_Concept_Level.csv.

Am I doing something wrong?

[katy-sadowski]

Hi @tlecarrour-ee ! I believe I know the root cause of this and a fix will be shipped in the upcoming v2.2 release: https://github.com/OHDSI/DataQualityDashboard/pull/432. There's an invisible character in some of the threshold csv files that causes these errors on some operating systems.

In the short term, you can try downloading the fixed csv file from our develop branch here and replacing your local one with the new file. Alternatively, if you've made modifications to your csv locally and don't want to overwrite it, you can remove the invisible character yourself. Here are instructions for doing so using VSCode: https://pinter.org/archives/9915. If you don't use VSCode you can Google "remove BOM from csv file" to find lots of other methods :)

[tlecarrour-ee]

Hi @katy-sadowski Thanks for your reply! I've updated the file and started DQD. I'll you keep you posted!

[tlecarrour-ee]

It worked! Thanks @katy-sadowski.

[katy-sadowski]

Updated DQD version should resolve this issue moving forward.