Error in running Achilles

Implementers
1

Hi,

I am trying to run Achilles analysis and kept getting the following error:
org.postgresql.util.PSQLException: ERROR: failed to find conversion function from unknown to text
An error report has been created at /usr/errorReport.txt

Below is part of the error report:
DBMS:
postgresql

Error:
org.postgresql.util.PSQLException: ERROR: failed to find conversion function from unknown to text

SQL:
CREATE TEMP TABLE s_tmpheel_25
AS
SELECT
analysis_id, stratum_1, stratum_2, sum(count_value) as statistic_value, measure_id
FROM(
–100000+analysis_id,
select
NULL as analysis_id,
stratum_2 as stratum_1,
cast(null as varchar(255)) as stratum_2,
count_value,
CAST(concat(‘ach_’,CAST(analysis_id as VARCHAR), ‘:GlobalCnt’) as varchar(255)) as measure_id
from result_cdm_synpuf_5_2_2.ACHILLES_results
where analysis_id in (1805,705,605,805,405)
) Q
group by analysis_id, stratum_1, stratum_2, measure_id

R version:
R version 3.5.0 (2018-04-23)

Platform:
x86_64-redhat-linux-gnu

Here is the command that I used to run Achilles:
achilles(connectionDetails,
cdmDatabaseSchema = “cdm”,
resultsDatabaseSchema=“result”,
vocabDatabaseSchema = “cdm”,
sourceName = “SUNPUF_1K”,
cdmVersion = “5.2.2”,
runHeel = TRUE,
runCostAnalysis = FALSE)

Does anybody know what went wrong? Thanks in advance!

2

I am still not able to fix this issue :sob: . Could someone help me to take a look?

3

Thank you for bringing this to our attention. This seems to be related to an existing issues but I would glad to help.

4

Thank you for your reply. It was able to create 3 tables, achilles_analysis, achilles_results and achilles_results_dist, before raising this error. I am running this analysis in a CentOS environment, but I didn’t encounter this error when I ran it in a Windows environment a while ago. Not sure what went wrong. I’d look forward to your update. Thank you so much!

5

We opened an issue in the GitHub project. Please add any additional information there.

6

Thank you! It seems the bug has been fixed. How can I update the code? Should I uninstall the old Achilles and reinstall it by running the install_github(“OHDSI/Achilles”) command?

7

@cli001 yes, that should work

8

@t_abdul_basser I reinstalled Achilles 1.6.1 and ran the analysis again. This time I ran into another error:
DBMS:
postgresql

Error:
org.postgresql.util.PSQLException: ERROR: syntax error at or near “.” Position: 22

SQL:
DROP TABLE IF EXISTS .tmpach_ch_condition

R version:
R version 3.5.0 (2018-04-23)

Platform:
x86_64-redhat-linux-gnu

Attached base packages:

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

Other attached packages:

  • Achilles (1.6.1)
  • OhdsiRTools (1.5.5)
  • httr (1.3.1)
  • openxlsx (4.1.0)
  • rjson (0.2.20)
  • DatabaseConnector (2.2.0)
  • SqlRender (1.5.2)
  • devtools (1.13.6)

Could you please take a look again? Many thanks!

9

It look as though a qualifier is missing. Did you add relevant qualifiers (e.g. CDM Schema) in function invocation?

10

Thanks for your reply. All I did was running the following command, and I don’t find a place that needs a qualifier:
achilles(connectionDetails,
cdmDatabaseSchema = “cdm”,
resultsDatabaseSchema=“result”,
vocabDatabaseSchema = “cdm”,
sourceName = “SUNPUF_1K”,
cdmVersion = “5.2.2”,
runHeel = TRUE,
runCostAnalysis = FALSE)

And I found that this command also generated a table called “s_tmpach_1832” in my cdm schema. Is this intended?
Thanks again.

11

By “qualifiers” I meant the schema names (e.g. resultsDatabaseSchema). Obviously .tmpach_ch_condition is missing a schema, so I thought that schema might be missing from the function invocation. It appears that this is not the case, so this may just be a bug in the query. Can you open an issue here and include the same information. We will address it.

In multithreaded mode, achilles can create temporary tables. Based on your response however you meant to run this in single threaded mode and therefore this is probably unexpected behavior. Again please open and issue.

12

I opened an issue for it. #323