9 Replies Latest reply on Sep 30, 2011 10:54 AM by Marco Rietveld

    jBPM 5.1 with SQL Server

    Dominique Paquin Newbie

      Hello all,

       

      I have been working on trying to integrate jBPM in out current product we are developping. I have been successful in running hte in-memory version of the project but I now need to store my process states in a data base for later recovery.

       

      I'm using SQL Server 2005

       

      My problem is in seting up the connection. I was able to setup a connection simply using our javax.persistence.EntityManagerFactory (which has a org.apache.commons.dbcp.BasicDataSource in it) and giving it as argument like this (with a JTA transaction manager):

       

       

          env.set(EnvironmentName.ENTITY_MANAGER_FACTORY, this.dataEntityManagerFactory);

          env.set(EnvironmentName.TRANSACTION_MANAGER, new JtaTransactionManager());

       

       

      and later calling

       

       

          JPAKnowledgeService.newStatefulKnowledgeSession(knowledgeBase, null, env);

       

       

      But when I start a process, I have a crash due to a missing TransactionManager.

          ...

          Caused by: org.springframework.transaction.CannotCreateTransactionException: No JTA UserTransaction available - programmatic PlatformTransactionManager.getTransaction usage not supported

                at org.springframework.transaction.jta.JtaTransactionManager.doGetTransaction(JtaTransactionManager.java:770)

                at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:335)

                at org.drools.container.spring.beans.persistence.DroolsSpringTransactionManager.begin(DroolsSpringTransactionManager.java:49)

                at org.drools.persistence.SingleSessionCommandService.<init>(SingleSessionCommandService.java:120)

                ... 65 more

       

       

       

      So I tried to feed it the transaction manager we have already existing in our product, org.springframework.transaction.PlatformTransactionManager,

          env.set(EnvironmentName.TRANSACTION_MANAGER, platformTransactionManager);

       

       

      but the code craches a bit further with:

       

          2011-09-20 09:12:52,409 [525023568@qtp-1417375004-4] [ERROR] [org.drools.persistence.SingleSessionCommandService] - Could not commit session

          java.lang.NullPointerException

                at org.drools.persistence.SingleSessionCommandService.<init>(SingleSessionCommandService.java:125)

                at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

                at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

                at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

                at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

                at org.drools.persistence.jpa.KnowledgeStoreServiceImpl.buildCommanService(KnowledgeStoreServiceImpl.java:116)

                at org.drools.persistence.jpa.KnowledgeStoreServiceImpl.newStatefulKnowledgeSession(KnowledgeStoreServiceImpl.java:54)

                at org.drools.persistence.jpa.JPAKnowledgeService.newStatefulKnowledgeSession(JPAKnowledgeService.java:122)

                ...

          ...

          ...

          Caused by: java.lang.NullPointerException

                at org.drools.persistence.SingleSessionCommandService.<init>(SingleSessionCommandService.java:125)

                ... 65 more

       

      This is the persistence.xml file I use:

       

          <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

       

       

          <persistence version="1.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"

            xmlns:orm="http://java.sun.com/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/persistence">

       

       

            <persistence-unit name="org.jbpm.persistence.jpa" 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>

       

       

              <properties>

                <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>

                <property name="hibernate.max_fetch_depth" value="3"/>

                <property name="hibernate.hbm2ddl.auto" value="update"/>

                <property name="hibernate.show_sql" value="true"/>

                <property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.BTMTransactionManagerLookup"/>

              </properties>

       

       

            </persistence-unit>

       

       

          </persistence>

       

      I obviously can't post my code since this is part of a huge project but if you have specific questions, I can answer them. Help would be appreciated, be it with comments on what I posted above or a Dummy's guide on how to run jBPM with SQL Server

       

      Note that with the above method, my tables were created in my database. I just can't start processes.

        • 1. Re: jBPM 5.1 with SQL Server
          Dominique Paquin Newbie

          While waiting for feedback on the above, I decided to try approach number 2

           

          It will use this persistence.xml file (contrary to the above post, I uncommented the node "jta-data-source" ):

          <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

           

          <persistence version="1.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"

            xmlns:orm="http://java.sun.com/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/persistence">

           

           

            <persistence-unit name="org.jbpm.persistence.jpa" 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>

           

           

              <properties>

                <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>

                <property name="hibernate.max_fetch_depth" value="3"/>

                <property name="hibernate.hbm2ddl.auto" value="update"/>

                <property name="hibernate.show_sql" value="true"/>

                <property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.BTMTransactionManagerLookup"/>

              </properties>

           

           

            </persistence-unit>

           

           

          </persistence>

           

          I try to initiate the database connection like proposed in the jBPM documentation:

           

                  PoolingDataSource ds = new PoolingDataSource();

                  ds.setUniqueName("jdbc/processInstanceDS");

                  ds.setClassName("net.sourceforge.jtds.jdbc.Driver");

                  ds.setMaxPoolSize(3);

                  ds.setAllowLocalTransactions(true);

                  ds.getDriverProperties().setProperty("user", "myUser");

                  ds.getDriverProperties().setProperty("password", "myPassword");

                  ds.getDriverProperties().put("URL", "jdbc:jtds:sqlserver://192.168.117.129:1433;databaseName=myDatabase");

           

           

                  ds.init();

          But when I start everything, the above code crashes with:

           

                  ...

                  Caused by: bitronix.tm.utils.PropertyException: no writeable property 'URL' in class 'net.sourceforge.jtds.jdbc.Driver'

                  at bitronix.tm.utils.PropertyUtils.getSetter(PropertyUtils.java:318)

                  at bitronix.tm.utils.PropertyUtils.setDirectProperty(PropertyUtils.java:217)

                  at bitronix.tm.utils.PropertyUtils.setProperty(PropertyUtils.java:83)

                  at bitronix.tm.resource.common.XAPool.createXAFactory(XAPool.java:314)

                  at bitronix.tm.resource.common.XAPool.<init>(XAPool.java:63)

                  at bitronix.tm.resource.jdbc.PoolingDataSource.buildXAPool(PoolingDataSource.java:85)

                  at bitronix.tm.resource.jdbc.PoolingDataSource.init(PoolingDataSource.java:72)

                  ... 58 more

           

          I looked this up on the net for a while and found that I could defin each parts of the URL in separate components like this (Though I have no clue as to how the connection will be initiaterd since the server type is not provided):

           

                  ...

                  ds.getDriverProperties().setProperty("serverName", "192.168.117.129");

                  ds.getDriverProperties().setProperty("portNumber", "1433");

                  ds.getDriverProperties().setProperty("databaseName", "racm_identity_data");

          In anycase, this also crashes with:

           

                  Caused by: bitronix.tm.utils.PropertyException: no writeable property 'user' in class 'net.sourceforge.jtds.jdbc.Driver'

                  at bitronix.tm.utils.PropertyUtils.getSetter(PropertyUtils.java:318)

                  at bitronix.tm.utils.PropertyUtils.setDirectProperty(PropertyUtils.java:217)

                  at bitronix.tm.utils.PropertyUtils.setProperty(PropertyUtils.java:83)

                  at bitronix.tm.resource.common.XAPool.createXAFactory(XAPool.java:314)

                  at bitronix.tm.resource.common.XAPool.<init>(XAPool.java:63)

                  at bitronix.tm.resource.jdbc.PoolingDataSource.buildXAPool(PoolingDataSource.java:85)

                  at bitronix.tm.resource.jdbc.PoolingDataSource.init(PoolingDataSource.java:72)

                  ... 58 more

           

          So with one technique or the other, I always get countered by some obscure problems time after time

          • 2. Re: jBPM 5.1 with SQL Server
            Mauricio Salatino Master

            did you start the transaction provider? take a look at the docs.. and you will see that we create a PoolingDataSource using bitronix and then we get the transaction manager from the context instead of instantiating a new one.

            Cheers

            • 3. Re: jBPM 5.1 with SQL Server
              Dominique Paquin Newbie

              Thanks for the feedback,

              On my second post (ie:second connection scenario), I can't get past the PoolingDataSource.init due to the exception presented above, so I can never get to the point of calling a TransactionManagerServices.getTransactionManager  (If this is what you are refering to.)

              • 4. Re: jBPM 5.1 with SQL Server
                Mauricio Salatino Master

                Oh.. that's right.. it could be a bitronix problem with SQL server??

                 

                did you try:

                ds.getDriverProperties().put("URL", "jtds:sqlserver://192.168.117.129:1433;databaseName=myDatabase");

                 

                Because I think is a problem with the way of sending the parameters to create the datasource, each driver has it's on ways. You need to look for the docs of that driver to know which parameters do you need to initialize.

                Cheers

                • 5. Re: jBPM 5.1 with SQL Server
                  Dominique Paquin Newbie

                  I tried the configuration you provided but, in end, the "put" is not able to set a URL property for the jtds driver... its pretty weird but its as if this property does not exist in the driver.

                   

                  I will try to find examples of bitronix configurations for SQL Server.

                   

                  Thanks for putting me on a new investigation trail

                  • 6. Re: jBPM 5.1 with SQL Server
                    Hugo Boss Newbie

                    I have the same problem... i'm using hsqldb and this driver org.hsqldb.jdbcDriver

                     

                     

                    Please post your solution, if you find one. Thank you.

                     

                    Here the exception...

                     

                     

                    {code}

                    Caused by: org.springframework.beans.BeanInstantiationException: Could not instantiate bean class [com.plixia.domain.businessprocess.ProcessSessionFactory]: Constructor threw exception; nested exception is bitronix.tm.resource.ResourceConfigurationException: cannot create JDBC datasource named jdbc/jbpmDataSource

                        at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:141)

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

                        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateBean(AbstractAutowireCapableBeanFactory.java:948)

                        ... 56 more

                    Caused by: bitronix.tm.resource.ResourceConfigurationException: cannot create JDBC datasource named jdbc/jbpmDataSource

                        at bitronix.tm.resource.jdbc.PoolingDataSource.init(PoolingDataSource.java:76)

                        at com.plixia.domain.businessprocess.ProcessSessionFactory.initEnvironment(ProcessSessionFactory.java:74)

                        at com.plixia.domain.businessprocess.ProcessSessionFactory.<init>(ProcessSessionFactory.java:57)

                        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

                        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

                        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

                        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

                        at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:126)

                        ... 58 more

                    Caused by: bitronix.tm.utils.PropertyException: no writeable property 'URL' in class 'org.hsqldb.jdbcDriver'

                        at bitronix.tm.utils.PropertyUtils.getSetter(PropertyUtils.java:318)

                        at bitronix.tm.utils.PropertyUtils.setDirectProperty(PropertyUtils.java:217)

                        at bitronix.tm.utils.PropertyUtils.setProperty(PropertyUtils.java:83)

                        at bitronix.tm.resource.common.XAPool.createXAFactory(XAPool.java:314)

                        at bitronix.tm.resource.common.XAPool.<init>(XAPool.java:63)

                        at bitronix.tm.resource.jdbc.PoolingDataSource.buildXAPool(PoolingDataSource.java:85)

                        at bitronix.tm.resource.jdbc.PoolingDataSource.init(PoolingDataSource.java:72)

                        ... 65 more

                    {code}

                    • 7. Re: jBPM 5.1 with SQL Server
                      Dominique Paquin Newbie

                      Unfortunately, I didn't come to any valid solution yet so I deffered this to one of out top-gun resources herre which is currently on prolonged vacations. He should be back in 10 days and he will most assuredly work on this and try to find a solution.

                       

                      But it really seems to boil down to provide an already working entity manager as well as a transaction manager with the environment object. The only problem is that the one we are currently implementing in our product (which works directly with hibernate instead of JPA) does not extends the exact same Interface that jBPM expects to receive in the environment (refer to my first post).

                       

                      So there are a bit more research to do, but as soon as we have a valid solution, I will try to come back here and provide a solution.

                      • 8. Re: jBPM 5.1 with SQL Server
                        Hugo Boss Newbie

                        I tried now something new...

                         

                        I removed the source with PoolingDataSource ds = new PoolingDataSource(); and tried to configure the datasource with Spring. Unfortunaly i have nearly no Spring practice... but we will see ;-)

                         

                        I included all libs for jBPM by Maven (see this link to get all dependencies) http://grepcode.com/snapshot/repo1.maven.org/maven2/org.jbpm/jbpm-bpmn2/5.1.0.Final

                        Then some of the libs were not compatible with my current libs... this i could solve too. At the moment i get an IllegalArgumentException when my Entities (not of the jBPM) are instanciated. The problem is: I have really many entities to change and i'm not sure that it will work after this.

                         

                        If you need just a persistence example without Maven and Spring, i can send you one... i found it in the INET. This works and might help you.

                        • 9. Re: jBPM 5.1 with SQL Server
                          Marco Rietveld Novice

                          Dominique,

                           

                          You're almost there!

                           

                          See here for the all of the code from the following segment, but the following should work for SQL server:

                           

                          PoolingDataSource pds = new PoolingDataSource();

                           

                          // The name must match what's in the persistence.xml!

                          pds.setUniqueName("jdbc/testDS1");

                           

                          //

                          pds.setClassName("com.microsoft.sqlserver.jdbc.SQLServerXADataSource");

                           

                          // doesn't really matter

                          pds.setMaxPoolSize(3);

                           

                          pds.getDriverProperties().put("user", "thisIsMyUsername");

                          pds.getDriverProperties().put("password", "thisIsMyPassword");


                          for (String propertyName : new String[] { "serverName", "portNumber", "databaseName" })

                           

                          pds.getDriverProperties().put("serverName", "The.Server.Name.as.in.DNS.Name");

                          pds.getDriverProperties().put("portNumber", "portNumber);

                          pds.getDriverProperties().put("databaseName", "NameOfTheDatabase");

                           

                          pds.getDriverProperties().put("URL", "jdbcUrl");

                          pds.getDriverProperties().put("selectMethod", "cursor");

                          pds.getDriverProperties().put("InstanceName", "ThisIsTheInstanceNameOfYourDatabase-sortOfLikeTheDatabaseName");

                           

                          pds.init();

                           

                          EntityManagerFactory emf = Persistence.createEntityManagerFactory("persistenceUnitName");

                           

                          Also, the jta-data-source element should not be commented out in your persistence.xml, obviously.