WebSphere JDBC adapter and hierarchical objects with Oracle
July 12, 2011 7 Comments
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.