Camunda - DBMS connection (with PostgreSQL)


#1

Hi,

I would like to ask your guidance for connecting Camunda BPM to a DMBS.
I use a WildFly 10 full distribution and I use the H2 database for testing.
I want to go a step forward and create a database in a DBMS (I will use PostgreSQL as a strong and open-source system) for my application data.
I have read various posts but I have some questions.

  • Can I continue using the H2 DB for the Process-engine data and have another DB in PostgreSQL for my business data? I know it’s not a good solution but I want to test some things first and I don’t want to touch the process-engine data.
  • Which are the main steps? Download the JDBC jar and put it in the Wildfly modules folder, then edit the standalone.xml file? I’ve read in this post https://forum.camunda.org/t/how-to-set-persistent-database-with-camunda/3199/4?u=kontrag that we need to create a module.xml file. Where do such instructions come from?
  • What is a typical Java code example to setup a connection and make a query? Actually, what is a common approach? Shall I have a service task in the beginning of the process model with a delegate code to open the connection? And then close this connection at the end of the process?

Thanks!


#2

The datasource is defined in the standalone.xml (assuming you are using a standalone WildFly instance versus a multi-instance domain configuration) “” section. You can switch between an external DBMS (e.g. PostgreSQL) and the default H2 database, but I can’t think of an easy way for you to use both simultaneously unless you were using multiple Camunda BPMN engines, which I have never done. We use a single database with a single Camunda instance.

One way to achieve this is to install two instances of WildFly/Camunda on the same server. The first instance can be your production instance and it would use the PostgreSQL database. The second instance would be “offset” to another set of TCP ports and use the H2 database.

To offset the second WildFly/Camunda instance, create a completely separate installation on the server. In the standalone.xml file, find the following entry:

<socket-binding-group name="standard-sockets" default-interface="public" port-offset="${jboss.socket.binding.port-offset:0}">

The last attribute is the integer value offset of all (or most) TCP ports for the second instance. For example, we run multiple instances on the same server and I offset them 1000 ports. This is an example of how that would look:

<socket-binding-group name="standard-sockets" default-interface="public" port-offset="${jboss.socket.binding.port-offset:1000}">

When selecting the TCP ports to use, be sure they won’t conflict with other services.

If you wanted to do what I describe above, do the following:

  1. Download the Camunda 7.X/WildFly 10.1 distribution from the Camunda site

  2. Create a directory on the server for the first instance (e.g. /usr/local/camunda) and untar the entire distribution into it.

  3. Create a second directory on the server for the second instance (e.g. /usr/local/camunda_1000) and untar the entire distribution into it.

  4. Install the PostgreSQL database

  5. From the root of the installation (e.g. /usr/local/camunda/camunda-bpm-ee-wildfly10-7.6.2-ee), find the “sql/create” directory. In there you see two scripts for creating the PostgreSQL database that you need to run. For example, run “postgres_engine_7.6.2-ee.sql” first, and then run “postgres_identity_7.6.2-ee.sql”. This will set up the Camunda database. Note, I’m not sure, but Camunda might even do this for you automatically if you create an empty database and user.

  6. Create a user and password to the Camunda database and grant DELETE, INSERT, SELECT, and UPDATE privileges to the user.

  7. In the first instance directory, open the standalone.xml file and find the the datasource section. You will need to modify the “datasource” to match what is required for PostgreSQL. I can’t help you with specifics as we don’t use PostgreSQL, but here is an example for MySQL:

     <subsystem xmlns="urn:jboss:domain:datasources:4.0">
         <datasources>
             <datasource jta="true" jndi-name="java:jboss/datasources/ProcessEngine" pool-name="ProcessEngine" enabled="true" use-java-context="true" use-ccm="true" statistics-enabled="true">
                 <connection-url>jdbc:mysql://localhost:3306/camunda?autoReconnect=true&amp;useSSL=false</connection-url>
                 <driver-class>com.mysql.jdbc.Driver</driver-class>
                 <driver>mysql</driver>
                 <pool>
                     <min-pool-size>10</min-pool-size>
                     <initial-pool-size>10</initial-pool-size>
                     <max-pool-size>200</max-pool-size>
                 </pool>
                 <security>
                     <user-name>camunda</user-name>
                     <password>CamuPass1</password>
                 </security>
                 <timeout>
                     <set-tx-query-timeout>false</set-tx-query-timeout>
                     <blocking-timeout-millis>0</blocking-timeout-millis>
                     <idle-timeout-minutes>0</idle-timeout-minutes>
                     <query-timeout>0</query-timeout>
                     <use-try-lock>0</use-try-lock>
                     <allocation-retry>0</allocation-retry>
                     <allocation-retry-wait-millis>0</allocation-retry-wait-millis>
                 </timeout>
                 <statement>
                     <share-prepared-statements>false</share-prepared-statements>
                 </statement>
             </datasource>
             <drivers>
                 <driver name="mysql" module="com.mysql">
                     <driver-class>com.mysql.jdbc.Driver</driver-class>
                 </driver>
             </drivers>
         </datasources>
     </subsystem>
    
  8. For the second instance, you need only modify the “offset” described above as the H2 database is already configured by default.

