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

Steward Heath Care

Office: 781-375-3034

Cell: 781-801-9313

Hong Lily Ju

Software Engineer

hong.ju@steward.org

Steward Heath Care

Office: 781-375-3034

Cell: 781-801-9313