4 Replies Latest reply on Mar 16, 2007 8:30 AM by Adam Michalik

    Setting up JBoss database for deployment

    Adam Michalik Newbie

      Hello!
      As a fresh Jboss user I came to a problem that is basic probably for most of you, but I cannot find a solution anywhere.
      I am using jBPM-starters kit 3.1.4 and working in Eclipse with the JBoss plugin.
      I try to create two processes, where one is nested in another as a subflow. The very simplified version of the problem is such:
      Super-process:

      <?xml version="1.0" encoding="UTF-8"?>
      
      <process-definition
       xmlns="urn:jbpm.org:jpdl-3.1" name="Super">
       <start-state name="start">
       <transition name="" to="process1"></transition>
       </start-state>
       <process-state name="process1">
       <sub-process name="Sub" />
       <transition name="" to="end1"></transition>
       </process-state>
       <end-state name="end1"></end-state>
      </process-definition>


      Sub-process:
      <?xml version="1.0" encoding="UTF-8"?>
      
      <process-definition
       xmlns="urn:jbpm.org:jpdl-3.1" name="Sub">
       <start-state name="start">
       <transition name="" to="end1"></transition>
       </start-state>
       <end-state name="end1"></end-state>
      </process-definition>


      Then I try to run the super-process in a way suggested in this thread:
      http://www.jboss.org/index.html?module=bb&op=viewtopic&t=93504

      public class Test {
      
       public static void main(String[] args) {
      
       JbpmContext context = JbpmConfiguration.getInstance()
       .createJbpmContext();
      
       try {
       FileInputStream fis_super = new FileInputStream(
       "/path/Eclipse/workspace/SubflowTest/src/main/Super/processdefinition.xml");
       FileInputStream fis_sub = new FileInputStream(
       "/path/Eclipse/workspace/SubflowTest/src/main/Sub/processdefinition.xml");
      
       ProcessDefinition pdload_sub = ProcessDefinition
       .parseXmlInputStream(fis_sub);
       context.deployProcessDefinition(pdload_sub);
      
       ProcessDefinition pdload_super = ProcessDefinition
       .parseXmlInputStream(fis_super);
       context.deployProcessDefinition(pdload_super);
      
       GraphSession gs_caller = context.getGraphSession();
      
       // don't think I necessarily need to reload the process definitions
       // from the session but it doesn't make a difference
       ProcessDefinition pd_sub = gs_caller
       .findLatestProcessDefinition("Sub");
       ProcessDefinition pd_super = gs_caller
       .findLatestProcessDefinition("Super");
      
       ProcessInstance inst = new ProcessInstance(pd_super);
      
       // begin
       Token token = inst.getRootToken();
       ContextInstance contextInst = (ContextInstance) inst
       .getInstance(ContextInstance.class);
      
       System.out.println("getNode().getName(): "
       + token.getNode().getName());
      
       token.signal();
       System.out.println("getNode().getName(): "
       + token.getNode().getName());
       } catch (Exception e) {
       e.printStackTrace();
       } finally {
       context.close();
       }
       }
      }


      The result of the execution is the well known "table JBPM_PROCESSDEFINITION not found" error (exactly:
      10:49:44,916 [main] DEBUG DbPersistenceService : beginning hibernate transaction
      10:49:44,959 [main] WARN JDBCExceptionReporter : SQL Error: -22, SQLState: S0002
      10:49:44,960 [main] ERROR JDBCExceptionReporter : Table not found in statement [select top ? processdef0_.ID_ as ID1_4_, processdef0_.NAME_ as NAME2_4_, processdef0_.VERSION_ as VERSION3_4_, processdef0_.ISTERMINATIONIMPLICIT_ as ISTERMIN4_4_, processdef0_.STARTSTATE_ as STARTSTATE5_4_ from JBPM_PROCESSDEFINITION processdef0_ where processdef0_.NAME_=? order by processdef0_.VERSION_ desc]
      org.hibernate.exception.SQLGrammarException: could not execute query
       at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
       at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
       at org.hibernate.loader.Loader.doList(Loader.java:2153)
      (...)
      Caused by: java.sql.SQLException: Table not found in statement [select top ? processdef0_.ID_ as ID1_4_, processdef0_.NAME_ as NAME2_4_, processdef0_.VERSION_ as VERSION3_4_, processdef0_.ISTERMINATIONIMPLICIT_ as ISTERMIN4_4_, processdef0_.STARTSTATE_ as STARTSTATE5_4_ from JBPM_PROCESSDEFINITION processdef0_ where processdef0_.NAME_=? order by processdef0_.VERSION_ desc]
       at org.hsqldb.jdbc.Util.throwError(Unknown Source)
       at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
       at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
      (...)
      10:49:44,968 [main] ERROR GraphSession : org.hibernate.exception.SQLGrammarException: could not execute query
      org.jbpm.JbpmException: couldn't find process definition 'Sub'
       at org.jbpm.db.GraphSession.findLatestProcessDefinition(GraphSession.java:157)
       at org.jbpm.db.GraphSession.deployProcessDefinition(GraphSession.java:67)
       at org.jbpm.JbpmContext.deployProcessDefinition(JbpmContext.java:173)
       at Test.main(Test.java:26)
      Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
       at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
       at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
      (...)
      Caused by: java.sql.SQLException: Table not found in statement [select top ? processdef0_.ID_ as ID1_4_, processdef0_.NAME_ as NAME2_4_, processdef0_.VERSION_ as VERSION3_4_, processdef0_.ISTERMINATIONIMPLICIT_ as ISTERMIN4_4_, processdef0_.STARTSTATE_ as STARTSTATE5_4_ from JBPM_PROCESSDEFINITION processdef0_ where processdef0_.NAME_=? order by processdef0_.VERSION_ desc]
       at org.hsqldb.jdbc.Util.throwError(Unknown Source)
       at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
       at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
      (...)
      
      (I trimmed the log)
      So one thing I understood from what I read in the forum and in the Internet - i have to set up the Hibernate database and create a table JBPM_PROCESSDEFINITION. But I couldn't find a hint on how to to it. Can it be done programatticaly? Or how should I do it in any other way (preferably nice and easy :P)?

        • 1. Re: Setting up JBoss database for deployment
          Ronald van Kuijk Master

          if you read the documentation, you'll see that:
          - you have to point jbpm to a database in the hibernate config file (hibernate is no database!!) either by configuring a database directly or by pointing to a datasource.
          - jbpm (hibernate) will create the tables if they do not exist

          • 2. Re: Setting up JBoss database for deployment
            Adam Michalik Newbie

            Thanks a lot - now I understand how it should work and what is happening here. But the current state is such:
            I started the JBoss server, so I presume I have the Hypersonic database running (my web browser jmx console shows it as StateString: Running).
            I should link the hypersonic.cfg.xml to this database.
            I can see that DatabasePath of the HypersonicDB is "/usr/local/jbpm-starters-kit-3.1.4/jbpm-server/server/jbpm/data/hypersonic/localDB". I should point the cfg file to this path.
            I open the default src/main/config/hibernate.cfg.xml file from the project which contains such entries:

            <property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>
             <property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property>
            <property name="hibernate.connection.url">jdbc:hsqldb:mem:.;sql.enforce_strict_size=true</property>
             <property name="hibernate.connection.username">sa</property>
             <property name="hibernate.connection.password"></property>
            
             <property name="hibernate.cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>
            

            As much as I understand the crucial is the entry
            <property name="hibernate.connection.url">jdbc:hsqldb:mem:.;sql.enforce_strict_size=true</property>
            

            So try to put there "jdbc:hsqldb:/usr/local/jbpm-starters-kit-3.1.4/jbpm-server/server/jbpm/data/hypersonic/localDB"
            but this doesn't work...
            I'm sure I still don't quite understand how this database should be linked. Could someone explain it to me? Am I going the right way at all? Thanks for any help :)

            • 3. Re: Setting up JBoss database for deployment
              Adam Michalik Newbie

              Alright, I did one more thing - in HSQL Database Manager from jmx console I did File > Connect with default settings - which are exactly the same as the defaults in hibernate.cfg.xml (password, user, driver and URL - jdbc:hsqldb:mem:.). I restored the default cfg file, so now I have

              <property name="hibernate.connection.url">jdbc:hsqldb:mem:.;sql.enforce_strict_size=true</property>

              back. But it still doesn't work. What am I missing now?

              • 4. Re: Setting up JBoss database for deployment
                Adam Michalik Newbie

                Doh.. that was more stupid than I could think ;( I simply was editing a hibernate.cfg.xml file from another project... Anyway, for future generations - the "jdbc:hsqldb:/usr/local/jbpm-starters-kit-3.1.4/jbpm-server/server/jbpm/data/hypersonic/localDB" is working fine.