Postgres SQL code not rendering properly - for Hermes

[jmbanda]

Greetings,

I have connected my PostgreSQL server via the LocalAPI section of the Configurations option and I am able to see my Local API (and the Public API) vocab versions under the gear icon in Hermes. As I type something to search, I get the following error

at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577) at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1125) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515) at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1059) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:215) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97) at org.eclipse.jetty.server.Server.handle(Server.java:497) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:248) at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:620) at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:540) at java.lang.Thread.run(Thread.java:745) Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select CONCEPT_ID, CONCEPT_NAME, ISNULL(STANDARD_CONCEPT,'N') STANDARD_CONCEPT, ISNULL(INVALID_REASON,'V') INVALID_REASON, CONCEPT_CODE, CONCEPT_CLASS_ID, DOMAIN_ID, VOCABULARY_ID from ohdsiv5.CONCEPT where (LOWER(CONCEPT_NAME) LIKE '%feet%' or CONCEPT_CODE LIKE '%feet%')

order by CONCEPT_NAME ASC ]; nested exception is org.postgresql.util.PSQLException: ERROR: function isnull(character varying, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 34 at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:416) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:471) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:481) at org.ohdsi.webapi.service.VocabularyService.executeSearch(VocabularyService.java:118) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) 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:151) at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:172) at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:195) at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:104) at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:384) at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:342) at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:101) at org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:271) at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271) at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267) at org.glassfish.jersey.internal.Errors.process(Errors.java:315) at org.glassfish.jersey.internal.Errors.process(Errors.java:297) at org.glassfish.jersey.internal.Errors.process(Errors.java:267) at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:297) at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:254) at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1030) at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:377) ... 38 more Caused by: org.postgresql.util.PSQLException: ERROR: function isnull(character varying, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 34

Seems to me the error is happening because the SQL query passed to my Postgre SQL server has a non-supported function (ISNULL), so I think it is not being properly translated. Querying for the same text string using the Public API works great, but I want to be able to reproduce the results using my own environment. The webAPI is running properly as it is able to query and extract my Vocab5 version properly.

I am running V 1.0 of Olympus and using PostgreSQL version 9.3.

Thanks!

Thanks!

[schuemie]

That's odd! SqlRender should have translated ISNULL to COALESCE as you can see here.

Is the DBMS parameter set up correctly in your local WebApi configuration?

[jmbanda]

Yes, the DBMS parameter is properly set as Olympus can correctly connect to the local WebAPI and extract the vocabulary version I am using. This error is produced after that query successfully runs.

---

Juan M. Banda, Ph.D. Postdoctoral Scholar - Center for Biomedical Informatics Research Stanford University School of Medicine

Website: http://www.jmbanda.com | Google Scholar: http://goo.gl/RiO2jY ResearchGate: http://goo.gl/7C31uc | DBLP: http://goo.gl/BBvmJj LinkedIn: http://goo.gl/h95tnq

On Wed, May 20, 2015 at 7:36 PM, Martijn Schuemie notifications@github.com wrote:

That's odd! SqlRender should have translated ISNULL to COALESCE as you can see here https://github.com/OHDSI/SqlRender/blob/master/inst/csv/replacementPatterns.csv#L92.

Is the DBMS parameter set up correctly in your local WebApi configuration?

— Reply to this email directly or view it on GitHub https://github.com/OHDSI/Olympus/issues/12#issuecomment-104105962.