| Author |
Message
|
| PravinG |
 Posted: Wed Nov 28, 2007 3:39 am Post subject: JDBC Adapter - Insert or Update |
 |
|
Newbie
Joined: 16 Oct 2006
Posts: 9
Location: India
|
Hi,
We are working on JDBC Adapter. Adapter is working fine for verbs Create,Update and Delete. But in some of our requirements we dont know whether the data (with some primary key) is present in the respective table or not. We want to update the data if its present already. Else we want to insert the data.
Can we have some kind of check/dynamic verb in JDBC Adapter by which we can have a processing like - "If data with that key is present in the table then update else insert" ?
In oracle we use merge statement for such cases.
I am sure I am not the first one to have this problem. Has anyone faced this issue before ? What could be the best approach to go with this ?
Thanks,
Pravin. |
|
| Back to top |
|
 |
| Gaya3 |
| Posted: Wed Nov 28, 2007 4:29 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
Hi
Why can't you use Stored Procedure here...
JDBC Adapter has the option of triggering Stored Procedure. 
hope this will work out for you
Regards
Gayathri
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| eai_guy |
| Posted: Wed Nov 28, 2007 4:53 am Post subject: |
|
|
Voyager
Joined: 18 Oct 2007
Posts: 90
|
Why dont you do this in your collaboration logic ??
First send the retieve verb to check if the data exists,
if data exists
send update verb,
else
send create verb. |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Wed Nov 28, 2007 5:32 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
Hi eai_guy
But here we are not sure, PravinG is using ICS to do so.
from the performance view, do you think that checking from collaboration logic is feasible. Indeed its giving some load...
Regards
Gayathri
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| PravinG |
| Posted: Wed Nov 28, 2007 7:56 am Post subject: |
|
|
Newbie
Joined: 16 Oct 2006
Posts: 9
Location: India
|
Thanks both of you.
We are using WBI Message Broker (V6.0) with JDBC Adapter.
We are not having enough knowledge abt using Stored Procedure with JDBC Adapter. Now we are exploring that option also.
But just one basic doubt:
We have to insert the data into 250+ tables for a single input message. So that way we have to write that much queries/stored procedures. Also we dont have administrative control on DB part of this project. So we dont know if we can get the approval on using Stored Procedures. Again maintenance will be an issue there.
So we are trying one more way. In our Error flow-message flow, we are checking our error queue (which is JDBC FAULT queue) for failed messages. If the reason for failed message matches with that of 'primary key violation' or 'data with this primary key not found in the table' then we are changing the verb and sending the data again to the JDBC input queue.
The ESQL extract for doing this is as below:
-----------------------------------------------
-- Database update failure because the data with this primary key is not present
IF InputRoot.MRM.verb = 'Update'
AND POSITION('MsgID: 35030' IN InputRoot.MQRFH2.usr.Description) <> 0
AND POSITION('Mesg: Cannot find the business object '|| InputRoot.MQRFH2.mcd.Type ||' in the database with the key values received.' IN InputRoot.MQRFH2.usr.Description ) <> 0 THEN
SET OutputRoot = InputRoot;
SET OutputRoot.MRM.verb = 'Create';
SET OutputDestinationList.Destination.MQDestinationList.DestinationData.queueName = GetMQQueueName(InputRoot.MQRFH2.mcd.Type);
PROPAGATE TO TERMINAL 'out1';
-- Database insert failure because primary key violation error
ELSEIF InputRoot.MRM.verb = 'Create'
AND POSITION('MsgID: 37004' IN InputRoot.MQRFH2.usr.Description) <> 0
AND POSITION('ORA-00001' IN InputRoot.MQRFH2.usr.Description) <> 0 THEN
SET OutputRoot = InputRoot;
SET OutputRoot.MRM.verb = 'Update';
SET OutputDestinationList.Destination.MQDestinationList.DestinationData.queueName = GetMQQueueName(InputRoot.MQRFH2.mcd.Type);
PROPAGATE TO TERMINAL 'out1';
END IF;
--------------------------------------
Any comments whether this is OK ?
I know that this is not the best way of handling such issue but its working for me.
Also our project is in development phase and there are more and more chances that the database definitions can change in future. So instead of changing/maintaining 250 odd tables/stored procedures, I think this solves our purpose.
Experts comments please.... 
Thanks,
Pravin. |
|
| Back to top |
|
|
| eai_guy |
| Posted: Wed Nov 28, 2007 8:51 pm Post subject: |
|
|
Voyager
Joined: 18 Oct 2007
Posts: 90
|
| Gaya3 wrote: |
Hi eai_guy
But here we are not sure, PravinG is using ICS to do so.
from the performance view, do you think that checking from collaboration logic is feasible. Indeed its giving some load...
Regards
Gayathri |
Hi Gayathri  ,
He is not using ICS  so cant go with collaboration approach......
As far as Insert or update is concerned using collboration will give some load.......
I dont see other way in ICS to have this check done(whether to insert or update)........ Do you foresee any other logic for this ?
I know this question is not relevant on this as the query is for using JDBC with MB..... Just asking out of curiosity
Thanks, |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Thu Nov 29, 2007 2:19 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
Hi
This is fine, but think if there are lakhs of messages failing due to the reason that you stated,
If you are using adapter, hand over any database transaction to Adapter, it will help to function your broker well with high performance. (esp:(Condition Checking etc))
Stored procedures are really powerful, there are lot of options available in that.
As the ESQL looks good, and as you said, its working fine for you. Its better to look forward a bit while designing.
eai_guy:
If you are using collboration and adapter, i suggest the above solution.
hand over to Adapter for functioning with Apps (Condition Checking etc)
Regards
Gayathri  [/u]
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Nov 29, 2007 3:01 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
The performance load for doing a select first should be small... and could be done from MB, by sending a retrieve message first.
It's probably okay to query one table for the primary key, so the retrieve will be fast.
Also, Gaya3... I think eai_guy is handing over all DB actions to adapter - WMB is just checking return message from adapter Update call to see if the error indicates it should send Insert call.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
