How do I complete switch of JBPM db to Oracle from Hypersoni

[brado]

I have downloaded the jBPM starter's kit, read the user guide, and all jBPM and jBPM-db readmes. It seems that the subject of configuring jBPM and the subsequent deployment to an actual JBoss server instance is an uncovered topic. The only documentation I can find seems to only address how to run the db create / drop tests from the jbpm-db ant build. I can run these things just fine, and all tests succeed.

But now I need to to know how to get a running instance of JBoss to properly use my Oracle database, rather than the Hypersonic database. I've tried changing the jbpm.sar's jbpm.properties file to point to my oracle-specific hibernate.properties file, but that doesn't seem to do anything. As a matter of fact, the changes to the jbpm.properties file in the jbpm/src/resources/jbpm.sar directory aren't found at all in the generated sar.

How is the switch to another database for use in JBoss accomplished?

(As an aside, I've found several bugs in the starter kit's jbpm ant files -- I'm wondering if there's maybe a new version of this going to be released soon -- it doesn't appear to have been tested.)

Help is greatly appreciated, thanks.

Brad

[fwshk]

Hi Brad,

I have success config jBPM to use MySQL as datasource
( Both editing starter's kit and build from original source )

Although, you are requesting Oracle not MySQL, the steps are almost the same. (In jBPM previous version)

In addition, the chapter 8 of jBPM userguide may also helpful to you

===== Pre-Requirement =====

1. Download jBoss
2. Download jBPM
3 .Download jBPM Startkit
4 .Download MySQL Driver
5. A Running MySQL Server
 - With a empty database named "jbpmtest"
 - The database should allow a user to access complete
 - The user's should be "root" and no password
6. A Installed Ant

Notice: Those simple operations are not included

===== ===== ===== ===== =====



===== Customization jBPM =====

Copy mysql-connector-java-5.0.0-beta-bin.jar to ${jBPM}/lib/jboss
Copy ${jBPM-Startkit}/jbpm-db/mysql to ${jBPM}/src/resources
Copy ${jBPM-Startkit}/jbpm-db/build/mysql/scripts/**/* to ${jBPM}/src/resources/mysql
Copy ${jBPM}/src/resources/hsqldb/identity.db.xml to ${jBPM}/src/resources/mysql

Create ${jBPM}/src/resources/jboss-4.0.x/deploy/jbpm-ds.xml
 - Context :
 <datasources>
 <local-tx-datasource>
 <jndi-name>JbpmDS</jndi-name>
 <connection-url>jdbc:mysql://localhost:3306/jbpmtest</connection-url>
 <driver-class>com.mysql.jdbc.Driver</driver-class>
 <user-name>root</user-name>
 <password></password>
 <metadata>
 <type-mapping>MySQL</type-mapping>
 </metadata>
 </local-tx-datasource>
 </datasources>

Edit ${jBPM}/src/resources/mysql/hibernate.properties
 - Replace
 - From : hibernate.connection.password=mysqlrootpwd
 - To : hibernate.connection.password=

Edit ${jBPM}/build.properties

Edit ${jBPM}/src/resources/jbpm.sar/META-INF/jboss-service.xml
 - Replace
 - From : jboss.jca:service=DataSourceBinding,name=DefaultDS
 - To : jboss.jca:service=DataSourceBinding,name=JbpmDS

Edit ${jBPM}/src/config.files/hibernate.cfg.xml
 - Replace
 - From :
 <property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>
 <property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property>
 <property name="hibernate.connection.url">jdbc:hsqldb:mem:.;sql.enforce_strict_size=true</property>
 <property name="hibernate.connection.username">sa</property>
 <property name="hibernate.connection.password"></property>
 - To :
 <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
 <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
 <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/jbpmtest</property>
 <property name="hibernate.connection.username">root</property>
 <property name="hibernate.connection.password"></property>

Edit ${jBPM}/build.xml
 - Replace
 - From :
 <replace file="build/jbpm.sar.cfg.jar.dir/hibernate.cfg.xml">
 <replacetoken><![CDATA[
 <property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>
 <property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property>
 <property name="hibernate.connection.url">jdbc:hsqldb:mem:.;sql.enforce_strict_size=true</property>
 <property name="hibernate.connection.username">sa</property>
 <property name="hibernate.connection.password"></property>]]>
 </replacetoken>
 <replacevalue><![CDATA[
 <property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>
 <property name="hibernate.connection.datasource">java:/DefaultDS</property>]]>
 </replacevalue>
 </replace>
 - To :
 <replace file="build/jbpm.sar.cfg.jar.dir/hibernate.cfg.xml">
 <replacetoken><![CDATA[
 <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
 <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
 <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/jbpmtest</property>
 <property name="hibernate.connection.username">root</property>
 <property name="hibernate.connection.password"></property>]]>
 </replacetoken>
 <replacevalue><![CDATA[
 <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
 <property name="hibernate.connection.datasource">java:/JbpmDS</property>]]>
 </replacevalue>
 </replace>

Edit ${jBPM}/build.deploy.xml
 - Replace All
 - From : classpath.hsqldb
 - To : classpath.db
 - Replace All
 - From : ${basedir}/src/resources/hsqldb/create.db.hibernate.properties
 - To : ${basedir}/src/resources/mysql/hibernate.properties
 - Replace
 - From : ${basedir}/src/resources/hsqldb/identity.db.xml
 - To : ${basedir}/src/resources/mysql/identity.db.xml
 - Replace
 - From :
 <fileset dir="${jboss.home}/server/default/lib" includes="hsqldb.jar" />
 - To :
 <fileset dir="${basedir}/lib/jboss" includes="mysql-connector-java-5.0.0-beta-bin.jar" />
 - Replace
 - From : <target name="create.db" depends="declare.jbpm.tasks, db.clean, db.start" description="creates a hypersonic database with the jbpm tables and loads the processes in there">
 - To : <target name="create.db" depends="declare.jbpm.tasks" description="creates a database with the jbpm tables and loads the processes in there">
 - Remove
 - Line :
 <!-- copy the jbpm database -->
 <mkdir dir="${jboss.home}/server/jbpm/data/hypersonic" />
 <copy todir="${jboss.home}/server/jbpm/data/hypersonic">
 <fileset dir="build/db" />
 </copy>
 <antcall target="db.stop" />
 - Line :
 <property name="hsqldb.database" value="${basedir}/build/db/localDB" />
 <property name="hsqldb.port" value="1701" />

 <target name="db.start" depends="declare.jbpm.tasks">
 <starthsqldb lib="${jboss.home}/server/default/lib/hsqldb.jar"
 database="${hsqldb.database}"
 port="${hsqldb.port}" />
 </target>

 <target name="db.start.mgr">
 <java classname="org.hsqldb.util.DatabaseManager" fork="true">
 <classpath refid="classpath.db"/>
 <arg line="-url jdbc:hsqldb:hsql://localhost:${hsqldb.port}"/>
 </java>
 </target>

 <target name="db.stop">
 <java classname="org.hsqldb.util.ShutdownServer" fork="true">
 <classpath refid="classpath.db"/>
 <arg line="-url jdbc:hsqldb:hsql://localhost:${hsqldb.port} -user sa"/>
 </java>
 </target>

 <target name="db.clean">
 <delete dir="build/db" />
 <mkdir dir="build/db" />
 </target>
 - Add
 - Line :
 <target name="copy.lib">
 <copy todir="${jboss.home}/server/jbpm/lib">
 <fileset dir="${basedir}/lib/jboss" includes="mysql-connector-java-5.0.0-beta-bin.jar" />
 </copy>
 </target>

===== ===== ===== ===== =====



===== Compile jBPM and Deploy =====

Open Command
 - cd ${jBPM}
 - ant
 - ant -f build.deploy.xml
 - ant -f build.deploy.xml copy.lib

===== ===== ===== ===== =====



===== Start jBoss =====

Open Command
 - cd ${jBPM}
 - ant -f build.deploy.xml start.jboss

===== ===== ===== ===== =====



===== Stop jBoss =====

Open Command
 - cd ${jBPM}
 - ant -f build.deploy.xml stop.jboss

===== ===== ===== ===== =====

Wish my reply is helpful and sorry for my poor English.

Roy

[brado]

Roy,

Thank you so much for your reply. I'm going through this now and I'll let you know how it goes.

Brad