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

[Solved] Integrate Oracle Database 12c with JBPM 6.4.0

felixchanch Mar 1, 2017 1:32 AM

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

Project/hibernate.xml

<hibernate-configuration>

<session-factory>

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

<property name="connection.url">jdbc:oracle:thin:@localhost:1522/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="hbm2ddl.auto">update</property>

</session-factory>

</hibernate-configuration>

Project/persistence.xml

<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>

</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