BPM 7.5.1 – Waging war against cyclic dependencies

I’m wearing the hat of toolkit producer these days and I’m responsible for delivering a number of outbound JDBC Advanced Integration Services.

Another team member is working on another toolkit, which also implements JDBC AISs.

Both toolkits need the JDBC connector module to compile, and both toolkits will bundle the JDBC RAR (embedded adapter).

This arrangement creates two interesting problems. One at ID workspace compile time, the other at runtime deployment time.

When I create the outbound JDBC import on my toolkit implementation module, the JDBC connector project with the adapter RAR is created, and in order to compile the toolkit, this project has to be associated with it or its dependencies.

So I associated the connector with my toolkit. Splendid.

Now, when I bring into my workspace the other team member’s toolkit, which also has the connector associated with it, things get hairy. Process Center tells me the connector already exists in my workspace, and I say “fine, makes sense, don’t bother loading it to my workspace a second time then” and I deselected it from the projects to bring in.

But then, what happens is, the connector is associated with only one of the toolkits, and my ID workspace can’t build. Joy.

To make matters worse, the runtime starts moaning about duplicate contributions, along the lines of “CWYBC_JDBC is already installed”.

Now, we can install the rar standalone on the server and that would resolve the deployment issue, but we still need a way to build the toolkits.

So what we did is factored out the connector project into a third toolkit and made both my toolkit and the other team member’s depend on it.

This approach worked fine, the connector module is associated with a single toolkit and that solves the build problems, and, somewhat counter intuitively, even though both our toolkit implementation modules dependencies ask for the connector project to be embedded, the deployment works OK as well.

WebSphere JDBC adapter and hierarchical objects with Oracle

At my client’s we have used the JDBC adapter to integrate with Oracle tables extensively in the past. In fact, the solution in place is pretty much hinging on Oracle tables for marketing campaigns and product configuration details.

So far we have always been conservative in our approach, creating integration flows one table at a time or working against Oracle views. Trying to not ask too much from the adapter.

New business requirements for a future release demand a bit more from our JDBC connectivity. We have to retrieve, update and create from multiple related tables, which is mostly fine, but we ran into a little rough patch doing the inserts.

It is worth mentioning than none of this would have been an issue with DB2, which supports Identity columns. The JDBC adapter understands these Identity fields and generates the Business Objects accordingly. But with Oracle things are a little different.

Oracle implements auto generated primary keys with a combination of Sequences and Triggers. The sequence supplies the ID value and the trigger fires before the insert populating the primary key with it.

Let’s take a simple example of two related tables, AUTHOR and BOOK.
AUTHOR.ID will be the primary key and BOOK.AUTHID will be the foreign key relating book rows to their author. I’m using Oracle XE 10g and created the tables using the web ui.

You can start creating a new WID integration project and drop an outbound JDBC  adapter in the assembly. Go through the wizard as usual but make sure you click the ‘Edit Query…’ button and check the ‘Prompt for additional configuration settings when adding business objects’ check box, as shown in the image below:

After you run the query you can start adding tables. Add the AUTHOR table first accepting all defaults.

Next add the BOOK table and build the child/parent relationship as in the following image:

Complete the wizard (I don’t generate business graphs, clear the checkbox)

Now is when the manual changes happen. Open the generated BO for the AUTHOR table, I’m using the system squema and the BO is called SystemAuthor.

You have to manually add the UID annotation to the ‘id’ attribute and supply the sequence name as shown below. This is done by a right-click on the metadata element.

You can repeat the process for the BOOK object for good measure, though for this case it isn’t strictly required.

Next we have to modify the Oracle trigger:

CREATE OR REPLACE TRIGGER  "BI_AUTHOR"
  before insert on "AUTHOR"
  for each row
WHEN (new.id is NULL)
begin
    select "AUTHOR_SEQ".nextval into :NEW.ID from dual;
end;

The manual change highlighted in red is required because the JDBC adapter, once is told that the ID field is generated, will query the sequence and populate the attribute with the retrieved value. It also synchronises any foreign keys on child objects. But just before the insert operation, without this manual change, the trigger will fire, replace the value of the auto-gen field with the next value in the sequence and cause the parent/child relationship to be out of sync.

You can also modify the trigger for BOOK inserts. We don’t have child objects of BOOK in this example to worry about but it makes sense to do it for completeness and to be ready for them.

You can now deploy and test. One thing to watch out for is the fact that the integration test client will populate the foreign key on the child object with a default value unless you manually unset it. So make sure you explicitly set any authid book attribute to unset as below:

The response should look like this:

You can also check your tables and verify that the rows have been inserted as expected.

Best regards

Gaby