Running DDL scripts in the CDM source’s results schema

[jcabrerazuniga (Jose Cabrera Zuniga)]

I am having the problem described at:

ERROR: relation "results.achilles_results" does not exist

Hi @Chris_Knoll, Thank you for the help and explanation, as always. I solved the problem, and let me share what I did here, so if anyone has the same issue, at least this is one thing she/he can try. This error message, org.postgresql.util.PSQLException: ERROR: relation "results.achilles_results" does not exist is actually due to some other issues, which prevent it from being created successfully, and once we fix those other issues, the problem will go away and results.achilles_results tabl…

where it talks about running:

http://localhost:8089/WebAPI/ddl/results?dialect=postgresql&schema=results&vocabSchema=cdm&tempSchema=temp&initConceptHierarchy=true

In my case, the server name is: https://hsc-ctsc-atlas.health.unm.edu/
where:

Source Daimons

Results atlas_results
Temp atlas_temp
Vocabulary mdcr2003_2016
CDM mdcr2003_2016
CEM
CEMResults

Should I run:

https://hsc-ctsc-atlas.health.unm.edu//WebAPI/ddl/results?dialect=postgresql&schema=results&vocabSchema=mdcr2003_2016&tempSchema=atlas_temp&initConceptHierarchy=true

I also got this response after trying this link on my server:

{“payload”:{“cause”:null,“stackTrace”:[],“response”:{“context”:{“headers”:{},“entity”:null,“entityType”:null,“entityAnnotations”:[],“entityStream”:{“closed”:false,“committed”:false},“mediaType”:null,“lastModified”:null,“date”:null,“acceptableLanguages”:[""],“responseCookies”:{},“lengthLong”:-1,“entityClass”:null,“links”:[],“allowedMethods”:[],“requestCookies”:{},“stringHeaders”:{},“entityTag”:null,“acceptableMediaTypes”:[{“type”:"",“subtype”:"*",“parameters”:{},“quality”:1000,“wildcardType”:true,“wildcardSubtype”:true}],“committed”:false,“length”:-1,“language”:null,“location”:null},“status”:404,“entity”:null,“mediaType”:null,“lastModified”:null,“date”:null,“metadata”:{},“links”:[],“allowedMethods”:[],“statusInfo”:“NOT_FOUND”,“stringHeaders”:{},“entityTag”:null,“cookies”:{},“length”:-1,“language”:null,“location”:null,“headers”:{}},“message”:“HTTP 404 Not Found”,“localizedMessage”:“HTTP 404 Not Found”,“suppressed”:[]},“headers”:{“id”:“c9b19c3c-846a-d749-bfe8-f52131375f02”,“timestamp”:1627939529210}}

I want to be sure before “ruining my database”
Thanks
Jose

[Chris_Knoll]

So I used the public atlas demo site to post the URL and it was working: http://api.ohdsi.org:8080/WebAPI/ddl/results?dialect=postgresql&schema=results&vocabSchema=mdcr2003_2016&tempSchema=atlas_temp&initConceptHierarchy=true.

So, the 404 you are getting is possibly that WebAPI can’t be found on the host. Maybe it’s not started, or some other issue. But that endpoint should work correctly on your own service…I noticed that you have a ‘double /’ in front of your WebAPI address, and when I alter the one I gave you with to have a double slash:
http://api.ohdsi.org:8080//WebAPI/ddl/results?dialect=postgresql&schema=results&vocabSchema=mdcr2003_2016&tempSchema=atlas_temp&initConceptHierarchy=true

I get the error you are reporting, so possibly just remove the double / from before WebAPI?

[jcabrerazuniga (Jose Cabrera Zuniga)]

WebApi works as Atlas works. From

https://hsc-ctsc-atlas.health.unm.edu/WebAPI/info

I get:

{“version”:“2.7.8”}

I just corrected that typo you mentioned:

https://hsc-ctsc-atlas.health.unm.edu/WebAPI/ddl/results?dialect=postgresql&schema=results&vocabSchema=mdcr2003_2016&tempSchema=atlas_temp&initConceptHierarchy=true

I got the attached response:
responseddl.pdf (73.0 KB)

However I got the error:

/opt/webapi/tomcat/logs/catalina.out 155211/162K 93%
FROM atlas_results.ACHILLES_analysis aa1
INNER JOIN
atlas_results.achilles_results ar1
ON aa1.analysis_id = ar1.analysis_id
WHERE aa1.analysis_id = 0

UNION

SELECT
aa1.analysis_name AS attribute_name,
cast(ar1.count_value AS VARCHAR) AS attribute_value
FROM atlas_results.ACHILLES_analysis aa1
INNER JOIN
atlas_results.achilles_results ar1
ON aa1.analysis_id = ar1.analysis_id
WHERE aa1.analysis_id = 1
)
ORDER BY attribute_name DESC]; nested exception is org.postgresql.util.PSQLException: ERROR: relation “atlas_results.achilles_analysis” does not exist
Position: 95
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:230)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:654)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:688)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:720)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:770)
at org.ohdsi.webapi.report.CDMResultsAnalysisRunner.getDashboard(CDMResultsAnalysisRunner.java:50)
at org.ohdsi.webapi.service.CDMResultsService.getRawDashboard(CDMResultsService.java:191)
at org.ohdsi.webapi.service.CDMResultsService.getDashboard(CDMResultsService.java:185)
at org.ohdsi.webapi.service.CDMResultsService$$FastClassBySpringCGLIB$$f65c71c1.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:736)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.cache.jcache.interceptor.JCacheInterceptor$1.invoke(JCacheInterceptor.java:52)
at org.springframework.cache.jcache.interceptor.JCacheAspectSupport.invokeOperation(JCacheAspectSupport.java:153)
at org.springframework.cache.jcache.interceptor.JCacheAspectSupport$CacheOperationInvokerAdapter.invoke(JCacheAspectSupport.java:167)
at org.springframework.cache.jcache.interceptor.CacheResultInterceptor.invoke(CacheResultInterceptor.java:62)
at org.springframework.cache.jcache.interceptor.JCacheAspectSupport.execute(JCacheAspectSupport.java:123)
at org.springframework.cache.jcache.interceptor.JCacheAspectSupport.execute(JCacheAspectSupport.java:102)
at org.springframework.cache.jcache.interceptor.JCacheInterceptor.invoke(JCacheInterceptor.java:61)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:671)
at org.ohdsi.webapi.service.CDMResultsService$$EnhancerBySpringCGLIB$$25de9a4f.getDashboard()
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144)

I did search for the table achilles_analysis in the attached pdf but no luck
Thanks

[Chris_Knoll]

Ok, I think I understand

The part of the Atlas app you are using is trying to pull data from the Achilles results tables. These tables aren’t part of WebAPI’s results schema, rather the achilles tables are additional tables that you can load into your results schema in order to view the data under the ‘datasources’ nav in Atlas.

This was described in the other thread that you need to run the Achilles package in order to create the achilles_results tables.

In order to populate the achilles_results tables, you will need to run the Achilles package, and specify the results schema as ‘atlas_results’.

[jcabrerazuniga (Jose Cabrera Zuniga)]

Awesome!!
Thanks