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:

  before insert on "AUTHOR"
  for each row
WHEN (new.id is NULL)
    select "AUTHOR_SEQ".nextval into :NEW.ID from dual;

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



About Gabriel Telerman
Occupation: IBM SOA-BPM Consultant. Profession: Guitar Hero

7 Responses to WebSphere JDBC adapter and hierarchical objects with Oracle

  1. SKyper says:

    Wow, amazing blog layout. Simple a Very Good info here with a lot of picture. Easy to understand. Bookmark your site. Thanks

  2. Goutam says:

    Really a nice article. I am using sql statements in my JDBC adapter. As multiple operations are there in my mediation, sometimes it is difficult to identify which sql statement is for which operation. Is there a way to change the name of sql statements, I mean from “SQL Statement 1” to some custom nake like “Employee Updatation”. Thanks in advance.

  3. In order to work 50 weeks a year, you’re going to have to work
    30 weeks; in order to work 30 weeks, you’ll likely have
    to work 10. Celebrity impersonations, musical gags, and the use
    of props are just some of the things that you could do
    in your routine. He represented his homeland in four Olympics and is a three-time indoor world champ in the 1500 meters.

  4. lions site says:

    Those trips towards the vet will leave your automobile smelling just like a sewer and nothing can get that smell out.
    n – Your lodging will likely be provided on-site,
    nevertheless, you will need to have your own personal funds for food and transportation around town.
    Do not delay because you don’t know what are
    the situation could be plus it might be time sensitive,
    making each of the difference between recovery or death.

  5. needs the income, too, to prevent alternative methods of taxing its citizens.
    For example, you are able to build two regular times for
    checking your email — 9 am and 4 pm. Developing good communication and listening skills by practicing empathy and compassion along with caring for others to get as pleasing and meaningful relationships,.

  6. It is probably the leading available in the market and contains
    shown effective being an anti-aging agent. Then after a limited time with no such product (roughly monthly to three months) they might try one
    other formula. If additionally, you may use them for a long period, in that case your body’s capacity to generate bodily hormones
    naturally will likely be decreased and ultimately, you’ll turn into influenced by those shots.

  7. John says:

    Thanks for this informative blog post. I couldn’t find how to set a sequence at any other site.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: