| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Nested ASBO for nested database tables... |
« View previous topic :: View next topic » |
| Author |
Message
|
| dunesand |
 Posted: Mon Oct 17, 2005 4:38 am Post subject: Nested ASBO for nested database tables... |
 |
|
Acolyte
Joined: 17 Nov 2003
Posts: 65
Location: Cambridgeshire, UK
|
Database tables don't come built with an OO infrastructure.
In order to mimic a nested data structure, I've got one table, say 'Dan_Fruit', which contains columns with the names 'Pear', 'Apple', 'Banana'.
The columns are all of type int, but the Apple column is a reference to an id in the table 'Dan_Apple'.
I've created a ASBO from the JDBC ODA to produce my business objects. The one change I make is to change the type of 'Apple' to 'Dan_Apple'.
Whenever I insert data into the table with this ASBO though, the 'Apple' column is always NULL. The rest of the row correctly appears in the table, and the Dan_Apple table also gets the correct data.
Is it possible to configure this ASBO so that it creates a reference id to the row in the other table?
If so, how?
Thanks for your time.
Dan.
   |
|
| Back to top |
|
 |
| vk |
| Posted: Wed Oct 19, 2005 12:23 pm Post subject: |
|
|
Partisan
Joined: 20 Sep 2005
Posts: 302
Location: Houston
|
For nested tables, you need to have 2 attributes in the parent table -in your case Dan_Fruit. 1 attribute should be Apple of type Integer. The other attribute should be Apple_Type which will be of type Dan_Apple (BO created corresponding to the nested table).
For all the attributes, defined in the parent object with any data type other than the simple typeslike String, Integer etc, the adaper will not populate any value.
Change the BO design and retry the operation. It should work.
Hope this helps.
Regards,
VK. |
|
| Back to top |
|
|
| dunesand |
| Posted: Thu Oct 20, 2005 3:12 am Post subject: |
|
|
Acolyte
Joined: 17 Nov 2003
Posts: 65
Location: Cambridgeshire, UK
|
VK,
Firstly, thanks for your reply and help on this, much appreciated.
I've altered my JDBC ASBO so that it has [Child] as a attribute of type integer, then an attribute [Child_Type] of type [Child_BO].
I've tried using a submap in my mapping from the GBO to the ASBO.
When running the application, the [Child] column in my database still gets null inserted...
I've not changed my database tables at all, should I change these and insert a column for the [Child_Type]?
Thanks again.
Daniel. |
|
| Back to top |
|
|
| vk |
| Posted: Thu Oct 20, 2005 5:34 am Post subject: |
|
|
Partisan
Joined: 20 Sep 2005
Posts: 302
Location: Houston
|
Hi Dan,
Which is your broker? From the reference to maps, I guess it is ICS. I am using the JDBC adapter with WMQI as the broker. I have a message flow which sends an XML message to the adapter having values corresponding to the parent and child records. Both these records get inserted correctly into the respective tables.
The actual tables need not have the columns corresponding to the child. In the parent table (Dan_Fruit), the column Apple should be defined as a foreign key to the column in the table child table (Dan_Apple).
There should be 2 BOs in the adapter. The one corresponding to the parent table (Dan_Apple) should have 2 fields - Child of type integer and Child_Type of type Child_BO. In the application specific information for child, set FK=Child_BO.Child_Type apart from setting the column name.
I am not very familiar with maps as I have not worked with ICS. But I still might be able to help you in configuring the tables and the BOs.
Thanks,
VK. |
|
| Back to top |
|
|
| dunesand |
| Posted: Fri Oct 21, 2005 12:03 am Post subject: |
|
|
Acolyte
Joined: 17 Nov 2003
Posts: 65
Location: Cambridgeshire, UK
|
VK,
Thanks for your suggestions, unfortunately I'm still getting NULL in the parent column that is the foreign key.
Yep, the broker is ICS (WBI Expess Plus).
DATABASE
To continue in the our example, in my (Sql Server database) Dan_Fruit (parent) table I've got a column called Apples (integer).
In the Dan_Apples (child) table I've got a column called AppleID (int), which is an Identity column. This is the primary key column for this table.
The Apples column in the Dan_Fruit (parent) is a foreign key with the AppleID (int - PK) in the child.
Does this database layout sound okay?
BUSINESS OBJECTS
The Dan_Fruit (parent) BO has one attribute, Apple (integer), which has the FK check box ticked.
This attribute also has application specific information:
| Code: |
| FK=Dan_Apples.Apple_Type:CN=Apples |
In the above:
The "Dan_Apples" is the name of the child database table.
The "Apple_Type" is the name of the next attribute I'll mention in the BO.
Finally, the "Apples" references the column name in the parent obect of the child FK.
There is another attribute in the Dan_Fruit (parent) BO, which is called Apple_Type and its type is Dan_Apples, referencing the child BO. It has no App Specific info.
Does that sound okay?
INHERENT PROBLEM
I can see how the two attributes are linked together, by the Apple attribute referencing the Apple_Type attribute in the app specific info. The problem I still see is, when the JDBC adapter inserts the parent row into the database table, how does the adapter know the value to be inserted in the FK, when the child row has not yet been inserted?
To explain in the context of our example, the JDBC adapter inserts the row into the Dan_Fruit table. It then inserts a value into the Dan_Apples table. It's only when data is inserted into the child table, that the FK value is generated and realised. How does the parent table then get this value into its FK column?
I'm thinking it does the insertion top down, perhaps it goes the other way? Looked at SQL Profiler though, which allows me to see each SQL command as it's executed against the table, it confirms it is actually top down.
Hope that makes sense, and thanks again for your help. |
|
| Back to top |
|
|
| vk |
| Posted: Fri Oct 21, 2005 5:37 am Post subject: |
|
|
Partisan
Joined: 20 Sep 2005
Posts: 302
Location: Houston
|
Hi Dan,
Your table stricture and BO definitions look perfect. The application specific info of Apple in Dan_Fruit should be -
FN=Dan_Apples.Id:CN=Apple (Where Id is the column in the table Dan_Apple to which the column Apple in Dan_Fruit corresponds).
For nested BO's, this is the way the JDBC adapter works - first it inserts all child BOs into the respective table. Then it inserts the parent BO into the parent table. Otherwise, the foreign key constraint in the parent table will be violated.
Now the point to note is, the adapter does not automatically determine the value of the foreign key in the parent table from the corresponding value in the child table. We will have to pass the value twice. This means that you will have to pass the same value for the attribute Apple in Dan_Fruit and Apple in Dan_Apple.
Consider the case where you have 2 tables and you are manually inserting the records. You will first insert into the child table and then make an entry into the parent table. Will the value in the parent table come automatically for the foreign key? We have to populate the value again in the insert statement.
I am passing the values twice in the XML message which I send to the adapter. You also will have to do the same in the map. Hope this helps you solve the issue.
Thanks,
VK. |
|
| Back to top |
|
|
| dunesand |
| Posted: Fri Oct 21, 2005 6:56 am Post subject: |
|
|
Acolyte
Joined: 17 Nov 2003
Posts: 65
Location: Cambridgeshire, UK
|
VK,
Thanks for sticking with me on this. Your help is really appreciated!
One thing to ask, who is generating your child table primary key id?
I'm assuming the database is doing this. So at no time does the business object contain this value when writing data into the database.
Are you assuming the BO contains this foreign key already?
Thanks.
Dan. |
|
| Back to top |
|
|
| vk |
| Posted: Fri Oct 21, 2005 7:08 pm Post subject: |
|
|
Partisan
Joined: 20 Sep 2005
Posts: 302
Location: Houston
|
Hi Dan,
I still dont think that the adapter will automatically populate the value for the column in the parent table, which has been defined as a foreign key.
The only solution I can think of for your problem is to have an after trigger on the child table to populate the value for Apple in the parent table.
Do let know if you find any other solution. Right now, I dont have a scenario where the value in the child table is auto generated. So I know the value and am passing it to the adapter twice using the message flow in WMQI, as an XML message.
Thanks,
VK. |
|
| Back to top |
|
|
| dunesand |
| Posted: Wed Oct 26, 2005 2:29 am Post subject: |
|
|
Acolyte
Joined: 17 Nov 2003
Posts: 65
Location: Cambridgeshire, UK
|
VK,
A response from one IBM guy was that it's generally not a good idea to place this sort of logic into the Mappings. The ASBO should generate your key for you, and you shouldn't have to populate the ID, or pass it anywhere.
The BO App Spec Data you need is the UID tag, which when =AUTO, tells the adapter that the database is generating a unique ID, and that the connector should retrieve it.
This is used on the primary key, and then any foreign key that references this primary key, inherits this value. The UID=AUTO works with DB2 and Sql Server according to the JDBC documentation.
I've managed to get a child referencing by holding its parent's key in an FK, but not the other way round...
Will let you know how it goes.
Dan.
PS. The FK=syntax is
| Code: |
| FK=[ForeignObjectName].[PrimaryKeyNameInForeignObject] |
|
|
| Back to top |
|
|
| vk |
| Posted: Wed Oct 26, 2005 6:28 pm Post subject: |
|
|
Partisan
Joined: 20 Sep 2005
Posts: 302
Location: Houston
|
Dan,
Thanks for the update. I will definitely try it out sometime.
VK. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
