Oracle Error Keyword from missing

[ajanz]

i got an error using oracle with jbpm

the statement is

select
 job0_.ID_ as ID1_25_,
 job0_.VERSION_ as VERSION3_25_,
 job0_.DUEDATE_ as DUEDATE4_25_,
 job0_.PROCESSINSTANCE_ as PROCESSI5_25_,
 job0_.TOKEN_ as TOKEN6_25_,
 job0_.TASKINSTANCE_ as TASKINST7_25_,
 job0_.ISSUSPENDED_ as ISSUSPEN8_25_,
 job0_.ISEXCLUSIVE_ as ISEXCLUS9_25_,
 job0_.LOCKOWNER_ as LOCKOWNER10_25_,
 job0_.LOCKTIME_ as LOCKTIME11_25_,
 job0_.EXCEPTION_ as EXCEPTION12_25_,
 job0_.RETRIES_ as RETRIES13_25_,
 job0_.NAME_ as NAME14_25_,
 job0_.REPEAT_ as REPEAT15_25_,
 job0_.TRANSITIONNAME_ as TRANSIT16_25_,
 job0_.ACTION_ as ACTION17_25_,
 job0_.GRAPHELEMENTTYPE_ as GRAPHEL18_25_,
 job0_.GRAPHELEMENT_ as GRAPHEL19_25_,
 job0_.NODE_ as NODE20_25_,
 job0_.CLASS_ as CLASS2_25_
from
 JBPM_JOB job0_
where (job0_.LOCKOWNER_ is null
 or job0_.LOCKOWNER_=?)
 and job0_.RETRIES_>0
 and job0_.DUEDATE_<=?
 and job0_.ISSUSPENDED_<>1
 order by job0_.DUEDATE_ asc

i could execute it from a database client. no problem

but under jbpm i got

ORA-00923- FROM keyword not found where expected-

what may i have done wrong?

[kukeltje]

wrong hibernate dialect?

[ajanz]

don't think so

configuration is

<?xml version='1.0' encoding='utf-8'?>

