4 Replies Latest reply on Dec 16, 2008 4:30 PM by mputz

    No auto-id for jbpm when running on Oracle

    janvandeklok

      Hello,

      We are running seam/jbpm on oracle. The jbpm version is 3.3.0 GA
      We ran into several problems that we did not experience when running the stuff on HSQLB.

      One of the problems (and this is probably also the cause for other oracle related problems) is that oracle does not have auti-id fields. As far as I can see, JBPM has not solved this problem meaning that we cannot start processinstances using the jbpm-console because hibernate tries to write records with a null in its id field.

      Here is the exception:


      2008-12-16 11:29:36,580 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 1400, SQLState: 23000
      2008-12-16 11:29:36,627 ERROR [org.hibernate.util.JDBCExceptionReporter] ORA-01400: cannot insert NULL into ("JANVDKLOK"
      ."JBPM_TOKEN"."ID_")


      And yes, we have specified the oracle dialect.


      I could not find any solution to this problem in the documentation.
      I suppose the solution will be :
      - creating one or more (for each table) oracle sequences and apply triggers to the database that get new id's for each new record.

      Since I don't want to reinvent the wheel :
      - has anybody founda solution to this problem?
      - Have I overlooked anything inthe documentation?

      Jan van de Klok



        • 1. Re: No auto-id for jbpm when running on Oracle
          mputz

          If you have created the jBPM tables using the provided jbpm.jpdl.oracle.sql script, a sequence should have been created, too:

          create sequence hibernate_sequence;

          The mapping files are held pretty generic when it comes to id generation:
          <id name="id" column="ID_"><generator class="native" /></id>

          This allows Hibernate to select the appropriate strategy depending on the configured db dialect, for Oracle it will be 'sequence'. Hibernate then calls the sequence (by default, if not further specified, the one named 'hibernate_sequence') when new rows are inserted.

          Have you set the dialect in hibernate.cfg.xml?
          <property name="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</property>


          • 2. Re: No auto-id for jbpm when running on Oracle
            janvandeklok

            Ai Martin.... shame on me!!!

            We have 2 hibernate.cfg.xml deployed in the jboss server: 1 that comes with the JBPM installation and 1 that was generated by SEAM for our application.

            Since JBPM did create-drop the jbpm tables and that behaviour was NOT specified in the JBPM hibernate.cfg.xml but was specified in the SEAM hibernate.cfg.xml I thought hibernate was just using the SEAM configuration file. In the SEAM configuration file the proper oracle dialect was set, in the JBPM configuration file it was not!!

            I changed the dialect and now the id generation work as does the hot deployment!!!

            Thanks Martin I owe you one!!!!

            What puzzles me however is that the JBPM hibernation reacts on the


            <property name="hbm2ddl.auto">create-drop</property>

            specified in the seam config file,


            while in the jbpm config file the following property was set
            <!-- Automatic schema creation (begin) -->
             <property name="hibernate.hbm2ddl.auto">none</property>
             <!-- Automatic schema creation (end) -->


            I'm not a hibernate expert but these configuration files are using different keys for the same behaviour. That seems strange doesn't it?

            Any way, Thanks for the help Martin.

            Jan




            • 3. Re: No auto-id for jbpm when running on Oracle
              kukeltje

              No, no shame on you... But... I've got to make a statement....

              You see Jan, even if Oracle is not (officially) supported (yet), it does work and there are great JBoss consultants/engineers/support staff/qa-people and unmentioned forum addicts that are able to help you out.... FOR FREE and get it working. Now try that at Bea/Oracle/.... ;-)

              • 4. Re: No auto-id for jbpm when running on Oracle
                mputz

                go, Ronald, go...!

                and Jan, I'm glad to hear your issue is resolved!