[rules-users] Drools with Microsoft SQL Server

[Hong Ju]

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 at steward.org<mailto:derrick.smith at steward.org>
Steward Heath Care

[Description: cid:image001.png at 01CD0DC1.C0D81BD0]

Office: 781-375-3034
Cell: 781-801-9313



Hong Lily Ju
Software Engineer
hong.ju at steward.org<mailto:derrick.smith at steward.org>
Steward Heath Care

[Description: cid:image001.png at 01CD0DC1.C0D81BD0]

Office: 781-375-3034
Cell: 781-801-9313

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/rules-users/attachments/20140218/a9058ae6/attachment-0001.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 1092 bytes
Desc: image001.jpg
Url : http://lists.jboss.org/pipermail/rules-users/attachments/20140218/a9058ae6/attachment-0001.jpg