<!DOCTYPE hibernate-configuration PUBLIC
 "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
 "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
 <session-factory>

 <!-- hibernate dialect -->
 <property name="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</property>

 <!-- JDBC connection properties (begin) -->
 <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
 <property name="hibernate.connection.url">jdbc:oracle:thin:@server:1521:orcl</property>
 <property name="hibernate.connection.username">admin</property>
 <property name="hibernate.connection.password">admin</property>
 <!-- JDBC connection properties (end) -->

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

 <!-- DataSource properties (begin) ===
 <property name="hibernate.connection.datasource">java:/JbpmDS</property>
 ==== DataSource properties (end) -->

 <!-- JTA transaction properties (begin) ===
 <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JTATransactionFactory</property>
 <property name="hibernate.transaction.manager_lookup_class">org.hibernate.transaction.JBossTransactionManagerLookup</property>
 ==== JTA transaction properties (end) -->

 <!-- CMT transaction properties (begin) ===
 <property name="hibernate.transaction.factory_class">org.hibernate.transaction.CMTTransactionFactory</property>
 <property name="hibernate.transaction.manager_lookup_class">org.hibernate.transaction.JBossTransactionManagerLookup</property>
 ==== CMT transaction properties (end) -->

 <!-- logging properties (begin) ===
 <property name="hibernate.show_sql">true</property>
 <property name="hibernate.format_sql">true</property>
 <property name="hibernate.use_sql_comments">true</property>
 ==== logging properties (end) -->

 <!-- ############################################ -->
 <!-- # mapping files with external dependencies # -->
 <!-- ############################################ -->

 <!-- following mapping file has a dependendy on -->
 <!-- 'bsh-{version}.jar'. -->
 <!-- uncomment this if you don't have bsh on your -->
 <!-- classpath. you won't be able to use the -->
 <!-- script element in process definition files -->
 <mapping resource="org/jbpm/graph/action/Script.hbm.xml"/>

 <!-- following mapping files have a dependendy on -->
 <!-- 'jbpm-identity.jar', mapping files -->
 <!-- of the pluggable jbpm identity component. -->
 <!-- Uncomment the following 3 lines if you -->
 <!-- want to use the jBPM identity mgmgt -->
 <!-- component. -->
 <!-- identity mappings (begin) -->
 <mapping resource="org/jbpm/identity/User.hbm.xml"/>
 <mapping resource="org/jbpm/identity/Group.hbm.xml"/>
 <mapping resource="org/jbpm/identity/Membership.hbm.xml"/>
 <!-- identity mappings (end) -->

 <!-- following mapping files have a dependendy on -->
 <!-- the JCR API -->
 <!-- jcr mappings (begin) ===
 <mapping resource="org/jbpm/context/exe/variableinstance/JcrNodeInstance.hbm.xml"/>
 ==== jcr mappings (end) -->


 <!-- ###################### -->
 <!-- # jbpm mapping files # -->
 <!-- ###################### -->

 <!-- hql queries and type defs -->
 <mapping resource="org/jbpm/db/hibernate.queries.hbm.xml" />

 <!-- graph.action mapping files -->
 <mapping resource="org/jbpm/graph/action/MailAction.hbm.xml"/>

 <!-- graph.def mapping files -->
 <mapping resource="org/jbpm/graph/def/ProcessDefinition.hbm.xml"/>
 <mapping resource="org/jbpm/graph/def/Node.hbm.xml"/>
 <mapping resource="org/jbpm/graph/def/Transition.hbm.xml"/>
 <mapping resource="org/jbpm/graph/def/Event.hbm.xml"/>
 <mapping resource="org/jbpm/graph/def/Action.hbm.xml"/>
 <mapping resource="org/jbpm/graph/def/SuperState.hbm.xml"/>
 <mapping resource="org/jbpm/graph/def/ExceptionHandler.hbm.xml"/>
 <mapping resource="org/jbpm/instantiation/Delegation.hbm.xml"/>

 <!-- graph.node mapping files -->
 <mapping resource="org/jbpm/graph/node/StartState.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/EndState.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/ProcessState.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/Decision.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/Fork.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/Join.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/MailNode.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/State.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/TaskNode.hbm.xml"/>

 <!-- context.def mapping files -->
 <mapping resource="org/jbpm/context/def/ContextDefinition.hbm.xml"/>
 <mapping resource="org/jbpm/context/def/VariableAccess.hbm.xml"/>

 <!-- taskmgmt.def mapping files -->
 <mapping resource="org/jbpm/taskmgmt/def/TaskMgmtDefinition.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/def/Swimlane.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/def/Task.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/def/TaskController.hbm.xml"/>

 <!-- module.def mapping files -->
 <mapping resource="org/jbpm/module/def/ModuleDefinition.hbm.xml"/>

 <!-- bytes mapping files -->
 <mapping resource="org/jbpm/bytes/ByteArray.hbm.xml"/>

 <!-- file.def mapping files -->
 <mapping resource="org/jbpm/file/def/FileDefinition.hbm.xml"/>

 <!-- scheduler.def mapping files -->
 <mapping resource="org/jbpm/scheduler/def/CreateTimerAction.hbm.xml"/>
 <mapping resource="org/jbpm/scheduler/def/CancelTimerAction.hbm.xml"/>

 <!-- graph.exe mapping files -->
 <mapping resource="org/jbpm/graph/exe/Comment.hbm.xml"/>
 <mapping resource="org/jbpm/graph/exe/ProcessInstance.hbm.xml"/>
 <mapping resource="org/jbpm/graph/exe/Token.hbm.xml"/>
 <mapping resource="org/jbpm/graph/exe/RuntimeAction.hbm.xml"/>

 <!-- module.exe mapping files -->
 <mapping resource="org/jbpm/module/exe/ModuleInstance.hbm.xml"/>

 <!-- context.exe mapping files -->
 <mapping resource="org/jbpm/context/exe/ContextInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/TokenVariableMap.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/VariableInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/ByteArrayInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/DateInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/DoubleInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/HibernateLongInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/HibernateStringInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/LongInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/NullInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/StringInstance.hbm.xml"/>

 <!-- job mapping files -->
 <mapping resource="org/jbpm/job/Job.hbm.xml"/>
 <mapping resource="org/jbpm/job/Timer.hbm.xml"/>
 <mapping resource="org/jbpm/job/ExecuteNodeJob.hbm.xml"/>
 <mapping resource="org/jbpm/job/ExecuteActionJob.hbm.xml"/>

 <!-- taskmgmt.exe mapping files -->
 <mapping resource="org/jbpm/taskmgmt/exe/TaskMgmtInstance.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/exe/TaskInstance.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/exe/PooledActor.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/exe/SwimlaneInstance.hbm.xml"/>

 <!-- logging mapping files -->
 <mapping resource="org/jbpm/logging/log/ProcessLog.hbm.xml"/>
 <mapping resource="org/jbpm/logging/log/MessageLog.hbm.xml"/>
 <mapping resource="org/jbpm/logging/log/CompositeLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/ActionLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/NodeLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/ProcessInstanceCreateLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/ProcessInstanceEndLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/ProcessStateLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/SignalLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/TokenCreateLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/TokenEndLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/TransitionLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/VariableLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/VariableCreateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/VariableDeleteLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/VariableUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/variableinstance/ByteArrayUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/variableinstance/DateUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/variableinstance/DoubleUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/variableinstance/HibernateLongUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/variableinstance/HibernateStringUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/variableinstance/LongUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/variableinstance/StringUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/log/TaskLog.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/log/TaskCreateLog.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/log/TaskAssignLog.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/log/TaskEndLog.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/log/SwimlaneLog.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/log/SwimlaneCreateLog.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/log/SwimlaneAssignLog.hbm.xml"/>

 </session-factory>
