Hello,

a simple SQL-Update like "Update LAGER set QTY=0 where QTY<0" raise error 5035 if there are record locked in ADSTable.

How to ignore (skip) this lock(s) within the Update-Statement and continue with the next record?

Regards Juergen

asked 27 Mar '14, 08:09

Joe%20Walk's gravatar image

Joe Walk
31337
accept rate: 0%


This is only possible using a script.

link

answered 27 Mar '14, 09:34

Joachim%20Duerr's gravatar image

Joachim Duerr
8.7k1031133
accept rate: 18%

Based on my experience I would say that ADS updates all the non-locked rows and only throws the above message at the end. So it actually skips / ignores the locked ones as you asked.

link

answered 27 Mar '14, 13:56

Stephan%20Leiwering's gravatar image

Stephan Leiw...
2.1k173362
accept rate: 19%

I try to catch the exception:

try Update lager set qty=0; catch ADS_SCRIPT_EXCEPTION If __errcode <> 5035 then RAISE; end if; end try;

Error 5035 do not raise, however the script stops at the locked record. How is it possible to skip the locked record and continue with the next record?

link

answered 28 Mar '14, 08:09

Joe%20Walk's gravatar image

Joe Walk
31337
accept rate: 0%

Declare tb cursor as select rowid from lager where qty<0;
Open tb;
While fetch tb do
  Try
    Update lager set qty=0 where rowid=tb.rowid and qty<0;
  Catch all
  End try;
End while;
Close tb;
link

answered 28 Mar '14, 09:29

Joachim%20Duerr's gravatar image

Joachim Duerr
8.7k1031133
accept rate: 18%

Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×325
×271
×154

Asked: 27 Mar '14, 08:09

Seen: 2,431 times

Last updated: 28 Mar '14, 09:29

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.