7 Replies Latest reply on Jul 20, 2006 3:56 AM by dgutil

    Cant get jBPM to work with oracle - please help

    qweniden

      Hi,

      I downloaded jBPM 3.1 (starters kit) and am trying to get a stand-alone proof of concept working and I got it to run fine with the default Hypersonic DB.

      Now I am trying to get it to work with Oracle. I noticed that the starters kit did not include a Oracle DDL script like there is for other supported databases so I looked online and found this link:

      http://wiki.jboss.org/wiki/Wiki.jsp?page=JbpmOnOracle

      I downloaded the DDL (jdpm-ddl-updated.sql) on there and ran it on my database. The problem is that it doesnt have all the tables that the other scripts have. For example there is no table called JBPM_PROCESSDEFINITION in it, but it is needed during runtime and its in the other scripts.

      What am I missing? I read userguide/thejbpmdatabase.html and it just says run the database scripts found at ${JBPM_SDK_HOME}/jbpm-db/build/${dbtype}/scripts but there is no oracle scripts there as I have mentioned.

      Am I just being stupid? Is there some documentation somehwere I didnt read thouroughly?

      Thanks in advance for any help/

        • 1. Re: Cant get jBPM to work with oracle - please help
          koen.aers

          The wiki is probably not up-to-date. Have a look at the docs : http://docs.jboss.com/jbpm/v3/userguide/thejbpmdatabase.html
          This describes the process for PostgreSQL and for MySQL. Oracle is just analogous...

          Regards,
          Koen

          • 2. Re: Cant get jBPM to work with oracle - please help
            qweniden

            >>>The wiki is probably not up-to-date. Have a look at the docs : http://docs.jboss.com/jbpm/v3/userguide/thejbpmdatabase.html
            This describes the process for PostgreSQL and for MySQL. Oracle is just analogous... <<<

            As I mentioned in my post I read that document and I understand it, the problem is that the oracle scripts are not included.

            See No Oracle scripts:

            C:\jbpm-starters-kit-3.1\jbpm-db\build>dir
            Volume in drive C has no label.
            Volume Serial Number is E4F2-0E0C

            Directory of C:\jbpm-starters-kit-3.1\jbpm-db\build

            03/16/2006 10:19 AM .
            03/16/2006 10:19 AM ..
            03/16/2006 10:19 AM daffodildb
            03/16/2006 10:19 AM derby
            03/16/2006 10:19 AM firebird
            03/16/2006 10:19 AM hsqldb
            02/13/2006 07:11 PM jbpm-db-3.1
            03/16/2006 10:19 AM mssql
            03/16/2006 10:19 AM mysql
            03/16/2006 10:19 AM postgresql
            03/16/2006 10:19 AM sybase
            0 File(s) 0 bytes
            11 Dir(s) 8,140,623,872 bytes free

            C:\jbpm-starters-kit-3.1\jbpm-db\build>

            • 3. Re: Cant get jBPM to work with oracle - please help
              calvin1

              Generate SQL scripts for oracle
              I hope this is useful...All you need to start is ant

              1. Download the latest version of the JBoss jBPM Starters kit.
              This has got the jbpm-db folder which contains all the useful jBPM database stuff.
              http://www.jboss.org/products/jbpm/downloads

              2. Download the latest version of the JBoss jBPM. http://www.jboss.org/products/jbpm/downloads
              This has all the latest source code.

              3. Navigate to the ...\jbpm-starters-kit-X.X\jbpm-db folder.

              This folder should must contain the following files:

              ?build.properties ? Edit this file to correctly set the jbpm.3.location variable to the JBoss jBPM file path.
              eg

              jbpm.3.location=C:/jbpm-X.X
              
              upgrade.hibernate.properties=hsqldb/hibernate.properties
              upgrade.libdir=hsqldb/lib
              upgrade.old.schema.script=hsqldb/upgrade.scripts/hsqldb.create.jbpm.3.0.2.sql
              


              ? hibernate.cfg.xml ? Copy and paste in this file from
              ...\jbpm-starters-kit-X.X\jbpm\src\config.files

              Edit the connection properties

              <!-- jdbc connection properties -->
               <property name="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</property>
               <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
               <property name="hibernate.connection.url">jdbc:oracle:thin@HOST:PORT:SID</property>
               <property name="hibernate.connection.username">USER</property>
               <property name="hibernate.connection.password">PASSWORD</property>
              


              4.Update the connection settings in the hibernate.properties file in
              ...\jbpm-starters-kit-X.X\jbpm-db\oracle
              hibernate.dialect=org.hibernate.dialect.Oracle9Dialect
              # for Oracle 8 compatibility use
              #hibernate.dialect=org.hibernate.dialect.OracleDialect
              
              hibernate.connection.driver_class=oracle.jdbc.driver.OracleDriver
              hibernate.connection.url=jdbc:oracle:thin@HOST:PORT:SID
              hibernate.connection.username=USER<username>
              hibernate.connection.password=PASSWORD<password>
              
              hibernate.show_sql=true
              hibernate.c3p0.min_size=1
              hibernate.c3p0.max_size=3
              
              hibernate.query.substitutions=true 1, false 0
              


              5. Get the latest oracle driver from oracle:
              http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

              Put this in the ...\jbpm-starters-kit-X.X\jbpm-db\oracle\lib folder
              This test was run for classes12.zip

              6. Open a cmd prompt and navigate to
              ...\jbpm-starters-kit-X.X\jbpm-db

              7. On the command line type: ant oracle.scripts

              8. The ant script should compile the latest source code into the following class folders in ...\jbpm-X.X\build.
              ?classes.examples
              ?classes.identity
              ?classes.identity.test
              ?classes.jbpm
              ?classes.jbpm.test
              ?classes.webapp

              It then uses these classes in the classpath to create the sql scripts.

              9. The following scripts should be created in
              ?..\jbpm-starters-kit-X.X\jbpm-db\build\oracle\scripts
              ?oracle.clean.sql
              ?oracle.create.sql
              ?oracle.drop.create.sql
              ?oracle.drop.sql



              • 4. Re: Cant get jBPM to work with oracle - please help
                qweniden

                Thanks :)

                Worked like a charm

                • 5. Re: Cant get jBPM to work with oracle - please help
                  sameer_ja1

                  I did the steps given above. Still the scripts are not generated.
                  the output of ant run is as follows

                  D:\jbpm-starters-kit-3.0.3\jbpm-db>ant oracle.scripts
                  Buildfile: build.xml
                  oracle.scripts:
                  prepare:
                  compile.jbpm:
                  compile.jbpm.test:
                  compile.identity:
                  compile.webapp:
                  compile.identity.test:
                  compile.examples:
                  compile:
                  db.scripts:
                  [delete] Deleting directory D:\jbpm-starters-kit-3.0.3\jbpm-db\build\oracle\scripts
                  [mkdir] Created dir: D:\jbpm-starters-kit-3.0.3\jbpm-db\build\oracle\scripts
                  [java] syntax: JbpmSchema create
                  [java] syntax: JbpmSchema drop
                  [java] syntax: JbpmSchema clean
                  [java] syntax: JbpmSchema scripts

                  BUILD SUCCESSFUL
                  Total time: 5 seconds

                  ---------- Still the scripts are not there in the D:\jbpm-starters-kit-3.0.3\jbpm-db\build\oracle\scripts folder.
                  Can anybody pls. tell me what could be the problem. Thanks in advance

                  • 6. Re: Cant get jBPM to work with oracle - please help

                    check out the wiki page now ^_^

                    • 7. Re: Cant get jBPM to work with oracle - please help
                      dgutil

                      Hello sameer_pa,

                      I've had the same problem with the same version of the starter kit.

                      First, if you have a look at the build.xml in the jbpm-db folder, you will find the target called "db.scripts".

                      Now count the number of arguments passed to the org.jbpm.db.JbpmSchema java class. In my case there 5 arguments passed to it.


                      <target name="db.scripts" description="helper target to generate the database scripts" depends="prepare">
                       <delete dir="build/${db}/scripts" />
                       <mkdir dir="build/${db}/scripts" />
                       <java classname="org.jbpm.db.JbpmSchema" fork="true">
                       <classpath refid="classpath.${db}" />
                       <arg value="scripts"/>
                       <arg value="${basedir}/build/${db}/scripts"/>
                       <arg value="${db}"/>
                       <arg value="${jbpm.3.location}/src/config.files/hibernate.cfg.xml"/>
                       <arg value="${basedir}/${db}/hibernate.properties"/>
                       </java>
                       </target>


                      Second, open the org.jbpm.db.JbpmSchema java class in the jbpm\src\java.jbpm\org\jbpm\db folder and please have a look at the main method. You notice that it only deals with 1 or 3 arguments whereas the build.xml file passes 5 arguments to it.

                      I simply added this test in the main method:

                      else if ( (args!=null)
                       && (args.length>3)
                       && ("scripts".equalsIgnoreCase(args[0])) ) {
                       new JbpmSchema(JbpmSessionFactory.createConfiguration()).saveSqlScripts(args[1], args[2]);
                      
                       }


                      Then I ran "ant mysql.scripts" and it worked.