Using SQL Server 2017 Issues

JMidkiff

unread,

Nov 24, 2020, 12:37:15 PM11/24/20

to HAPI FHIR

I am trying to get HAPI FHIR connected to my Microsoft SQL Server, SQL Server 2017, using the JPA starter project (5.2.0).

I have added the dependency to the pom file -

<groupId>com.microsoft.sqlserver</groupId>

<artifactId>mssql-jdbc</artifactId>

</dependency>

and the config to the application.yaml file -

url - jdbc:sqlserver://localhost;databaseName=hapi

dialect - org.hibernate.dialect.SQLServer2012Dialect

driverClassName - com.microsoft.sqlserver.jdbc.SQLServerDriver

And when I start it up it seems to connect fine, but once it gets up and running it throws exceptions.

I started a SQL trace and this is the query it is trying to execute -

exec sp_executesql N'SELECT TOP(@P0) RES_ID FROM ( SELECT t0.RES_ID FROM HFJ_RESOURCE t0 WHERE ((t0.RES_TYPE = @P1) AND (t0.RES_DELETED_AT IS NULL)) ) AS RES_ID',N' @P0 nvarchar(4000),@P1 int',N'SearchParameter',2147483647

This query doesnt return anything (actually errors in sql server when trying to run it from SSMS). It did create the tables, but I am not sure whats going on here. I did successfully get this to work (quite smoothly) in an older version of HAPI (5.1.0). So maybe this is something with this version of HAPI? I even tried an older version of the SQL Server mssql-jdbc dependency, but it still throws the same errors.

Any help would be great. Thanks!

Here is the stack trace -

2020-11-24 11:22:57.002 [main] INFO c.u.f.j.s.BaseSchedulerServiceImpl [BaseSchedulerServiceImpl.java:203] Scheduling local job ca.uhn.fhir.jpa.subscription.match.registry.SubscriptionLoader with interval 00:01:00.000

2020-11-24 11:22:57.025 [main] INFO org.hibernate.dialect.Dialect [Dialect.java:170] HHH000400: Using dialect: org.hibernate.dialect.SQLServer2012Dialect

2020-11-24 11:22:57.079 [main] WARN o.h.e.jdbc.spi.SqlExceptionHelper [SqlExceptionHelper.java:137] SQL Error: 1060, SQLState: S0001

2020-11-24 11:22:57.079 [main] ERROR o.h.e.jdbc.spi.SqlExceptionHelper [SqlExceptionHelper.java:142] The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer.

2020-11-24 11:22:57.083 [main] ERROR c.u.f.j.s.b.sql.SearchQueryExecutor [SearchQueryExecutor.java:136] Failed to create or execute SQL query

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

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

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

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

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

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

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

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

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

at org.hibernate.loader.Loader.scroll(Loader.java:2927)

at org.hibernate.loader.custom.CustomLoader.scroll(CustomLoader.java:383)

at org.hibernate.internal.SessionImpl.scrollCustomQuery(SessionImpl.java:2116)

at org.hibernate.internal.AbstractSharedSessionContract.scroll(AbstractSharedSessionContract.java:1168)

at org.hibernate.query.internal.NativeQueryImpl.doScroll(NativeQueryImpl.java:220)

at org.hibernate.query.internal.AbstractProducedQuery.scroll(AbstractProducedQuery.java:1488)

at org.hibernate.query.internal.AbstractProducedQuery.scroll(AbstractProducedQuery.java:111)

JMidkiff

unread,

Nov 25, 2020, 1:05:08 PM11/25/20

to HAPI FHIR

Has anyone run into this? I could really use some help getting this figured out.

Thanks!

Kevin Seegmiller

unread,

Dec 13, 2020, 12:16:03 AM12/13/20

to HAPI FHIR

I am running into the same issue, has anyone been able to use the JPA Server project with SQL Server 2017?

Thanks,

Kevin

Jens Villadsen

unread,

Dec 13, 2020, 6:06:09 AM12/13/20

to HAPI FHIR

This is most likely because the HAPI FHIR starter project now as of 5.2.0 defaults to a native SQL search strategy instead of the generic one using Hibernate. I'll submit a bug referring this discussion, as James should know that there are dialect issues on SQL 2017. The HAPI FHIR starter project should perhaps also have switch to default to the previous strategy (with a performance penalty) making the project functional again

Jens Villadsen

unread,

Dec 13, 2020, 6:10:52 AM12/13/20

to HAPI FHIR

Posted on https://github.com/hapifhir/hapi-fhir-jpaserver-starter/issues/189

James Agnew

unread,

Dec 13, 2020, 9:25:36 AM12/13/20

to Jens Villadsen, HAPI FHIR

Hi Kevin,

Are you able to try this again on the current HAPI FHIR 5.3.0-SNAPSHOT builds? I believe this issue is solved in current master.

You can try out this branch by checking out this PR: https://github.com/hapifhir/hapi-fhir-jpaserver-starter/pull/190

Cheers,

James

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/hapi-fhir/f3acbb18-2789-4f30-a45d-ac490cbb8052n%40googlegroups.com.

Kevin Seegmiller

unread,

Dec 14, 2020, 8:48:21 PM12/14/20

to HAPI FHIR

Hi James,

I used the 5.3.0-SNAPSHOT with the rel_5.3.0 branch and I get the same error.

2020-12-14 18:44:37.004 [main] WARN o.h.e.jdbc.spi.SqlExceptionHelper [SqlExceptionHelper.java:137] SQL Error: 1060, SQLState: S0001

2020-12-14 18:44:37.005 [main] ERROR o.h.e.jdbc.spi.SqlExceptionHelper [SqlExceptionHelper.java:142] The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer.

2020-12-14 18:44:37.009 [main] ERROR c.u.f.j.s.b.sql.SearchQueryExecutor [SearchQueryExecutor.java:136] Failed to create or execute SQL query