Websphere JDBC Adapter Conclusions

A few months ago, before changing the project, my team took the decision of using JDBC Adapter for a Bridge application.

The conclusion was that it is not trivial to use and does not have enough documentation for complex cases.

1. the main problem was that the Inboud (from the DB towards application) was not always working, sometimes we had to restart the servers 5-10 time to make it work. The solution was to update the process server to a higher version;

2. We did some tricks to make the relations one child table with two parents work (see previous post);

3. Bottom line is that if you're using Websphere Application Server instead of Websphere Process Server you have more chances to have it run smoothly concerning this topic; don't use this solution unless you have pretty straight forward schema design and you're not goiing to modifiy it too often, because the manual modifications might not cover all the Websphere Integration Development wizard for Adapters generates and you'll end up swearing.

Websphere JDBC Adapter

The last days I had to check how to save through the JDBC Adapter from Websphere a structure that is like this (see below just a relevant minimum example):

T_ADDRESS
{
ADRESS_ID;
PERSON_FK nullable true;(references t_person on person_id);
COMPANY_FK nullable true;(references t_company on person_id);
STREET_NAME;
...
}

T_PERSON
{
PERSON_ID;
NAME;
...
}

T_COMPANY
{
COMPANY_ID;
CUI;
...
}

So address is used for person type and for company type. From the JDBC Adapter Wizard I was able to configure only one relation parent child, let's say
T_Person is parent and T_Address is child. What about the other relation (T_Company -> T_Address)?

The wizard generated behind the scenes a new entry in the Person.xsd of type Address and in Adress.xsd added for element(column) PERSON_FK a new tag PERSON_ID.

SO I did the same modifications in T_COMPANY (I added a new element of type Address with preserve, KeepRelation and Ownership true) and I added a in Adress.xsd for COMPANY_FK the COMPANY_ID attribute.


Then I tried to save a sdo object of type person like below

Person
person_id = 1;
name = 'Some Name';
...

Address
address_id = 1;
person_fk = 1;
company_fk = null; --(this is an adress for a person not for a company)
street_name = 'New Street';
...


And the Adapter threw a NullPointerException. Why? It seems because the ForeignKey attribute inside forces him to believe that it needs to have a "parent" although the field and column are nullable.

So what did I do? I removed the COMPANY_ID and
PERSONS_ID tags.