Set up jBPM5.3 to use MS SQL Server 2008

Version 2

    Hi, just converting a discussion thread to an Article here,

     

     

    To setup jBPM5.3 with MS SQL Server 2008 using jtds as connection driver, do 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. a. 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" />
    
    

     

    8.b. there is one more persistence.xml which needs to be updated in "\task-service\resources\war\persistence.xml" open and edit the hibernate dialect there

     

    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. It turns out that the jbpm-console has its own persistence.xml that needs to be modified, as does the human task war.

    so, 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

    Actually there are three files:

    1. inside "lib\jbpm-5.3.0.Final-gwt-console.zip"

    2. inside "lib\jbpm-5.3.0.Final-human-task-war.zip"

    3. inside "lib\jbpm-human-task-war-5.3.0.Final.war"

     

    number 2 and 3 seems referring to the same file, not sure which one is used, but I edit them all. So I edited 3 (three) persistence.xml from those three locations.

     

    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

     

    That's it.

     

    if you follow the jbpm5.3 user guide and miss something, please revisit step 8b. since that persistence.xml is not mentioned in the user guide documentation

     

    For the attachments, please refer to the source discussion:

     

    This article was generated from the following discussion: How to set up jBPM5.3 to use MS SQL Server 2008 ?