Update- statement in JDBC sender Adapter

[Former Member]

Hi ,

I have a requirement where I am trying to fetch data from ODS based on some flag and immediately I want to update the flag status . So I used selected and update in JDBC sender adapter .

But I have one question , in the small timeframe where XI has done a select and just before XI could issue the update statement if a new record is added to ODS . Then that new record will also get updated though it wasnot selected in select statement .

Can someone let me know how to overcome this problem ; as I want to make sure I update only those recrods which are selected in select statement ??

Thanks & Regards,

Suvarna

[former_member431549]

I would suggest using a stored procedure. I opened a message with SAP and was told the view of the database woudl be the same between select and update, but found that not to be the case when the polling interval was short.

Not 100% sure why it wasn't working (updating records that had not been selected), but using a stored procedure works perfectly.

[Former Member]

Hi,

When ever U wanna use SELECT and UPDATE statements at the same channel, give the Polling intervel for maximum time. or at least 90 sec to 120 sec. because if the volume of data is more, then the adapter will have the performance issue or the operations in database side may be ambiguity.

try to give poll interval for litel long time.

From.,

Yallabandi

[Former Member]

Hi,

Check out the following thread. It gives the exact answer to this question.

One of the replies in the above thread:

I put in an OSS note asking this exact question with your specific example, and got a reply. I was referred to OSS note 0000831162, where it is explained very unambiguously that the SELECT and UPDATE queries are run in the same transaction.

Regards,

P.Venkat

Message was edited by:

Venkataramanan

[Former Member]

Hi Venkat ,

Thanks a lot for your reply . That's the solution I was looking for .

Thanks & Regards,

Suvarna

[Former Member]

Hi Venkat ,

I tried using select for update but it is not working .

Do you know any reason why JDBC adapter is not accepting it .

Thanks & Regards,

Suvarna

[moorthy]

Hi,

setting up Transaction Isolation Level may help u

more~ http://help.sap.com/saphelp_nw2004s/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

Regards,

Moorthy

[Former Member]

Hi suvarna,

If i get u correctly you tried giving a 'select' statement in 'update' place right. This cannot happen. Only an insert, update or delete statement can be given in that field.

I think this is not possible because a 'select' statement returns a set of results and there is no place to hold the selected results for the second time.

However, as far as i know, such a case of giving 'select' for second time doesnt really arise in a real world scenario.

Hope this clears your doubt.

Regards,

P.Venkat