1:02 p.m.
Hi,
Has anyone had an experience to configure Drools to use Microsoft SQL server? We have been
using H2, but we want to switch to SQL. This is my persistence unit:
<persistence-unit name="stewardcareH2" transaction-type="JTA">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>jdbc/processInstanceDS</jta-data-source>
<class>org.drools.persistence.info.SessionInfo</class>
<class>org.jbpm.persistence.processinstance.ProcessInstanceInfo</class>
<class>org.drools.persistence.info.WorkItemInfo</class>
<class>org.steward.care.fact.Account</class>
<class>org.steward.care.fact.ObservationGroup</class>
<class>org.steward.care.fact.ObservationParent</class>
<class>org.steward.care.fact.Observation</class>
<class>org.steward.care.fact.ClinicalRuleMatch</class>
<class>org.steward.care.fact.Comorbidity</class>
<class>org.steward.care.fact.OutboundNotice</class>
<class>org.steward.care.fact.Medication</class>
<class>org.steward.care.fact.data.CodedElement</class>
<class>org.steward.care.fact.data.StructuredValue</class>
<class>org.steward.care.fact.data.TriggerMatch</class>
<properties>
<property name="hibernate.dialect"
value="org.hibernate.dialect.SQLServerDialect" />
<property name="hibernate.max_fetch_depth"
value="3" />
<property name="hibernate.hbm2ddl.auto"
value="create-drop" />
<property name="hibernate.show_sql"
value="true" />
<property name="hibernate.connection.autocommit"
value="true" />
<!-- <property
name="hibernate.transaction.manager_lookup_class" value=
"org.hibernate.transaction.BTMTransactionManagerLookup"/> -->
<property name="hibernate.transaction.jta.platform"
value="org.hibernate.service.jta.platform.internal.BitronixJtaPlatform"/>
</properties>
</persistence-unit>
This is how I configure a data pooling:
PoolingDataSource pds = new PoolingDataSource();
// The name must match what's in the persistence.xml!
pds.setUniqueName("jdbc/processInstanceDS");
//
pds.setClassName("com.microsoft.sqlserver.jdbc.SQLServerXADataSource");
// doesn't really matter
pds.setMaxPoolSize(3);
pds.getDriverProperties().put("user", "myuser");
pds.getDriverProperties().put("password",
"mypassword");
pds.getDriverProperties().put("serverName",
"localhost");
pds.getDriverProperties().put("databaseName",
"CAREDatabase");
pds.getDriverProperties().put("URL",
"jdbc:sqlserver://localhost;databaseName=CAREDatabase;IntegratedSecurity=true");
// pds.getDriverProperties().put("selectMethod",
"cursor");
pds.init();
But I get this error:
bitronix.tm.resource.ResourceConfigurationException: cannot create JDBC datasource named
jdbc/processInstanceDS
at bitronix.tm.resource.jdbc.PoolingDataSource.init(PoolingDataSource.java:92)
at
org.steward.care.core.CAREKnowledgeEngine.InitializePoolingDataSource(CAREKnowledgeEngine.java:762)
at
org.steward.care.core.CAREKnowledgeEngine.initializePersistedKnowledgeSession(CAREKnowledgeEngine.java:720)
at
org.steward.care.core.CAREKnowledgeEngine.initializeKnowledgeEngine(CAREKnowledgeEngine.java:662)
at org.steward.care.CAREManager.main(CAREManager.java:50)
Caused by: bitronix.tm.recovery.RecoveryException: failed recovering resource
jdbc/processInstanceDS
at
bitronix.tm.recovery.IncrementalRecoverer.recover(IncrementalRecoverer.java:100)
at bitronix.tm.resource.ResourceRegistrar.register(ResourceRegistrar.java:87)
at
bitronix.tm.resource.jdbc.PoolingDataSource.buildXAPool(PoolingDataSource.java:104)
at bitronix.tm.resource.jdbc.PoolingDataSource.init(PoolingDataSource.java:88)
... 4 more
Caused by: javax.transaction.xa.XAException: The function RECOVER: failed. The status is:
-3. Error: "*** SQLJDBC_XA DTC_ERROR Context: xa_recover, state=1, StatusCode:-3
(0xFFFFFFFD) ***"
at
com.microsoft.sqlserver.jdbc.SQLServerXAResource.DTC_XA_Interface(SQLServerXAResource.java:550)
at
com.microsoft.sqlserver.jdbc.SQLServerXAResource.recover(SQLServerXAResource.java:728)
at bitronix.tm.recovery.RecoveryHelper.recover(RecoveryHelper.java:103)
at bitronix.tm.recovery.RecoveryHelper.recover(RecoveryHelper.java:61)
at bitronix.tm.recovery.IncrementalRecoverer.recover(IncrementalRecoverer.java:63)
... 7 more
If anyone has done this and provides an example, that will be great. Thank you!!!
Hong Lily Ju
Software Engineer
hong.ju@steward.org<mailto:derrick.smith@steward.org>
Steward Heath Care
[Description: cid:image001.png@01CD0DC1.C0D81BD0]
Office: 781-375-3034
Cell: 781-801-9313
Hong Lily Ju
Software Engineer
hong.ju@steward.org<mailto:derrick.smith@steward.org>
Steward Heath Care
[Description: cid:image001.png@01CD0DC1.C0D81BD0]
Office: 781-375-3034
Cell: 781-801-9313
