JDBC Sender select/update problem

[Former Member]

Hi all,

we have a serious problem. We are running the scenario JDBC>XI>RFC. In the JDBC sender adapter we do a select statement (select * from table where flag='0') and an update statement (update * from table set flag ='1' where flag='0'). Now, the problem is that when we do the select statement a third party system may insert data in the database, so the update statement updates MORE rows than the select statement has selected.

Does anyone knows a solution (or a workaround).

TIA

[Former Member]

Hi Peter ,

It is better you use a seperate saging table for XI adapter. You can place insert/update triggers on your actual table to move data to the staging table used by XI JDBC adapter. This could avoid the problem you are facing. well this is not a solution , rather an approach you can consider. Other mature eai tools have adapters that will generate these triggers & metadata for you , but here in XI we need to do manually thoe.

Regards

Saravana

[Former Member]

Hi guys,

would this be a possible solution:

-My select statement remains the same, in my update statement I put <test>.

- I create a module which reads the selected key values and in my module I update the sql server table via the received key values.

Its some coding work but maybe this is a solution?

[Former Member]

hi,

use jdbc lookup inside the message mapping to fetch rows and update(create transaction for selection and updation so that database gets locked till transaction is over).

this might help.

thanks and regards

vishal

[udo_martens]

Hi Peter,

The send step is transactional, so, if a third party system just post data in the moment, the JDBC adapter has made the select but not the update, the third party update has to wait. The new sets will have the flag "0".

Conclusion: Do nothing, everythink is fine

Regards,

Udo

[Former Member]

Hi Udo,

I had the same thoughts as you, but in reality it doesn't. We did multiple tests, and concluded this problem. Perhaps we have to place a note than?

[udo_martens]

Hi Peter,

Hmmm, you destroy my view of the world

Plz be so kind to inform me (us) about ur call.

Regards,

Udo

[bhavesh_kantilal]

Hi peter,

can you check the transcation isolation level of your Database?

one option would be to make the database transaction such that the write transaction gets locked when even the READ is being done. So, when your JDBC is trying to select the data from the database, the WRITE will get locked and so, no entries will get selected and then, UPDATE will get executed.

Though I havent tried this, wouldnt it be possible?

Also, go through the help documentation and check the Adavanced Setting and the Transaction Isolation and see if any of the XI options can also solve your problem.

Regards,

Bhavesh

[Former Member]

Hi all,

it seems, at first sight, that the issue is solved via the additional parameters. By setting it to serializable...

[Former Member]

HI Peter,

Instead of " update * from table set flag ='1' where flag='0') " . give as

<b> " update tablename set flag ='1' where flag='0') "</b>

where flag is field name.

regards

mahesh

[Former Member]

Sorry Mahesh, it was a typo error: we already use this syntax.

So our select statement is:

select * from table where flag ='0'

Our update statement is:

update table set flag='1' where flag='0'