SAP XI JDBC update SQL statement

[Former Member]

Hi,

I am new to JDBC to RFC configuration and is currently on a deadlock.

We are running a real time JDBC update statement where records are created based on employee login. We have configured the sender adapter to retrieved 5 records for every 5 seconds using the SQL query below:

select *  from dtable where  uploaded=false order by recordno asc limit 5;

and update using:

update dtable set uploaded=true where uploaded=false order by recordno asc limit 5;

The problem is that when there are only 3 records retrieved in the selection, the update statement always update 5 records back. This makes records created in-between the select and update to be updated as well.

Can anyone suggest the correct update statement? Can I use the record numbers retrieved from the first statement in the update?

Thanks,

Ryan

[Former Member · Accepted solution]

Hi,

Thanks for your inputs. I have solved this by setting the JDBC sender comm channel Isolation level to Serializable. And after days of monitoring the issue stop occuring.

Regards,

Ryan

[baskar_gopalakrishnan2]

Few cents.... First of all why do you need order by clause for update. You are not populating that data any where for other system. I would recommend to use sql statement as needed.  So the simplest answer is correct the update system as below.... Your select query remains still same.

update dtable set uploaded=true where uploaded=false

[anupam_ghosh2]

Hi Ryan,

             Could you please try this SQL statement for update instead of two statements.

update dtable set uploaded=true where  field1 in (select field1 from dbtable where uploaded=false order by recordno asc limit 5);

Here I am not selecting all fields but only one table column say "field1". For best results please ensure that "field1" is the primary key for dbtable.

This way you can prevent updation of records created in-between the select and update statement.

Please try this and lets hope problem resolves.

Regards

Anupam

[MichalKrawczyk]

Hi,

>>>>Can anyone suggest the correct update statement?

1. you have a few choices but one of them would be to use a stored procedure in the select statement (which would have both select and update statements)

2.

a) select *  from dtable where  uploaded=false order by recordno

b) update dtable set uploaded=true where uploaded=false

c) split them by 5 rows in the mapping (multimapping)

Regards,

Michal Krawczyk