Thomas Setiabudi [https://community.jboss.org/people/thomas.setiabudi] modified the document: "Set up JBPM5.4 Final Installer to use MS SQL Server 2008 using JTDS" To view the document, visit: https://community.jboss.org/docs/DOC-47975 -------------------------------------------------------------- * #A_What_I_Am_Going_To_Do A. What I Am Going To Do * #B_The_Setup_Steps B. The Setup Steps I have been using JBPM5.3 for some months and then I switch to JBPM5.4. After I download JBPM5.4.Final full installer, I thought the DB Set up will be exactly the same with JBPM5.3 full installer https://community.jboss.org/docs/DOC-19221 https://community.jboss.org/wiki/SetUpJBPM53ToUseMSSQLServer2008 But I realized that some things has changed and at the time when I write this, the guide for changing Database in JBPM5.4 full installer is still outdated http://docs.jboss.org/jbpm/v5.4/userguide/ch.installer.html#d0e609 http://docs.jboss.org/jbpm/v5.4/userguide/ch.installer.html#d0e609 h1. A. What I Am Going To Do      1. I will change the Database setting of jbpm5.4 final full installer and point it to use MS SQL Server 2008      2. The Database driver used is jtds-1.2.4      3. Although it is possible to have separate database for Task and Process data, in this setup I will use only one Database for all the Task database tables and all the Process database tables.      4. I do this set up using Windows XP 64 Bit      5. apache ant version used is Apache Ant 1.8.4      6. jdk used is jdk 1.6 (set in Windows Environment variable as JAVA_HOME)      7. I use 7-zip to help me modify files inside a .war file h1. B. The Setup Steps 1. JBPM5.4 final full installer comes with almost every needed component, except eclipse (optional). But I want the eclipse, so I download *eclipse-java-helios-SR2-win32-x86_64.zip* and put it into *jbpm-installer\lib* folder 2. Because my OS is 64 bit version, I have to do a modification to the *build.xml* file found in *jbpm-installer* folder. So I changed this part <!-- 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> 3. Now its time to set up the Hibernate settings, mostly we will make changes to the hibernate dialect, lets open *jbpm-installer\db* folder. 4. I am not sure how each file is used here in jbpm5.4 since the official guide still reflect jbpm5.3 files, so here is what I do, I start with opening *jbpm-installer\db*\*jbpm-persistence-JPA1.xml* I changed: <properties>       <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>       <property name="hibernate.max_fetch_depth" value="3"/> to <properties>       <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>       <property name="hibernate.max_fetch_depth" value="3"/> 5. Then I open *jbpm-installer\db*\*jbpm-persistence-JPA2.xml* I changed: <properties>       <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />       <property name="hibernate.max_fetch_depth" value="3" /> to <properties>       <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect" />       <property name="hibernate.max_fetch_depth" value="3" /> 6. Lets continue, and open *jbpm-installer\db\task-persistence-JPA1.xml* I changed: <properties>       <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>       <property name="hibernate.connection.driver_class" value="org.h2.Driver"/>       <property name="hibernate.connection.url" value="jdbc:h2:tcp://localhost/runtime/task" />       <property name="hibernate.connection.username" value="sa"/>       <property name="hibernate.connection.password" value=""/> to <properties>       <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/MyJBPMDB" />       <property name="hibernate.connection.username" value="myuser"/>       <property name="hibernate.connection.password" value="mypassword"/> 7. Open *jbpm-installer\db\task-persistence-JPA2.xml* I changed: <properties>       <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>                <property name="hibernate.max_fetch_depth" value="3"/> to <properties>       <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>                <property name="hibernate.max_fetch_depth" value="3"/> 8. Lets navigate to other file in other folder, lets open *jbpm-installer\task-service\resources\META-INF\persistence.xml* I changed: <properties>       <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>       <property name="hibernate.connection.driver_class" value="org.h2.Driver"/>       <property name="hibernate.connection.url" value="jdbc:h2:tcp://localhost/runtime/task" />       <property name="hibernate.connection.username" value="sa"/>       <property name="hibernate.connection.password" value=""/> to <properties>       <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/MyJBPMDB" />       <property name="hibernate.connection.username" value="myuser"/>       <property name="hibernate.connection.password" value="mypassword"/> 9. Ok, lets dig into .war files in the lib folder, (I use 7-zip to help me modify files inside .war files), lets open *jbpm-installer\lib* folder 10. Open *jbpm-installer\lib\jbpm-gwt-console-server-5.4.0.Final-EE6.war\WEB-INF\classes\META-INF\persistence.xml* I changed: <properties>       <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" />       <property name="hibernate.max_fetch_depth" value="3" /> to <properties>       <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect" />       <property name="hibernate.max_fetch_depth" value="3" /> 11. Next, Open *jbpm-installer\lib\jbpm-human-task-war-5.4.0.Final-EE6.war\WEB-INF\classes\META-INF\persistence.xml* I changed: <properties>       <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>                <property name="hibernate.max_fetch_depth" value="3"/> to <properties>       <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>                <property name="hibernate.max_fetch_depth" value="3"/> 12. Allright, seems like we are done changing dialects, lets provide our jtds jar. put *jtds-1.2.4.jar* into *jbpm-installer\db\driver* folder 13. next, we need to provide an xml file that will be used by jboss AS to recognize our jtds later, so *Create* a file called *sqlserver_module.xml* and put it in *jbpm-installer\db* folder The file content is: <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> 14. Wondered how the ant build will recognize our sqlserver_module.xml later? Happens to me too at first, lets open *jbpm-installer\build.properties* file Look at this section: # data base related properties db.driver.jar=${install.home}/db/driver/${db.driver.jar.name} db.driver.module.dir=${jboss.home}/modules/${db.driver.module.prefix}/main/ # default is H2 db.name=h2 db.driver.jar.name=${db.name}.jar db.driver.download.url=http://repo1.maven.org/maven2/com/h2database/h2/${... #other options are mysql #  db.name=mysql #  db.driver.module.prefix=com/mysql #  db.driver.jar.name=${db.name}-connector-java.jar #  db.driver.download.url=https://repository.jboss.org/nexus/service/local/r... #postresql #  db.name=postresql #  db.driver.module.prefix=org/postgresql #  db.driver.jar.name=${db.name}-jdbc.jar #  db.driver.download.url=https://repository.jboss.org/nexus/content/reposit... lets comment out the h2 setting and make our own sqlserver setting like this: # data base related properties db.driver.jar=${install.home}/db/driver/${db.driver.jar.name} db.driver.module.dir=${jboss.home}/modules/${db.driver.module.prefix}/main/ # default is H2 #db.name=h2 #db.driver.jar.name=${db.name}.jar #db.driver.download.url=http://repo1.maven.org/maven2/com/h2database/h2/${H2.version}/h2-${H2.version}.jar #other options are mysql #  db.name=mysql #  db.driver.module.prefix=com/mysql #  db.driver.jar.name=${db.name}-connector-java.jar #  db.driver.download.url=https://repository.jboss.org/nexus/service/local/r... #postresql #  db.name=postresql #  db.driver.module.prefix=org/postgresql #  db.driver.jar.name=${db.name}-jdbc.jar #  db.driver.download.url=https://repository.jboss.org/nexus/content/reposit... #sqlserver db.name=sqlserver db.driver.module.prefix=net/sourceforge/jtds db.driver.jar.name=jtds-1.2.4.jar because or db.name is sqlserver, our sqlserver_module.xml will be recognized by the ant build later on. If you really want to know the reason, open *jbpm-installer\build.xml* and look at this section     <copy file="db/${db.name}_module.xml" tofile="${db.driver.module.dir}/module.xml" /> 15. ok, now lets deal with the jboss AS 7 standalone setting, open *jbpm-installer\standalone.xml* we need to do two things here, modify data source and make jboss as recognize our jtds driver, so change this section: <datasource jta="true" jndi-name="java:jboss/datasources/jbpmDS" pool-name="H2DS" enabled="true" use-java-context="true" use-ccm="true">                     <connection-url>jdbc:h2:~/jbpm</connection-url>                     <driver>h2</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>sa</user-name>                     </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="h2" module="com.h2database.h2">                         <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>                     </driver>                 </drivers> to                <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>myuser</user-name>                        <password>mypassword</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>                                          <driver name="h2" module="com.h2database.h2">                                                  <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>                                         </driver>                 </drivers> 16. Phew, quite a lot files that we have configured. at this point you may want to copy and backup the whole *jbpm-installer* folder, just in case you missed some configuration and caused error in the installation process later, who knows, things like that can happen, so just to be safe... -------------------------------------------------------------- Comment by going to Community [https://community.jboss.org/docs/DOC-47975] Create a new document in jBPM at Community [https://community.jboss.org/choose-container!input.jspa?contentType=102&a...]