</hibernate-configuration>

[kukeltje]

oracle boolean substitutions?

<property name="hibernate.query.substitutions">true 1, false 0</property>

[ajanz]

ok i only changed the hibernate.cfg.xml in the directory server/conf...but not in the jbpm-console and jbpm-identity archives.

so now i did that.

but after starting jbpm the database remains empty, especially jbpm_id_user is empty so i couldn't log in in jbpm-console.

is there an insert script for the default data?

[ajanz]

ok found the default data....

insert into JBPM_ID_USER (ID_,CLASS_,NAME_,EMAIL_,PASSWORD_) values (1,'U','user','[e-mail]','user');
insert into JBPM_ID_USER (ID_,CLASS_,NAME_,EMAIL_,PASSWORD_) values (2,'U','manager','[e-mail]','manager');
insert into JBPM_ID_USER (ID_,CLASS_,NAME_,EMAIL_,PASSWORD_) values (3,'U','admin','[e-mail]','admin');
insert into JBPM_ID_USER (ID_,CLASS_,NAME_,EMAIL_,PASSWORD_) values( 4,'U','shipper','[e-mail]','shipper');

insert into JBPM_ID_GROUP (ID_,CLASS_,NAME_,TYPE_) values (1,'G','sales','organisation');
insert into JBPM_ID_GROUP (ID_,CLASS_,NAME_,TYPE_) values (2,'G','admin','security-role');
insert into JBPM_ID_GROUP (ID_,CLASS_,NAME_,TYPE_) values (3,'G','user','security-role');
insert into JBPM_ID_GROUP (ID_,CLASS_,NAME_,TYPE_) values (4,'G','hr','organisation');
insert into JBPM_ID_GROUP (ID_,CLASS_,NAME_,TYPE_) values (5,'G','manager','security-role');

insert into JBPM_ID_MEMBERSHIP (ID_,CLASS_,USER_,GROUP_) values (1,'M',2,4);
insert into JBPM_ID_MEMBERSHIP (ID_,CLASS_,USER_,GROUP_) values (2,'M',3,4);
insert into JBPM_ID_MEMBERSHIP (ID_,CLASS_,USER_,GROUP_) values (3,'M',4,4);
insert into JBPM_ID_MEMBERSHIP (ID_,CLASS_,USER_,GROUP_) values (4,'M',4,3);
insert into JBPM_ID_MEMBERSHIP (ID_,CLASS_,USER_,GROUP_) values (5,'M',1,3);
insert into JBPM_ID_MEMBERSHIP (ID_,CLASS_,USER_,GROUP_) values (6,'M',2,3);
insert into JBPM_ID_MEMBERSHIP (ID_,CLASS_,USER_,GROUP_) values (7,'M',3,3);
insert into JBPM_ID_MEMBERSHIP (ID_,CLASS_,USER_,GROUP_) values (8,'M',3,2);
insert into JBPM_ID_MEMBERSHIP (ID_,CLASS_,USER_,GROUP_) values (9,'M',2,2);
insert into JBPM_ID_MEMBERSHIP (ID_,CLASS_,USER_,GROUP_) values (10,'M',2,5);
insert into JBPM_ID_MEMBERSHIP (ID_,CLASS_,USER_,GROUP_) values (11,'M',2,1);
insert into JBPM_ID_MEMBERSHIP (ID_,CLASS_,USER_,GROUP_) values (12,'M',1,1);