This guide explains how to use the Intalio|BPMS Connector for JDBC, which provides the ability to connect to a database directly from your processes.

In this guide, we will see how to:

• Configure JDBC connections in Intalio|BPMS Designer
• Create a SQL query in Intalio|BPMS Designer
• Invoke a SQL query from a process in Intalio|BPMS Designer
• Configure JDBC connections in Intalio|BPMS Server

---

Configure JDBC connections in Intalio|BPMS Designer

Before being able to integrate your database with your processes, you first need to configure Intalio|BPMS Designer connections to your database. Such connections require a JDBC driver to be loaded in Intalio|BPMS Designer for your specific database.

Creating a JDBC driver bundle

Before creating a JDBC connection, you must create a JDBC driver bundle that can be reused in several connections that can share the same JDBC driver. To create/edit/remow a JDBC driver bundle:

1. Go to Window > Preferences
2. In the menu on the left, expand the Intalio|BPMS node and then the JDBC Connector node
3. Click on JDBC Driver's Bundles in the left menu (under Intalio|BPMS > JDBC Connector)

This gives you access to the window where you can manage your JDBC driver bundles.

Intalio|BPMS Designer already defines bundles for all supported database. You just need to edit the bundle for your database to add its JDBC driver. To edit a bundle:

1. Select your database in the list of bundles.
2. Click the Edit button. This opens a new window that edits your bundle properties.
3. Next to the Libraries field, click the Open tool and locate the JDBC driver for this JDBC bundle, and then click OK.

The following screenshot shows a fully configured MySQL bundle:

To test your JDBC driver bundle:

1. Select your bundle in the list.
2. Click the Test button.

If your bundle is properly configured, a pop up window should say "JDBC driver found". This does not test any connection to your database; it only checks that the driver to connect to your database is accessible by Intalio|BPMS Designer. Now you can create connections using this bundle.

Creating a database connection

To create a database connection:

1. Go to Window > Preferences
2. In the menu on the left, expand the Intalio|BPMS node and then the JDBC Connector node
3. Click on Database Connections in the left menu (under Intalio|BPMS > JDBC Connector)

This gives you access to the window where you can manage your database connections.

To create a new connection:

1. Click the New button. This opens the configuration window.
2. Provide a Configuration ID. This Configuration ID is extremely important. It defines your connection and the same ID must be used in the runtime configuration. This ID fully defines the connection. Note that the design time and runtime configurations can be different, only the configuration ID needs to be consistent.
   
3. Select a bundle. The drop-down menu gives you access to the list of bundles that are configured in the JDBC driver bundles properties (see above).
4. If necessary, provide a username and password.
5. Finally, provide the database URL. By default this field shows the database URL template as defined in your bundle.
6. Click OK
   

The following screenshot shows an example using a local MySQL database:

You can now test your database connection by selecting it and clicking the Test button. If your configuration is correct, a pop up window should say "Test successful!!!".

 

---

Create a SQL query in Intalio|BPMS Designer

To create a new SQL query in Intalio|BPMS Designer:

1. Go to File > New > Other...
   
2. In the "New" wizard, expand the Intalio|BPMS node and select "SQL query"
3. Click Next >
4. Click the Browse... button to select the project/folder where you want to store your query
5. Provide a file name for your query.
6. Click Finish

