HAPI JPA Sever (1.6) error - SqlExceptionHelper - Incorrect syntax near '@P0'

[Kevin Mayfield]

Have just moved over to SQL Server to conduct some performance testing and started to get the error below. 

Reverted back to MySQL and still had errors (but nothing logged).

Believe it's occurring when I post an Organisation which references an existing organisation. (The parent organisations posted fine but the child orgs didn't)

2016-07-15 09:30:52,187 [io-8080-exec-53] INFO  SearchBuilder                  -  Organization on SearchParameterMap[params={identifier=[[TokenParam[system=urn:fhir.nhs.uk/id/ODSOrganisationCode,value=03X]]]}] in 15ms

2016-07-15 09:30:52,204 [io-8080-exec-53] WARN  SqlExceptionHelper             - SQL Error: 102, SQLState: S0001

2016-07-15 09:30:52,204 [io-8080-exec-53] ERROR SqlExceptionHelper             - Incorrect syntax near '@P0'.

2016-07-15 09:30:52,205 [io-8080-exec-53] ERROR ExceptionHandlingInterceptor   - Failure during REST processing: org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:418)

at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:492)

at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)

at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)

at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)

at com.sun.proxy.$Proxy753.findWithSearchUuid(Unknown Source)

at ca.uhn.fhir.jpa.search.PersistedJpaBundleProvider.doSearchOrEverythingInTransaction(PersistedJpaBundleProvider.java:132)

at ca.uhn.fhir.jpa.search.PersistedJpaBundleProvider$2.doInTransaction(PersistedJpaBundleProvider.java:216)

at ca.uhn.fhir.jpa.search.PersistedJpaBundleProvider$2.doInTransaction(PersistedJpaBundleProvider.java:205)

at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)

at ca.uhn.fhir.jpa.search.PersistedJpaBundleProvider.getResources(PersistedJpaBundleProvider.java:205)

at org.hl7.fhir.dstu3.hapi.rest.server.Dstu3BundleFactory.initializeBundleFromBundleProvider(Dstu3BundleFactory.java:333)

at ca.uhn.fhir.rest.method.BaseResourceReturningMethodBinding.doInvokeServer(BaseResourceReturningMethodBinding.java:379)

at ca.uhn.fhir.rest.method.BaseResourceReturningMethodBinding.invokeServer(BaseResourceReturningMethodBinding.java:248)

at ca.uhn.fhir.rest.server.RestfulServer.handleRequest(RestfulServer.java:681)

at ca.uhn.fhir.rest.server.RestfulServer.doGet(RestfulServer.java:272)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

at org.ebaysf.web.cors.CORSFilter.handleNonCORS(CORSFilter.java:437)

at org.ebaysf.web.cors.CORSFilter.doFilter(CORSFilter.java:172)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)

at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528)

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1099)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476)

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Unknown Source)

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)

at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:492)

at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:191)

at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:78)

at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:102)

at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:92)

at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:482)

at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:460)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)

... 44 more

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)

at org.hibernate.loader.Loader.getResultSet(Loader.java:2115)

at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1898)

at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1874)

at org.hibernate.loader.Loader.doQuery(Loader.java:919)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)

at org.hibernate.loader.Loader.doList(Loader.java:2610)

at org.hibernate.loader.Loader.doList(Loader.java:2593)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2422)

at org.hibernate.loader.Loader.list(Loader.java:2417)

at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)

at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)

at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)

at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1339)

at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)

at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)

at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)

... 58 more

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)

... 74 more

[Kevin Mayfield]

Moved back to a version built yesterday and all is fine.

Seems to have started after a mvn clean 

[Lars Kristian Roland]

I'm having the same problem on HAPI 2.1, running towards MS SQL server (express edition). Did you find out why it happened and has it happened again (as far as I understand from your post, the error disappeared?)

I'm getting:

2016-12-01 11:14:46.338 [scheduledExecutorService-5] ERROR o.h.e.jdbc.spi.SqlExceptionHelper [SqlExceptionHelper.java:131] Incorrect syntax near '@P0'.
2016-12-01 11:14:46.344 [scheduledExecutorService-5] ERROR o.s.s.s.TaskUtils$LoggingErrorHandler [TaskUtils.java:95] Unexpected error occurred in scheduled task.

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:492)

...

There seems to be various stackoverflow responses to a similar problems, so doubt this is a HAPI-specific issue. Seems more likely to be something to do with Hibernete, choice of dialects and use of MS SQL 2014.

[James Agnew]

Hi Lars,

I remember getting this error once on another project and it was because I had used the wrong dialect class (there are several SQL server ones).

Which dialect are you using?

sent from my phone.

--
You received this message because you are subscribed to the Google Groups "HAPI FHIR" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hapi-fhir+unsubscribe@googlegroups.com.
To post to this group, send email to hapi...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/hapi-fhir/1635794f-06f1-415c-91a9-fdf3d7e8d33d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lars Kristian Roland]

Thanks James

It seems you are right. Hibernate seems to only support up to the 2012 dialect. I have the 2016 MS SQL server, and when I turn on 2012-compatibility modus on the server and change to the SQLServer2012Dialect then the error disappears. I'm not sure if I need the 2016-version, but I guess 2012 will do the job fine for me. SQLServerDialect which I was using is apparently older. 

I filed an issue/question at https://github.com/Microsoft/mssql-jdbc/issues/49 and it seems 2012 is the latest according to what they know. 

So, I guess the issue is closed (and it definitely isn't a HAPI-issue).

Thanks for responding.

Best regards,

Lars

--

Lars Kristian Roland

Email: la...@roland.bz

Phone: +47 90733036