Skip to Content

JDBC UPDATE statement issue

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 06, 2014 at 06:11 AM

    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




    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

  • Posted on Jan 06, 2014 at 05:06 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.