database configuration jboss, jbpm, mysql

[abhikal]

Hi
i have configured jbpm 3 on jboss 4 server and now i need to have 'mysql' database server.
Please help me to configure mysql on jboss jbpm 3 sever
steps that i followed are given in 'read me' text file, but they are not complete one to use mysql for hibernation.Please suggest

[brianmb99]

Hi there,

here's how I did it:

step 1: Using the jbpm-db package, generate database scripts for creating jBPM database in MySQL.

step 2: Create a new db and run the generated creation scripts. I haven't got the "clean" scripts to work on MySQL, but the create scripts work just fine in an empty database.

step 3: Modify the hibernate settings in jbpm.sar.cfg.jar (in jbpm.sar) to use a mysql datasource. Here are some example settings:
For jbpm.hibernate.properties:
hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect

hibernate.connection.driver_class=com.mysql.jdbc.Driver
hibernate.connection.url=jdbc:mysql://localhost:3306/jbpmtest
hibernate.connection.username=root
hibernate.connection.password=

hibernate.show_sql=true
hibernate.query.substitutions=true 1, false 0
hibernate.c3p0.min_size=1
hibernate.c3p0.max_size=3
For jbpm.hibernate.cfg.xml properties:
hibernate.dialect -> org.hibernate.dialect.MySQLInnoDBDialect
hibernate.connection.driver_class" ->com.mysql.jdbc.Driver
hibernate.connection.url ->jdbc:mysql://localhost:3306/databasename

Note that the hibernate config files to be used are specified in jbpm.properties.

step 4: Create the datasource that you specified in your jbpm hibernate settings and put the file in the jboss deploy directory. Here's an example:
<?xml version="1.0" encoding="UTF-8"?>

<!-- $Id: mysql-ds.xml,v 1.3.2.1 2004/12/01 11:46:00 schrouf Exp $ -->
<!-- Datasource config for MySQL using 3.0.9 available from:
http://www.mysql.com/downloads/api-jdbc-stable.html
-->


<local-tx-datasource>
<jndi-name>LocalMySqlDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/databasename</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>root</user-name>

<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
<!-- sql to call when connection is created
<new-connection-sql>some arbitrary sql</new-connection-sql>
-->
<!-- sql to call on an existing pooled connection when it is obtained from pool
<check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
-->

<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->

<type-mapping>mySQL</type-mapping>

</local-tx-datasource>


Step 5: make sure mysql-connector-java-3.1.7.jar is in your server lib.

Step 6: configure jBPM service to use the new MySQL data source. Back in the jbpm.sar, change the jboss-service.xml in META-INF to use MySQLDS (or whatever you called your data source in step 4) instead of DefaultDS:
jboss.jca:service=DataSourceBinding,name=LocalMySqlDS

That's it! Of course, if you want to use your MySQL database when running jBPM outside of the server (i.e. for testing in eclipse) you'll have to change the hibernate settings for whatever .par you're running.

I'm sure there's other ways to do it, using ant and recompiling the jbpm.sar, but this is how I got it done for testing purposes.

Hope that helps.

[abhikal]

Hi brian

i appreciate your efforst to give to me the step by step instructions
i would follow it
Thsnks again

regards
abhikal