Intalio|BPMS 5.2 has been introduced with a new mechanism for retrieving data from a database: database connectors.  This functionality deprecates the existing JDBC connector that was already included in Intalio.

This tutorial will walk the reader through the process of creating a database connector, adding the connector to a process, and deploying the process.  We will focus on reading from a database.  A database connector will be used in the context of a process that selects information regarding a person from a table and sends part of the results back to the process initiator.

Before adding the database connector, the process looks like this.  A copy of the project up to this point is available here.

 

Preparation

Before creating our Database connector we will need to set up a connection profile and have a table to connect to.  A tutorial describing how to set up a connection profile is available here .  Instructions for setting up the table used in this tutorial will follow.

Using the People Table

This tutorial will use this people table definition with the data defined in the insert statements.

CREATE TABLE people(

               id int not null primary key auto_increment,

               username varchar(100),

               firstname varchar(100),

               lastname varchar(100),

               email varchar(100),

               phone varchar(100)

);

INSERT INTO people(username, firstname, lastname, email, phone)

values ('examples\\msmith', 'Mark', 'Smith', ' ', '1234567');

INSERT INTO people(username, firstname, lastname, email, phone)

values('examples\\ewilliams', 'Emily', 'Williams', ' ','98765432');

We will add the table to the existing Derby database that comes with Intalio|BPMS.  There are many ways to do this, but I use the SQuirreL SQL Client available at http://www.squirrelsql.org.  If you are running Intalio|BPMS on your computer then the database URL is ‘jdbc:derby://localhost:1527/BPMSDB’, the user name is ‘app’ and the password is ‘app’.  Make sure you are using the Apache Derby Client driver, not the Apache Derby Embedded driver.

Creating a Database Connector

Now that the connection profile is created and the table has been created and populated, we can finally create the database connector.

Right click on the project in the Process explorer and select “New > Database Connector”.  A dialogue will appear.

Edit the SQL file name to your desired name and make sure that the desired connection profile is selected.  For standard select interactions with a database, select “Service” in the “Use As A:” field.  I changed the “SQL File Name:” to getpeople.sql.  All other information is already appropriate for my project.  Selecting finish creates the file getpeople.sql and opens it in the Database Connector editor.

Edit the select statement to read:

Select * from people where firstname = ?

The question mark indicates that firstname is a parameter that will be assigned dynamically by the process.  Save getpeople.sql, close it and re-open it.  Closing it and reopening it will cause the parameter types to refresh.  The getpeople.sql file should now look like this.

Notice that firstname is now listed on the Parameter Types tab.  This will be how we dynamically pass data into the query from the process.

Now switch over to the Connection Profiles tab under the SQL Properties tab.  This tab has to do with how the process connects to the database when the process is deployed to the server.  By default, it uses the connection profile that you set up before creating this database connector.  Alternatively, you can use one of the JNDI names that you configured on your application server.  For this example we will use the connection profile.

Integrating the database connector into a process

By now the getpeople.sql database connector will be in your process explorer.

Drag and drop the getpeople.sql database connector from the process explorer to the non-executable process pool marked database.

You must drop database connectors onto non-executable pools only.  They cannot be added to executable pools.

Once added to the diagram connect the newly created task and the “Request people record” task with message flow arrows.  Save the file after you have finished these steps.

The process is almost complete.  All that remains is to map the data from the GetDataRequest into the database connector request and map the data returned by the database connector back into GetDataResponse.

First, select Request people record in the Get Data pool.  The mapper will look like this:

Map the data from $thisTaskRequestMsg.body->firstname to $nsGetPeopleRequestMsg.parameters->execute->parameters->firstname.  Make sure you save GetData.bpm after you finish the mapping.

This means that the data passed in as a parameter to the message that starts the process will be used as part of the select statement in the database connector.

The last bit of mapping is to use the result set from the database connector to populate the response message of the process.  Select the message end event at the end of the executable process.

We will be mapping the lastname from $nsGetpeopleResponseMsg.parameters to lastname in $thisTaskResponseMsg.body.  Notice the [+] next to row under $nsGetpeopleResponseMsg.parameters.  This means that response from the database query could be 0 or more records.  For the purpose of this tutorial we will just take the first row returned.  To do this you will need to edit the bpel code by hand.  First, though, map data from lastname on the left to lastname on the right of the mapper.

Now look at the data editor and find the mapping you just created.  It will be under “Get Data -> <sequence> -> EventEndMessage -> assign -> <bpel:copy>.  Right click on the <bpel:copy> and select “Edit”.

An “Edit” dialogue will appear with the bpel code for the mapping you just created.  Insert a “[1]”into the statement so that it now reads:

    <bpel:from>$nsGetpeopleResponseMsg.parameters/ns:rows/ns:row[1]/ns:LASTNAME</bpel:from>

Once again, save the GetData.bpm file. 

The process is now ready to deploy and executed.  Click on the “Deploy” button and open up the bpms-console at http://localhost:8080/bpms-console.  Login as “admin” with the password “changeit”.  Select “Processes” next to the Intalio logo in the upper left corner of the page.  Start the process by checking the box next to “processes/GetData:Get_Data” and then clicking on the Start button.

A popup window will appear.  Enter the first name “Mark” and click on the “Submit” button.

The process will return the last name of the first record with a first name of Mark.  In this case, Smith.