There may be additional configuration steps as described in the Camunda documentation on WildFly, but at this point you should be able to start both instances.

If you need to install a driver, which you probably will, then you might want to have a look at this page: http://ralph.soika.com/wildfly-install-postgresql-jdbc-driver-as-a-module/

There are countless examples of how to set up the driver, but if you look at the example datasource section I provided, it will give you general guidelines.

Finally, you don’t really need to write Java database connections for Camunda to work. It already has everything it needs. This page gives details on how to connect to a PostgreSQL database with Java: https://jdbc.postgresql.org/documentation/80/connect.html. There are huge number of examples of how to do this, but I’ve never done it as I work exclusively in MySQL.

I hope this gets you started. Let me know if you have any questions.

Michael


#3

Hi @mppfor_manu,

Thanks for your reply!!
I was thinking to use the H2 DB for the process-engine data, so I wouldn’t have to create the tables in PostgreSQL, but it may be more complicated to run two instances.
So, let’s say, I move my DBs to the PostgreSQL server.
My questions now are:

  • Is the name of the process engine database fixed? Or can I use mine? For example, in the standalone.xml, where does the datasource “ProcessEngine” refer to? Database name? And in the connection-url, what does the /camunda refer to? Is this the name of the database or a fixed term?

  • In your post in the other topic, you write that the JDBC driver jar file shall be put in /usr/local/camunda-bpm-ee-wildfly10-7.6.2-ee/server/wildfly-10.1.0.Final/modules/system/layers/base/com while in the link you posted above it says to put it under /usr/local/camunda-bpm-ee-wildfly10-7.6.2-ee/server/wildfly-10.1.0.Final/modules/system/layers/base/org.
    Do you know which one is correct?

  • Examples of how to create the connection in a process model if not with Java delegate code?

Thanks!


#4

The name of the process engine isn’t fixed, but we’ve always just left it at “default”. Moreover, the name of the datasource isn’t fixed either, but we’ve always left it at “ProcessEngine”.

I always used the Maven artifact “group” to define the path for the module. If you look in the module directories, you can see a bunch of examples of how other modules configure it.

There are multiple locations for WildFly’s class loader to pick up modules. I don’t have a deep understanding of how the classloader works, but here’s an example from one of my servers:

/usr/local/camunda-bpm-ee-wildfly10-7.6.2-ee/server/wildfly-10.1.0.Final/modules/system/layers/base/com/mysql/main/mysql-connector-java-commercial-5.1.39-bin.jar

Here’s the contents of module.xml:

<module xmlns="urn:jboss:module:1.0" name="org.camunda.connect.camunda-connect-http-client">
  <resources>
    <resource-root path="camunda-connect-http-client-1.0.3.jar" />
  </resources>

  <dependencies>
    <module name="javax.api"/>
    <module name="org.camunda.commons.camunda-commons-logging" />
    <module name="org.camunda.commons.camunda-commons-utils" />

    <module name="org.camunda.connect.camunda-connect-core" />

    <module name="org.apache.httpcomponents.httpclient" />
  </dependencies>
</module>

As to creating a connection, what are you trying to do? What is the use case?


#5

I think I managed to setup the JDBC driver so that Camunda can connect to my DBs in PostgreSQL server and now I want to try to query the databases.
My use case is straightforward, I want to have an open connection as long as a process instance runs so I can query (or even update) my DBs.
I guess I need some Jave delegate code, like in this example.
Is this a right way?
What other options do I have from Camunda?


#6

Can anyone advise me on how to query a database from Camunda?
A service task with a delegate code in the beginning of the process is my first thought.
But what to do with the results?
Create also Java objects and store them there? And then store them as process variables (with JSON serialization) so I can use them throughout all the process?

Thanks!


#7

Any advice? It is quite common what I ask I guess but I want to hear opinions from more experienced users.
Thanks!


#8

Hi again,

I understand that it may be general coding questions but can anyone give opinion on how to deal with DB connection?
I manage to connect to a PostgreSQL DB server from Camunda, but how to store the data taken from a query?
Can I use Entity Manager? Or is this only used for the in-memory H2 database?

Thanks!