3 Replies Latest reply on Oct 9, 2012 2:48 AM by Thomas Setiabudi

    How to set up jBPM5.3 to use MS SQL Server 2008 ?

    Thomas Setiabudi Expert

      Hi,

       

       

      I try to setup jBPM5.3 with MS SQL Server 2008 but still cant get it works. I have done the following steps

       

      1. Download "jbpm-5.3.0.Final-installer-full.zip"

      2. Download "eclipse-java-helios-SR2-win32-x86_64.zip" as my dev PC does not have Internet connection

      3. unzip the "jbpm-5.3.0.Final-installer-full.zip" and I get the "jbpm-installer" folder

      4. copy the "eclipse-java-helios-SR2-win32-x86_64.zip" to "jbpm-installer\lib"

      5. modify the build.xml in "jbpm-installer" :

          I made change from:

       

      <!-- Download Eclipse -->
        <condition property="download.type" value="win32">
          <os family="windows" />
        </condition> 
      

      to

       

      <!-- Download Eclipse -->
        <condition property="download.type" value="win32-x86_64">
          <os family="windows" />
        </condition>
      

      in order to make the ant install.demo detect my eclipse installer

       

      6. Then I start to refer the instruction to change the DB in http://docs.jboss.org/jbpm/v5.3/userguide/ch.installer.html#d0e597

      7. changed hibernate dialect in "db/persistence.xml" to SQLServerDialect (attached file db_persistence.xml)

       

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

      8. changed another persistence.xml in "task-service/resources/META-INF/persistence.xml" (attached task-service_resources_meta-inf_persistence.xml)

       

      <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>
            <property name="hibernate.connection.driver_class" value="net.sourceforge.jtds.jdbc.Driver"/>
            <property name="hibernate.connection.url" value="jdbc:jtds:sqlserver://DBName:1433/MyTaskDB" />
      
      

      9. As the jBPM 5.3 full installer comes with JBoss AS 7, so I modified the "standalone.xml" (attached standalone.xml)

       

      <datasources>
                      <datasource jndi-name="java:jboss/datasources/jbpmDS" pool-name="H2DS" enabled="true" jta="true" use-java-context="true" use-ccm="true">
                          <connection-url>jdbc:jtds:sqlserver://DBName:1433/MyJBPMDB</connection-url>
                          <driver>JTDS</driver>
                          <pool>
                              <min-pool-size>1</min-pool-size>
                              <max-pool-size>4</max-pool-size>
                              <prefill>false</prefill>
                              <use-strict-min>false</use-strict-min>
                              <flush-strategy>FailingConnectionOnly</flush-strategy>
                          </pool>
                          <security>
                             <user-name>test</user-name>
                             <password>12345</password>
                          </security>
                          <validation>
                             <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                             <validate-on-match>false</validate-on-match>
                             <background-validation>false</background-validation>
                          </validation>
                      </datasource>
                      <drivers>
                         <driver name="JTDS" module="net.sourceforge.jtds">
                                  <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>  
                          </driver>
                      </drivers>
                  </datasources>
      
      

      10. Because I use SQL Server, I continue to section "3.7.4. Using a different database" in http://docs.jboss.org/jbpm/v5.3/userguide/ch.installer.html#d0e597

      I choose to install the DB Driver in JBoss AS 7 as module, so..:

      11. in build.xml, I change the db.driver.jar.name to

       

      <property name="db.driver.jar.name" value="jtds-1.2.4.jar" />
      

      12. reopen the standalone.xml and make sure the driver name

       

      <driver>JTDS</driver>
      

      match with

       

       <drivers>
                         <driver name="JTDS" module="net.sourceforge.jtds">
                                  <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>  
                          </driver>
                      </drivers>
      
      

      13. open build.xml again, and change the db.driver.module.prefix to

       

      <property name="db.driver.module.prefix" value="net/sourceforge/jtds"/>
      

      14. modify the "db/driver_jar_module.xml" (attached as db_driver_jar_module.xml)

       

      <module xmlns="urn:jboss:module:1.0" name="net.sourceforge.jtds">
         <resources>
           <resource-root path="jtds-1.2.4.jar"/>
         </resources>
         <dependencies>
            <module name="javax.api"/>
            <module name="javax.transaction.api"/>
          </dependencies>
      </module>
      
      

      15. Copy the jtds driver (jtds-1.2.4.jar) to db\driver folder

       

      16. ok, up to this step, I tried to run ant install.demo.db

           and then followed by ant start.demo.db

      The DB Tables are created properly in my MS SQL Server.

      But I cannot use the jbpm-console, it turns out that the jbpm-console has its own persistence.xml that needs to be modified, as does the human task war

      so I go and modify those two persistence.xml (by exploding the war and re-war it again) and fix the hibernate dialect in those two persistence.xml

       

      17. I try again with fresh unzip of full installer, do all the step above up to step 15, and then in "lib" folder, I replaced

      "jbpm-5.3.0.Final-gwt-console.zip" with "jbpm-5.3.0.Final-gwt-console.zip" that have its persistence.xml modified (changed hibernate dialect to org.hibernate.dialect.SQLServerDialect)

      and also replaced "jbpm-5.3.0.Final-human-task-war.zip" and "jbpm-human-task-war-5.3.0.Final.war" with the one its persistence.xml modified (changed hibernate dialect to org.hibernate.dialect.SQLServerDialect)

       

      18. run "ant install.demo", everything installed properly

       

      19. run "ant install.db.files" to install the jtds driver as module in the JBoss AS 7

       

      20. run "ant start.jboss" to start the JBoss AS 7

       

      21. run "ant start.eclipse" to start the eclipse

       

      22. If I open http://localhost:8080/jbpm-console/ , login as krisv, password krisv. go to process section, and wait for a minute or two, I will get the following error (attached jbpmconsole.png)

       

       

          URL: 'http://localhost:8080/gwt-console-server/rs/tasks/krisv'
          Action: 'org.jboss.bpm.console.client.task.LoadTasksAction'
          Exception: 'class com.google.gwt.http.client.RequestException'
      HTTP 500: 
      org.jboss.resteasy.spi.UnhandledException: java.lang.ExceptionInInitializerError
       org.jboss.resteasy.core.SynchronousDispatcher.unwrapException(SynchronousDispatcher.java:345)
       org.jboss.resteasy.core.SynchronousDispatcher.handleApplicationException(SynchronousDispatcher.java:321)
       org.jboss.resteasy.core.SynchronousDispatcher.handleException(SynchronousDispatcher.java:214)
       org.jboss.resteasy.core.SynchronousDispatcher.handleInvokerException(SynchronousDispatcher.java:190)
       org.jboss.resteasy.core.SynchronousDispatcher.getResponse(SynchronousDispatcher.java:534)
       org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:496)
       org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:119)
       org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.service(ServletContainerDispatcher.java:208)
       org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:55)
       org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:50)
       javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
       org.jboss.bpm.console.server.util.GWTJsonFilter.doFilter(GWTJsonFilter.java:59)
       
       Root Cause
      java.lang.ExceptionInInitializerError
       org.jbpm.integration.console.StatefulKnowledgeSessionUtil.getStatefulKnowledgeSession(StatefulKnowledgeSessionUtil.java:131)
       org.jbpm.integration.console.SessionInitializer.<init>(SessionInitializer.java:25)
       org.jbpm.integration.console.TaskManagement.<init>(TaskManagement.java:51)
       org.jbpm.integration.console.ManagementFactory.createTaskManagement(ManagementFactory.java:26)
       org.jbpm.integration.console.ManagementFactory.createTaskManagement(ManagementFactory.java:19)
       org.jboss.bpm.console.server.TaskListFacade.getTaskManagement(TaskListFacade.java:72)
       org.jboss.bpm.console.server.TaskListFacade.getTasksForIdRef(TaskListFacade.java:101)
       sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       java.lang.reflect.Method.invoke(Method.java:597)
       org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:140)
       org.jboss.resteasy.core.ResourceMethod.invokeOnTarget(ResourceMethod.java:255)
      
      

       

      23. If I try to import the sample project to the eclipse, and then run the sample as java application, I got the following error

       

      0    02/08 13:14:04,650[main] ERROR service.hornetq.HornetQTaskClientConnector.connect  - Unable to connect to server using configuration org-hornetq-core-remoting-impl-netty-NettyConnectorFactory?port=5445&host=127-0-0-1
      org.jbpm.workflow.instance.WorkflowRuntimeException: [com.sample.evaluation:1 - Self Evaluation:2] -- Could not connect task client: on ip: 127.0.0.1 - port: 5445
       at org.jbpm.workflow.instance.impl.NodeInstanceImpl.trigger(NodeInstanceImpl.java:130)
       at org.jbpm.workflow.instance.impl.NodeInstanceImpl.triggerNodeInstance(NodeInstanceImpl.java:205)
       at org.jbpm.workflow.instance.impl.NodeInstanceImpl.triggerCompleted(NodeInstanceImpl.java:164)
       at org.jbpm.workflow.instance.node.StartNodeInstance.triggerCompleted(StartNodeInstance.java:49)
       at org.jbpm.workflow.instance.node.StartNodeInstance.internalTrigger(StartNodeInstance.java:41)
       at org.jbpm.workflow.instance.impl.NodeInstanceImpl.trigger(NodeInstanceImpl.java:124)
       at org.jbpm.ruleflow.instance.RuleFlowProcessInstance.internalStart(RuleFlowProcessInstance.java:35)
       at org.jbpm.process.instance.impl.ProcessInstanceImpl.start(ProcessInstanceImpl.java:188)
       at org.jbpm.workflow.instance.impl.WorkflowProcessInstanceImpl.start(WorkflowProcessInstanceImpl.java:303)
       at org.jbpm.process.instance.ProcessRuntimeImpl.startProcessInstance(ProcessRuntimeImpl.java:168)
       at org.jbpm.process.instance.ProcessRuntimeImpl.startProcess(ProcessRuntimeImpl.java:138)
       at org.drools.common.AbstractWorkingMemory.startProcess(AbstractWorkingMemory.java:1082)
       at org.drools.impl.StatefulKnowledgeSessionImpl.startProcess(StatefulKnowledgeSessionImpl.java:320)
       at com.sample.ProcessTest.main(ProcessTest.java:31)
      Caused by: java.lang.IllegalArgumentException: Could not connect task client: on ip: 127.0.0.1 - port: 5445
       at org.jbpm.process.workitem.wsht.GenericHTWorkItemHandler.connect(GenericHTWorkItemHandler.java:119)
       at org.jbpm.process.workitem.wsht.GenericHTWorkItemHandler.executeWorkItem(GenericHTWorkItemHandler.java:143)
       at org.drools.process.instance.impl.DefaultWorkItemManager.internalExecuteWorkItem(DefaultWorkItemManager.java:70)
       at org.jbpm.workflow.instance.node.WorkItemNodeInstance.internalTrigger(WorkItemNodeInstance.java:105)
       at org.jbpm.workflow.instance.impl.NodeInstanceImpl.trigger(NodeInstanceImpl.java:124)
       ... 13 more
      
      

       

      24. I attached server.log too

       

      25. What other things that I have tried:

           - Change driver-class in standalone.xml to

       

      <xa-datasource-class>net.sourceforge.jtds.jdbcx.JtdsDataSource</xa-datasource-class>
      

             this result to an exact same error, no improvement

       

          - put both driver-class and and the xa-datasource in standalone.

       

      <driver name="JTDS" module="net.sourceforge.jtds">
                                  <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>  
                                 <xa-datasource-class>net.sourceforge.jtds.jdbcx.JtdsDataSource</xa-datasource-class>
               </driver>
      
      

           the result is the same error.

       

      anything i miss out?

      or do I need to change something else?

      Any help is appreciated

        • 1. Re: How to set up jBPM5.3 to use MS SQL Server 2008 ?
          Thomas Setiabudi Expert

          Found it at last!

           

          there is one more persistence.xml which needs to be updated in "\task-service\resources\war\persistence.xml"

           

           

          • 2. Re: How to set up jBPM5.3 to use MS SQL Server 2008 ?
            Ville Itämaa Newbie

            Hi Thomas Setiabudi!

             

             

            I have been trying to also get a new database, but I am using MySQL instead.

            I have also managed to generate the tables in the database.

            But when I try to run the demo application (through Eclipse), I get the following error:

             

             

            05/10 09:10:06,785[main] ERROR service.hornetq.HornetQTaskClientConnector.connect  - Unable to connect to server using configuration org-hornetq-core-remoting-impl-netty-NettyConnectorFactory?port=5445&host=127-0-0-1
            org.jbpm.workflow.instance.WorkflowRuntimeException: [com.sample.evaluation:1 - Self Evaluation:2] -- Could not connect task client: on ip: 127.0.0.1 - port: 5445
            

             

             

            I have browsed following threads to find the error to no avail:

            https://community.jboss.org/thread/201620

            https://community.jboss.org/message/758919#758919

             

             

            What I have done sofar:

             

            1. Changed hibernate dialect in:

             

             

            "db/persistence.xml" to 

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

            (I have also tried the MySQLDialect, but noticed that I run an InnoDB version of MySQL)

             

             

            "task-service/resources/META-INF/persistence.xml" to    

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

             

             

            "task-service/resources/war/persistence.xml to

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

             

             

            "task-service/target/war/persistence.xml" to

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

             

            "task-service/target/META-INF/persistence.xml" to

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

             

            2. Modified the "standalone.xml" just like you have it in your step 9. (Apart that I have MySQL credentials, and the way it is described in the Demo tutorial)

             

             

            3. Double checked the build.xml and the section for the mysql driver

            <property name="db.driver.jar.name" value="mysql-connector-java.jar" />
            

             

             

            4. Ensured the standalone.xml to ensure driver name

            <driver>mysql</driver>
            

             

             

            match with:

             

             

                      <driver name="mysql" module="com.mysql">
                                <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
                      </driver>
            

            5. Ensure build.xml has correct prefix:

             

             

            <property name="db.driver.module.prefix" value="com/mysql"/>
            

             

             

            6. Ensure the "db/driver_jar_module.xml" looks correct:

             

             

            <module xmlns="urn:jboss:module:1.0" name="com.mysql">
               <resources>
                 <resource-root path="mysql-connector-java.jar"/>
               </resources>
               <dependencies>
                  <module name="javax.api"/>
                  <module name="javax.transaction.api"/>
                </dependencies>
            </module>
            

             

            7. Ensure the "db\driver" has the right driver in the directory: 

             

             

            mysql-connector-java.jar

             

             

            8. Try to run the application and I get the same results.

            Verified in the JBOSS AS Management console that the Human Task Service is running.

            (See attachment "JBOSS Management.png")

             

            9. Then I read on to change another persistence.xml in the jbpm-console:

            - Uncompress the jbpm-5.3.0.Final-gwt-console.zip => jbpm-gwt-console-5.3.0.Final.war & jbpm-gwt-console-server-5.3.0.Final.war

            - "Unwar" the jbpm-gwt-console-server-5.3.0.Final.war.

            - Change the "jbpm-gwt-console-server-5.3.0.Final/WEB-INF/classes/META-INF/Persistence.xml" to

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

             

             

            10. Try to "re-war" "jbpm-gwt-console-server-5.3.0.Final" into its original package.

            But this is where I am stuck!

             

             

            This is my build.xml and I am trying to war this using ant with the follwing command: "ant build-war".

             

             

            <?xml version="1.0" encoding="UTF-8"?>
            <target name="build-war">
                      <war destfile="jbpm-gwt-console-server-5.3.0.Final.war" webxml="jbpm-gwt-console-server-5.3.0.Final/WEB-INF/web.xml">
                                <fileset dir="jbpm-gwt-console-server-5.3.0.Final"/>
                                <lib dir="jbpm-gwt-console-server-5.3.0.Final/WEB-INF/lib"/>
                                <classes dir="jbpm-gwt-console-server-5.3.0.Final/WEB-INF/classes"/>
                      </war>
            </target>
            

             

            I get the following message:

             

             

            C:\War changes\New One\build.xml:2: Unexpected element "{}target" {antlib:org.ap
            ache.tools.ant}target
            

             

            How should the build.xml look like to "re-war" the directory: "jbpm-gwt-console-server-5.3.0.Final"?

             

             

            Attached is my directory structure ("directory structure war.png")

            • 3. Re: How to set up jBPM5.3 to use MS SQL Server 2008 ?
              Thomas Setiabudi Expert

              Hi Ville Itämaa,

               

              I havent tried configuring the JBPM with MySQL, but are you using the correct hibernate dialect?

               

              From the guide here http://docs.jboss.org/jbpm/v5.3/userguide/ch.installer.html#d0e597

               

              they seem to use  <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/> instead of <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLInnoDBDialect"/>

              I am not sure if it was the cause of your problem.

               

               

              Regards,

              Thomas Setiabudi