For example, the screenshot below shows how a "query.sql" file will be created in the TestJDBC project (step #5).

This opens the SQL query editor. At this point you will typically need to use two views: "DB Explorer" and "SQL Results". If they are not visible already, you can make them visible by going in Window > Show View > Other... and then select them under the JDBC Connector node.

1. Select your database connection in the DB Explorer view
2. Type your SQL query in the editor.
3. Click the "Execute query" tool.

This runs the query using the selected connection and shows the results in the SQL results view as in the screenshot below:

You can also define SQL queries to use parameters that will be dynamically passed from your process. To do this:

1. Type your SQL parameter name in the SQL query between question marks.
2. Click the .prm tab at the bottom of the SQL query editor (if your query is called "query.sql", the tab is called "query.prm")
3. Click the + button for each parameter in your query.
4. Select the parameter in the list and change its properties. The Parameter name must match the name in your SQL query. You can also configue its type, size, precision, scale and a test value.
5. Click the "Execute query" tool to test your query with the test value.

The SQL Results view is updated according to your query, as in the screenshot below:

To reuse your query in your process:

1. From the query editor, after you have successfully tested it, click the "Generate service description for last successful query" tool. This launches a wizard to create a WSDL file that will allow you to integrate your query within your process.
2. The first screen asks you whether you want to create a new WSDL file or reuse an existing one. Typically the same WSDL is used for several queries using the same connection.
3. The next screen asks you to define where the WSDL file should be created and under what name. By default the name will be the name of your database connection configuration with the ".wsdl" suffix.
4. Finally a last screen provides some additional configuration options (see screenshot below).
5. The "Select operation name from WSDL file" is editable. This can be any text (without special characters such as white space) and it is typically the name of your query. This will also be used as the default label on your activity that performs this query in your process model.
6. The service location defines the Intalio|BPMS Server where the connector is configured. You can typically leave it as it is. You may however need to change the host and port.
7. You can optionally set a target namespace, or leave it as it is. This namespace will be used by your process data variables for the input/output of your query.
8. Select the "Default user credentials" if you want the runtime configured default credentials to be used when performing the query at runtime. If you don't check it, then you can provide user credentials dynamically within your process (in the mapper) and at runtime the connector will use the dynamically provided credentials when running the query.
9. Select the "User server side query definition" unless you want to overwrite the SQL statement to define it dynamically.
10. Click Finish.

This creates a WSDL file in your project at the chosen location. You can now reuse this WSDL file in any process just like any other processes. Use the mapper to define its input parameters and reuse the result set (see Invoke a SQL query from a process in Intalio|BPMS Designer )

 

---

 

Invoke a SQL query from a process in Intalio|BPMS Designer

After you have created a SQL query in your project as described above, you may reuse it in any process:

1. In the Process Explorer, expand your query WSDL until you find the query operation (DB.wsdl > DB > DBPort > query, where DB is the name of your database connection, and query is the name of the query).
2. Drag-and-drop the query operation from your query WSDL into your process pool. A contextual menu pops up.
3. In the contextual menu, select "Invoke operation 'query' bound to port 'DBPort' inside service 'DB' (where DB is the name of your database connection and query is the name of the query)

---

Configure JDBC connections in Intalio|BPMS Server

At runtime, you need to configure one JDBC connection per database your processes are connecting to.

To configure a connection, simply create a folder under [IntalioServer]/var/config/jdbc-connector.

In the newly created folder, create a file 'connection.properties' with the following content:

#The database type
#Available types - DER10 for DERBY, MYS5 for MySQL , ORA9 for Oracle.
com.intalio.bpms.connector.jdbc.db.type=DER10

# Data source type: "jndi" or "driver"
com.intalio.bpms.connector.jdbc.ds.type=jndi

# JDNI path for data source (only used if data source type is "jndi")
com.intalio.bpms.connector.jdbc.jndi=jdbc/DBName

# Whether to allow dynamic (ad-hoc) queries or restrict to predefined, server-side, set of queries
com.intalio.bpms.connector.jdbc.allowDynamicSQL=true

# JDBC driver data source (only used if data source type is "driver")
com.intalio.bpms.connector.jdbc.driver=
com.intalio.bpms.connector.jdbc.url=

# Other JDBC driver specific properties here;
# these will be passed to underlying driver as-is
user=
password=

You can choose to configure your JDBC Connection by either retrieving a datasource already configured via JNDI or by using the driver directly. By default a JDBC Connection is configured for the BPMSDB which is the master database for the Intalio|BPMS runtime.

 

For development, the easiest approach is to use the "driver" type. Here is an example to connect to a MySQL database:

 

#The database type
#Available types - DER10 for DERBY, MYS5 for MySQL , ORA9 for Oracle.
com.intalio.bpms.connector.jdbc.db.type=MYS5

# Data source type: "jndi" or "driver"
com.intalio.bpms.connector.jdbc.ds.type=driver

# Whether to allow dynamic (ad-hoc) queries or restrict to predefined, server-side, set of queries
com.intalio.bpms.connector.jdbc.allowDynamicSQL=true

# JDBC driver data source (only used if data source type is "driver")
com.intalio.bpms.connector.jdbc.driver=com.mysql.jdbc.Driver
com.intalio.bpms.connector.jdbc.url=jdbc:mysql://localhost/myDatabase

# Other JDBC driver specific properties here;
# these will be passed to underlying driver as-is
user=jack
password=rabbit

 

Additionally, you must place the JDBC driver .jar file under the WEB-INF/lib directory of the JDBC connector. On Geronimo this directory corresponds to: %GERONIMO_HOME%\repository\ode\ode-war\5.0.xx\ode-war-5.0.xx.war\WEB-INF\lib.

 

Once you have saved your configuration and added your JDBC driver .jar, you are ready to go! Note that it takes a few seconds for Intalio|BPMS Server to load new JDBC connector configurations.