JDBC UPDATE statement issue

[former_member460664]

Dear All,

I have developed many scenarios from Database to SAP ( JDBC to ABAP proxies). I have used JDBC adapter at sender side. I have put 1 sec as poll interval in Communication channel. I have used SELECT and UPDATE statements as per the requirement. But I have been facing problem while updating the records in database. UPDATE statement updates more records than records fetched by the SELECT statement. In my case, one customer has submitted 104 records at a time. For 1st poll, SELECT ( Where FLAG=0) statement has taken 23 records  and UPDATE ( SET FLAG=1 where FLAG=0) statement has updated 24 records. Next poll, SELECT statement has taken 79 records and UPDATE statement has updated 80 records. So, Finally two records have been skipped for processing. How to write UPDATE statement so that it should only update records that are fetched by SELECT statement?

Regards,

Ashok.

[former_member184720]

Hi Ashok - You will find many threads on the same topic and AFAIK having a such less interval frequency to process the records from database any of the setting would address your issues. Of course you can give a try

I personally feel that stored procedure is the best option which will avoid such issues.

[Former Member]

Hi Ashok

Make sure you have the following setting in your JDBC sender adapter

Isolation level for transaction is set to repeatable_read or serializable.

Disconnect from Database After Processing Each Message

[former_member460664]

Hi Indrajit,

As you said I have made changes. But still UPDATE statement updates more records than records fetched by SELECT statement. Is there any solution plz let me know...

Regards,

Ashok.

[Former Member]

Hi Ashok

Try to set the Isolation level for transaction is set to repeatable_read.

Also increase the poll interval to 10 sec.

[former_member184720]

Hi Ashok - None of the setting should address the issue. Simple logic - the select&update queries executed separately n i dont think select query will lock the table.

As i mentioned earlier, you can find many blogs on the same topic and everything ends up at one suggesstion i.e. stored procedure.