jBPM with MS SqlServer 2008 R2

[parthtrivedi]

Hi,

I am very new to jBPM and I am facing some problems connecting my workflows on Kie workbench (or eclipse) with MS SQLSERVER 2008.

Please correct me if I am wrong here, I created a workflow in Kie.

I deployed it and started workflow.

Then I modified it and again deployed and started the workflow. So now I have around 6 instance IDs under process instances.

As far as my understanding goes, these instance IDs should be reflected in the database using :

select * from ProcessInstanceInfo and/or

select * from ProcessInstanceLog

But I don't see any of the information in the database I also tried checking in h2 database but again there anre no rows.

Now, I tried changing configs here: http://localhost:9990/console/App.html#datasources and I got successful test connection with my MS SQLServer database. I disabled other two exaple ones and enabled my connection. so, I am assuming that server is connected to my db and not h2. Even then I am not seeing any entries in db.

I have made changes in the configurations as below:

wildfly-8.1.0.Final\standalone\configuration\standalone.xml

<datasources>

               <!-- <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">

                    <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>

                    <driver>h2</driver>

                    <security>

                        <user-name>sa</user-name>

                        <password>sa</password>

                    </security>

                </datasource>

                <datasource jta="true" jndi-name="java:jboss/datasources/jbpmDS" pool-name="H2DS" enabled="true" use-java-context="true" use-ccm="true">

                    <connection-url>jdbc:h2:~/jbpm</connection-url>

                    <driver>h2</driver>

                    <security>

                        <user-name>sa</user-name>

                    </security>

                </datasource> -->

  <datasource jta="true" jndi-name="java:jboss/datasources/sqlserver" pool-name="sqlserver" enabled="true" use-java-context="true" use-ccm="true">

                    <connection-url>jdbc:sqlserver://SQLSERVER2008R2:1234;DatabaseName=xyz</connection-url>

                    <driver>sqljdbc4-2.0</driver>

                    <security>

                        <user-name>sa</user-name>

                        <password>sa</password>

                    </security>

                </datasource>

                <drivers>

                    <!--<driver name="h2" module="com.h2database.h2">

                        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

                    </driver> -->

  <driver name="sqljdbc4-2.0" module="com.microsoft.sqlserver">

                        <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>

                    </driver>

In jbpm-persistence-JPA2.xml

<properties>

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

in build.properties:

#MSsqlserver

  db.name=sqlserver

  db.driver.module.prefix=net/sourceforge/jtds

  db.driver.jar.name=sqljdbc4-2.0.jar

in persistence.xml in \jbpm-installer\wildfly-8.1.0.Final\standalone\tmp\vfs\temp\temp4beea6c954470658\jbpm-console.war-cdbbb05879b61ee5\WEB-INF\classes\META-INF\persistence.xml

<properties>

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

Please help.

Thanks

[abhijithumbe]

Hi,

You configuration looks fine for this time. Looks like you are starting server instance using ant task(ant start.demo), am I correct ? If yes then this task start JBoss server with 'full' profile(standalone-full.xml)

~~~

  <target name="start.jboss">

    <property name="jboss.full.path.win" location="${jboss.home}/bin/standalone.bat" />

    <exec executable="${jboss.full.path.win}" spawn="yes" osfamily="windows">

      <env key="JAVA_OPTS" value="-XX:MaxPermSize=256m -Xms256m -Xmx512m" />

      <arg value="-b" />

      <arg value="${jboss.bind.address}" />

      <arg value="--server-config=standalone-full.xml" />

~~~

But you have modified standalone.xml file. To start server with MS sql DB configure similar datasource in standalone-full.xml file and check. Thanks.!!

[parthtrivedi]

Hi Abhijit,

Thanks for your reply but above solution didn't work for me. Apparently I found that if I create any new datasource in wildfly 8.1.0 -> Configuration -> Add and add attributes, connection, security and just save it and make it enable, it automatically reflets in standalone-full.xml ..So there was nothing to add in there for me.

So, now when I try to connect it to my datasource, it gives success note that means, my server is connected to my datasource. But now, When I am creating a project in Kie workbench and try to run it, I don't see any entries in sqlserver.. Please help.

[swiderski.maciej]

you need to change data source JNDI name in persistence.xml to point to the one you created on the server for SQLServer

HTH

[parthtrivedi]

Hi Maciej , in jBPM 6.1 and 6.2 There is only jBPM-persistence-JPA2.xml file and not persistence.xml file. There is one but in temp folder and I assume that is not useful. Okay, here is my jBPM-persistence-JPA2.xml configs:

<properties>

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

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

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

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

      <!-- BZ 841786: AS7/EAP 6/Hib 4 uses new (sequence) generators which seem to cause problems -->

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

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

    </properties>

  <jta-data-source>java:jboss/datasources/jBPMDS_MSSQL</jta-data-source> <!-- added this line -->

  </persistence-unit>

</persistence>

I am not so sure where do I need to change the JNDI name since I tried to search but there is nothing related to JNDI in this xml.

Please help. thanks!

[parthtrivedi]

Hi All,

thanks a lot for your help and inputs. I am able to see all the process logs in my MSSQLSERVER 2008 db.

Here is what I did: Step by step process to connect jBPM-6.2.0 to Microsoft SQLSERVER2008R2

Thanks,

Parth