JPA Server on MS SQL Server 2012

Kevin Mayfield

unread,

Jul 22, 2016, 9:45:27 AM7/22/16

to HAPI FHIR

Has anyone got FHIR JPA Server running on MS SQL Server (2012)? My setup works fine against MQSql but want to check performance against SQL Server with a lot of umph.

It mostly works but queries fail..

I've been using these properties:

jdbc.Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbc.url=jdbc:sqlserver://DATABASExxxxxxxx:1433;databaseName=HAPI_FHIR_STU3_DEV

jdbc.username=fhirjpa

jdbc.password=fhirjpa

hibernate.dialect=org.hibernate.dialect.SQLServerDialect

FHIRServerConfigStu3:

@Bean

public DataSource dataSource() {

SimpleDriverDataSource retVal = new SimpleDriverDataSource();

try {

@SuppressWarnings("unchecked")

Class<? extends Driver> driverClass = (Class<? extends Driver>) Class.forName(env.getProperty("jdbc.Driver"));

retVal.setDriverClass(driverClass);

} catch (Exception e) {

// log.error("Error loading driver class", e);

}

retVal.setUrl(env.getProperty("jdbc.url"));

retVal.setUsername(env.getProperty("jdbc.username"));

retVal.setPassword(env.getProperty("jdbc.password"));

return retVal;

}

private Properties jpaProperties() {

Properties extraProperties = new Properties();

extraProperties.put("hibernate.dialect", env.getProperty("hibernate.dialect"));

extraProperties.put("hibernate.format_sql", "true");

extraProperties.put("hibernate.show_sql", "false");

extraProperties.put("hibernate.hbm2ddl.auto", "update");

extraProperties.put("hibernate.jdbc.batch_size", "20");

extraProperties.put("hibernate.cache.use_query_cache", "false");

extraProperties.put("hibernate.cache.use_second_level_cache", "false");

extraProperties.put("hibernate.cache.use_structured_entries", "false");

extraProperties.put("hibernate.cache.use_minimal_puts", "false");

extraProperties.put("hibernate.search.default.directory_provider", "filesystem");

// needed to set properties of this directory sudo chmod -R 777 .

extraProperties.put("hibernate.search.default.indexBase", "/Development/lucene/indexes");

extraProperties.put("hibernate.search.lucene_version", "LUCENE_CURRENT");

extraProperties.put("hibernate.connection.driver.class",

env.getProperty("jdbc.Driver"));

return extraProperties;

}

Kevin Mayfield

unread,

Jul 22, 2016, 9:46:17 AM7/22/16

to HAPI FHIR

Stack trace is on this thread

https://groups.google.com/forum/#!topic/hapi-fhir/VFoNp8LhKXg

James Agnew

unread,

Jul 22, 2016, 10:16:11 AM7/22/16

to Kevin Mayfield, HAPI FHIR

Hi Kevin,

I haven't personally tried on MSSQL, but I've certainly used Hibernate on MSSQL before and its been successful so I don't see why this shouldn't work.

Three thoughts:

* Any chance it's this issue: https://hibernate.atlassian.net/browse/HHH-10032 (on other words, is that the right dialect for the version of MSSQL you're using?)

* Are you on the latest version of HAPI (1.6), which in turn would pull in the latest version of hibernate?

* If nothing else succeeds, can you enable SQL Statement logging? Maybe that would give a hint as to what it's generating..

Cheers,

James

On Fri, Jul 22, 2016 at 9:46 AM, Kevin Mayfield <mayfiel...@gmail.com> wrote:

Stack trace is on this thread

https://groups.google.com/forum/#!topic/hapi-fhir/VFoNp8LhKXg

--
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 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/d7e8c79d-7147-460c-920d-c137ac141cb5%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

Kevin Mayfield

unread,

Jul 22, 2016, 11:17:44 AM7/22/16

to HAPI FHIR

I think I created the database with the wrong dialect setting, should have used org.hibernate.dialect.SQLServer2012Dialect

This created the following error:

2016-07-22 15:41:27,625 [nio-8080-exec-7] WARN SqlExceptionHelper - SQL Error: 11726, SQLState: S0001

2016-07-22 15:41:27,625 [nio-8080-exec-7] ERROR SqlExceptionHelper - Object 'SEQ_SEARCH' is not a sequence object.

Dropping the database and running under the correct dialect appears to have sorted.

Believe the pre 2012 behaviour was to use a table for the SEQ* objects and 2012 these should be a sequence

Many thanks for your assistance.

James Agnew

unread,

Jul 22, 2016, 11:23:53 AM7/22/16

to Kevin Mayfield, HAPI FHIR

Fantastic! Glad to hear it works on that platform too!

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 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/85c60118-dca6-474f-8f38-9420986b842a%40googlegroups.com.

as...@yahoo.com

unread,

Sep 29, 2017, 12:04:23 PM9/29/17

to HAPI FHIR

Hi Kevin - I'm also trying to use SQL Server (2014) with the JPA Server. I copied/pasted your code for the dataSource() function and encountered a nasty looking exception when using the latest versions of the Microsoft JDBC open-source driver (versions 6.1.0 and 6.2.1). Did you use either of these two versions of the JDBC driver or an older one? Many thanks in advance.

