Tomcat 5.5 - Jbpm 3.2 - Starting failed <<couldn't get acqui

[hipchic]

Hi, i try to deploy on my tomcat 5.5.20 jbpm 3.2, and i used oracle 10g express edition, my problem it is a configuration database, but i didn't find some scripts for resolve my problem.

SO here the steps i have followed :

- download the jbpm-jpdl-suite-3.2.GA.zip (extract)

- copy the war file C:\jbpm-jpdl-3.2.GA\deploy\jbpm-console.war in your $CATALINA_HOME\webapps\

- modify the hibernate.cfg.xml in the jbpm-console.war file (you can also find the file in the config directory)

These the part of file i have modified

 <!-- JDBC connection properties (begin) -->
 <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
 <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
 <property name="hibernate.connection.username">jbpm</property>
 <property name="hibernate.connection.password">jbpm</property>
 <!-- JDBC connection properties (end) -->
 <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>

- i copy all the librairies in C:\jbpm-jpdl-3.2.GA\lib\*.jar in my $CATALINA_HOME\webapps\jbpm-console\WEB-INF\lib
i have copy the ojdb14.jar (oracle driver) in my $CATALINA_HOME\webapps\jbpm-console\WEB-INF\lib


- and i run the oracle script jbpm-jpdl-3-suite.2.GA\db\jbpm.jpdl.oracle.sql in my sqlplus console, and all the tables have been created successfully.

- And add the jBPM specific users and roles to $CATALINA_HOME/conf/tomcat-users.xml

 <role rolename="manager"/>
 <role rolename="admin"/>
 <role rolename="participant"/>

 <user username="ernie" password="ernie" roles="participant,manager,administrator"/>
 <user username="bert" password="bert" roles="participant"/>

So now when i launch my tomcat, i have this error :

15:38:41,921 [JbpmJobExector:216.94.110.165:1] WARN JDBCExceptionReporter : SQL Error: 923, SQLState: 42000
15:38:41,931 [JbpmJobExector:216.94.110.165:1] ERROR JDBCExceptionReporter : ORA-00923: FROM keyword not found where expected

15:38:41,931 [JbpmJobExector:216.94.110.165:1] ERROR JobSession : org.hibernate.exception.SQLGrammarException: could not execute query
15:38:41,951 [JbpmJobExector:216.94.110.165:1] ERROR JobExecutorThread : exception in job executor thread. waiting 80000 milliseconds
org.jbpm.JbpmException: couldn't get acquirable jobs
at org.jbpm.db.JobSession.getFirstAcquirableJob(JobSession.java:44)
at org.jbpm.job.executor.JobExecutorThread.acquireJobs(JobExecutorThread.java:111)
at org.jbpm.job.executor.JobExecutorThread.run(JobExecutorThread.java:56)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2214)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:388)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:804)
at org.jbpm.db.JobSession.getFirstAcquirableJob(JobSession.java:40)
... 2 more
Caused by: java.sql.SQLException: ORA-00923: FROM keyword not found where expected

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:911)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1121)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:962)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1243)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3459)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2211)
... 11 more

As you can the database is empty, so i didn't find some script for populate the database I check all the sql script available but there is nothing =>
http://wiki.jboss.org/wiki/Wiki.jsp?page=JbpmOnOracle

Thks for you reply...
regis

PS: i try to deploy the customized version for tomcat in launching the ant build process :
in jbpm-jpdl-3.2.GA/deploy

ant customize.console.for.tomcat

but when i deploy i have a severe error (Severe - listener cannot start)

[mputz]

Did you set the hibernate.dialect property in hibernate.cfg.xml?

Besides the changes you have already made, there should also be an entry for the dialect. Use org.hibernate.dialect.OracleDialect for Oracle 8 and earlier, org.hibernate.dialect.Oracle9Dialect for Oracle 9 and 10.

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

Regards,
Martin

[hipchic]

Thanks it's work now !!!

It would be very useful for others, if i could submit all steps i did for setup jbpm on tomcat 5.5 on the wiki.

