0 Replies Latest reply on Mar 1, 2017 1:32 AM by Felix Chan

    [Solved] Integrate Oracle Database 12c with JBPM 6.4.0

    Felix Chan Newbie

      Hello.

       

      I am trying to create a JBPM runtime which connects with my existing Oracle database. However, the runtime cannot be successfully initiated because of a type conversion error (?) . The below is the error messages I got:

       

      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Error messages:

       

      Feb 27, 2017 10:23:49 AM org.drools.persistence.TransactionManagerFactory <clinit>

      INFO: Using org.drools.persistence.jta.JtaTransactionManagerFactory@7f6fadfa

      Hibernate: select taskimpl0_.id as col_0_0_, deadlineim1_.id as col_1_0_, deadlineim1_.deadline_date as col_2_0_ from Task taskimpl0_, Deadline deadlineim1_ where taskimpl0_.archived=0 and (deadlineim1_.id in (select startdeadl2_.id from Deadline startdeadl2_ where taskimpl0_.id=startdeadl2_.Deadlines_StartDeadLine_Id)) and deadlineim1_.escalated=0 order by deadlineim1_.deadline_date

      Hibernate: select taskimpl0_.id as col_0_0_, deadlineim1_.id as col_1_0_, deadlineim1_.deadline_date as col_2_0_ from Task taskimpl0_, Deadline deadlineim1_ where taskimpl0_.archived=0 and (deadlineim1_.id in (select enddeadlin2_.id from Deadline enddeadlin2_ where taskimpl0_.id=enddeadlin2_.Deadlines_EndDeadLine_Id)) and deadlineim1_.escalated=0 order by deadlineim1_.deadline_date

      Feb 27, 2017 10:23:49 AM bitronix.tm.twopc.Preparer prepare

      WARNING: executing transaction with 0 enlisted resource

      Hibernate: select sessioninf0_.id as id1_71_0_, sessioninf0_.lastModificationDate as lastModi2_71_0_, sessioninf0_.rulesByteArray as rulesByt3_71_0_, sessioninf0_.startDate as startDat4_71_0_, sessioninf0_.OPTLOCK as OPTLOCK5_71_0_ from SessionInfo sessioninf0_ where sessioninf0_.id=?

      Feb 27, 2017 10:23:49 AM org.drools.persistence.SingleSessionCommandService rollbackTransaction

      WARNING: Could not commit session due to Could not find session data for id 1

      Hibernate: select SESSIONINFO_ID_SEQ.nextval from dual

      Hibernate: insert into SessionInfo (lastModificationDate, startDate, OPTLOCK, id, rulesByteArray) values (?, ?, ?, ?, ?)

      0    [http-bio-8088-exec-2] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper  - SQL Error: 932, SQLState: 42000

      1    [http-bio-8088-exec-2] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper  - ORA-00932: inconsistent datatypes: expected BINARY got TIMESTAMP

      Feb 27, 2017 10:23:49 AM org.drools.persistence.jta.JtaTransactionManager commit

      WARNING: Unable to commit transaction

      bitronix.tm.internal.BitronixRollbackException: RuntimeException thrown during beforeCompletion cycle caused transaction rollback

      Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected BINARY got TIMESTAMP

      .... and a lot of similar errors then

       

      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

       

      The following are the project setting:

       

      Tomcat/conf/context.xml

      <Transaction factory="bitronix.tm.BitronixUserTransactionObjectFactory"/>

      <Resource auth="Container" driverClassName="oracle.jdbc.driver.OracleDriver" maxActive="15" maxIdle="2" maxWait="10000" name="jdbc/jbpm-ds" type="javax.sql.DataSource" url="jdbc:oracle:thin:@localhost:1522/xxx" username="xxx" password="xxx" />

       

      Project/hibernate.xml

      <hibernate-configuration>

        <session-factory>

             <property name="connection.datasource">jdbc/jbpm-ds</property>

             <property name="current_session_context_class">thread</property>

       

             <property name="connection.url">jdbc:oracle:thin:@localhost:1522/xxx</property>

             <property name="connection.username">xxx</property>

             <property name="connection.password">xxx</property>

             <property name="connection.driver_class">oracle.jdbc.OracleDriver</property>

             <property name="dialect">org.hibernate.dialect.Oracle10gDialect</property>

       

             <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

             <property name="show_sql">true</property>

             <property name="hbm2ddl.auto">update</property>

       

        </session-factory>

      </hibernate-configuration>

       

      Project/persistence.xml

      <persistence>

         <persistence-unit name="org.jbpm.persistence.jpa" transaction-type="JTA">

               <provider>org.hibernate.ejb.HibernatePersistence</provider>

               <jta-data-source>java:comp/env/jdbc/jbpm-ds</jta-data-source>       

               <properties>    

                 <property name="hibernate.max_fetch_depth" value="3"/>

                 <property name="hibernate.hbm2ddl.auto" value="update" />

                 <property name="connection.autocommit" value="true"/>

                 <property name="hibernate.show_sql" value="true" />

                 <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />

                 <property name="hibernate.id.new_generator_mappings" value="false" />           

       

                 <property name="hibernate.transaction.jta.platform" value="org.hibernate.service.jta.platform.internal.BitronixJtaPlatform" />

          </properties>       

        </persistence-unit>

      </persistence>

       

      Project/Main.java

      public static RuntimeManager PerRequestRuntimeManager(String process, Properties properties) {

             UserGroupCallback userGroupCallback = new JBossUserGroupCallbackImpl(properties);

             RuntimeEnvironment environment = RuntimeEnvironmentBuilder.getDefault()

                  .entityManagerFactory(Persistence.createEntityManagerFactory("org.jbpm.persistence.jpa"))

                  .userGroupCallback(userGroupCallback)

                  .addAsset(ResourceFactory.newClassPathResource(process), ResourceType.BPMN2).get();

             runtimeManager = RuntimeManagerFactory.Factory.get().newSingletonRuntimeManager(environment);

             return runtimeManager;

      }

      public static void main(String[] args){

           Properties properties = new Properties();

           RuntimeManager  runtime = JBPMUtil.PerRequestRuntimeManager("negomultitasks.bpmn", properties);

      }

       

      What is the cause of it? Can I manually change the type or alter the table structure in order to solve this problem?

      Thank you for your help!

       

      Regards,

      Felix

       

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

       

      Solution:

      The problem was solved and it was caused by the type errors in schema provided by the jbpm-installer.

      If change all variables with timestamp type to date type, it would work fine. (In addition, you need to make sure those variables are stored as "Date" in the Oracle database)

       

      For example,

      jbpm-installer\db\ddl-scripts\oracle

          create table ProcessInstanceLog (

              id number(19,0) not null,

              correlationKey varchar2(255 char),

              duration number(19,0),

              end_date timestamp,

              externalId varchar2(255 char),

              user_identity varchar2(255 char),

              outcome varchar2(255 char),

              parentProcessInstanceId number(19,0),

              processId varchar2(255 char),

              processInstanceDescription varchar2(255 char),

              processInstanceId number(19,0) not null,

              processName varchar2(255 char),

              processVersion varchar2(255 char),

              start_date timestamp,

              status number(10,0),

              primary key (id)

          );

       

      end_date and start_date need to be Date