2017-09-29 11:57:30.186 [RMI TCP Connection(4)-127.0.0.1] ERROR o.s.web.context.ContextLoader [ContextLoader.java:350] Context initialization failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in ca.uhn.fhir.jpa.demo.FhirServerConfigDstu3: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean]: Factory method 'entityManagerFactory' threw exception; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in ca.uhn.fhir.jpa.demo.FhirServerConfigDstu3: Invalid destruction signature; nested exception is org.springframework.beans.factory.support.BeanDefinitionValidationException: Couldn't find a destroy method named 'close' on bean with name 'dataSource'

at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:599)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1173)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1067)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:513)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483)

at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)

at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)

at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)

at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)

at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1081)

at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:856)

at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542)

at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:443)

at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:325)

at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:107)

at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4643)

at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5105)

at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)

at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:740)

at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:716)

at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:703)

at org.apache.catalina.startup.HostConfig.manageApp(HostConfig.java:1729)

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.apache.tomcat.util.modeler.BaseModelMBean.invoke(BaseModelMBean.java:287)

at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:819)

at com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:801)

at org.apache.catalina.mbeans.MBeanFactory.createStandardContext(MBeanFactory.java:456)

at org.apache.catalina.mbeans.MBeanFactory.createStandardContext(MBeanFactory.java:405)

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.apache.tomcat.util.modeler.BaseModelMBean.invoke(BaseModelMBean.java:287)

at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:819)

at com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:801)

at javax.management.remote.rmi.RMIConnectionImpl.doOperation(RMIConnectionImpl.java:1468)

at javax.management.remote.rmi.RMIConnectionImpl.access$300(RMIConnectionImpl.java:76)

at javax.management.remote.rmi.RMIConnectionImpl$PrivilegedOperation.run(RMIConnectionImpl.java:1309)

at javax.management.remote.rmi.RMIConnectionImpl.doPrivilegedOperation(RMIConnectionImpl.java:1401)

at javax.management.remote.rmi.RMIConnectionImpl.invoke(RMIConnectionImpl.java:829)

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 sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:346)

at sun.rmi.transport.Transport$1.run(Transport.java:200)

at sun.rmi.transport.Transport$1.run(Transport.java:197)

at java.security.AccessController.doPrivileged(Native Method)

at sun.rmi.transport.Transport.serviceCall(Transport.java:196)

at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568)

at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826)

at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:683)

at java.security.AccessController.doPrivileged(Native Method)

at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

at java.lang.Thread.run(Thread.java:745)

Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean]: Factory method 'entityManagerFactory' threw exception; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in ca.uhn.fhir.jpa.demo.FhirServerConfigDstu3: Invalid destruction signature; nested exception is org.springframework.beans.factory.support.BeanDefinitionValidationException: Couldn't find a destroy method named 'close' on bean with name 'dataSource'

at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:189)

at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:588)

... 59 common frames omitted

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in ca.uhn.fhir.jpa.demo.FhirServerConfigDstu3: Invalid destruction signature; nested exception is org.springframework.beans.factory.support.BeanDefinitionValidationException: Couldn't find a destroy method named 'close' on bean with name 'dataSource'

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:601)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483)

at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)

at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)

at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)

at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)

as...@yahoo.com

unread,

Sep 29, 2017, 12:41:05 PM9/29/17

to HAPI FHIR

I was able to work through the Invalid destruction signature exception above by removing the destroyMethod reference in the @Bean annotation:

@Bean(destroyMethod = "close")
public DataSource dataSource() {

I'm thinking that the Microsoft JDBC driver doesn't have a "close" or similar function that Spring should invoke upon destruction.

I'm now encountering the following exception - appears that the JDBC connection returned is null at the following code segment in Hibernate's DdlTransactionIsolatorNonJtaImpl class:

public Connection getIsolatedConnection() {
  if (this.jdbcConnection == null) {
    try {
      this.jdbcConnection = this.jdbcContext.getJdbcConnectionAccess().obtainConnection();

      try {
        if (!this.jdbcConnection.getAutoCommit()) {

2017-09-29 12:32:37.197 [RMI TCP Connection(5)-127.0.0.1] ERROR o.s.web.context.ContextLoader [ContextLoader.java:350] Context initialization failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in ca.uhn.fhir.jpa.demo.FhirServerConfigDstu3: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: HAPI_PU] Unable to build Hibernate SessionFactory

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1628)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555)

Caused by: javax.persistence.PersistenceException: [PersistenceUnit: HAPI_PU] Unable to build Hibernate SessionFactory

at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:967)

at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:892)

at org.hibernate.jpa.HibernatePersistenceProvider.createContainerEntityManagerFactory(HibernatePersistenceProvider.java:151)

at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:353)

at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:370)

at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:359)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624)

... 57 common frames omitted

Caused by: java.lang.NullPointerException: null

at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:46)

at org.hibernate.tool.schema.internal.exec.ImprovedExtractionContextImpl.getJdbcConnection(ImprovedExtractionContextImpl.java:60)

at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:40)

at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:65)

at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.<init>(DatabaseInformationImpl.java:59)

at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:132)

at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:92)

at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:183)

at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)

at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:309)

at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:445)

at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:889)

... 63 common frames omitted

Kevin Mayfield

unread,

Sep 29, 2017, 1:26:41 PM9/29/17

to as...@yahoo.com, hapi...@googlegroups.com

I think that’s what I did.

Have you checked your connection string works?

Sent from my iPhone

--
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 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/1a93f1c6-337e-43bb-aa2b-3f02d8e0f019%40googlegroups.com.

Reply all

Reply to author

Forward