Before, to deploy the application i spend half of a day for succeed to arrive to this step.

[mputz]

This wiki entry http://wiki.jboss.org/wiki/Wiki.jsp?page=JbpmOnTomcat already describes the steps to get jBPM 3.2 working with Tomcat 6.

If you find any differences there to what you have done with Tomcat 5.5 please feel free to update the wiki.

Regards,
Martin

[hipchic]

here my contribution http://wiki.jboss.org/wiki/Wiki.jsp?page=JbpmOnTomcatOracle

[dleerob]

I followed the steps on the JbpmOnTomcat wiki, however there is one step that I think you left out. It had me going for a short while, till I figured it out.

By the way, I am using Tomcat 5.0.28, and MySQL 5.0.27.

When I finished doing everything on the Wiki, I started up tomcat, and got an error that the JbpmDS datasource was not found/bound to that context.

In the end, I had to change the hibernate.connection.datasource property in the hibernate.cfg.xml file to:

<property name="hibernate.connection.datasource">java:comp/env/jdbc/JbpmDS</property>

Then in tomcat\conf\Catalina\localhost\jbpm-console.xml, I added the following:

<Resource name="jdbc/JbpmDS" auth="Container" type="javax.sql.DataSource"/>
 <ResourceParams name="jdbc/JbpmDS">
 <parameter>
 <name>factory</name>
 <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
 </parameter>
 <parameter>
 <name>maxActive</name>
 <value>100</value>
 </parameter>
 <parameter>
 <name>maxIdle</name>
 <value>30</value>
 </parameter>
 <parameter>
 <name>maxWait</name>
 <value>10000</value>
 </parameter>
 <parameter>
 <name>username</name>
 <value>jbossjbpm</value>
 </parameter>
 <parameter>
 <name>password</name>
 <value>jbossjbpm</value>
 </parameter>
 <parameter>
 <name>driverClassName</name>
 <value>com.mysql.jdbc.Driver</value>
 </parameter>
 <parameter>
 <name>defaultAutoCommit</name>
 <value>true</value>
 </parameter>
 <parameter>
 <name>url</name>
 <value>jdbc:mysql://localhost/jbpm32?useUnicode=true&amp;characterEncoding=utf-8</value>
 </parameter>
 <parameter>
 <name>removeAbandoned</name>
 <value>true</value>
 </parameter>
 <parameter>
 <name>removeAbandonedTimeout</name>
 <value>60</value>
 </parameter>
 <parameter>
 <name>logAbandoned</name>
 <value>true</value>
 </parameter>
 </ResourceParams>

After doing that, the jbpm-console worked.
There was no mention of creating this datasource in the Wiki. Did I miss something? Why was it different for me?

[mputz]

There was no mention of creating this datasource in the Wiki.

You are absolutely right - I never thought of creating a datasource for Tomcat, but this is a very valid approach as well.

If you look at the hibernate.cfg.xml snippet in the wiki, you will notice that the datasource reference is commented out while the jdbc connection properties are directly used.

It's up to you what you think is the better approach :-) If you want to pass that approach to other users, feel free to add it to the wiki!

[dleerob]

Aah, thanks for pointing out that the datasource is commented out. I didn't notice that, and of course that is why I was originally getting the datasource error, which is why I ended up creating the datasource in the first place. Would of been quicker/easier to just comment it out :)

[dleerob]

Okay, so I commented out the datasource, but then I get java.lang.UnsupportedOperationException: The user must supply a JDBC connection.

Any suggestions? I've found somewhere on the intenet that mentioned it was a JBPM bug. If this is try, is there a workaround?

[kukeltje]

yes commment out the datasource, but UNCOMMENT the database properties then. You should use one or the other

[dleerob]

Thank you. I thought just the header/comment for the jdbc properties was commented out, and not the entire block. Didn't notice the end comment element was at the end of the block.

[dleerob]

Thank you, all sorted out now. Didn't notice the JDBC properties were commented out. I though just the header was.