Re: Select for update Question

> That may be because there is no guarantee of the ordering of your returned
> records.

That makes sense - I just didn't think of it. Doh!

> Maybe you need to re-initialise your variables before executing the
> select?

I'm not sure what variables I could re-initialise here as the select
statement copies the current values INTO the two variables: vWDResourceID
and vImageStateID.

Thanks for your feedback.

John

Paul Thomas said:
>
> On 10/12/2003 09:10 John Sidney-Woollett wrote:
>> [snip]
>>
>> Actually from my tests, the simple code below *sometimes* blocks on the
>> locked row, and sometimes skips the locked row, and returns the next
>> unlocked row.
>
> That may be because there is no guarantee of the ordering of your returned
> records.
>
>> [snip]
>> LESS SIMPLE, AND DOES NOT WORK AS EXPECTED
>>
>> In this example, I tried placing the select into a loop so that it would
>> keep trying to locate a matching record until either none were
>> available,
>> or it got hold of a matching record.
>>
>> The problem is that the select fails to return the correct results after
>> the first iteration through the loop - it's almost as if it is not
>> refiring the query, but only applying the where clause against the
>> previously found resultset record(s) (which is not what I want).
>>
>> while true loop
>> -- locate the first (unlocked?) ImageHeader awaiting processing
>> -- it will block here
>> select WDResourceID, WPImageStateID
>> into vWDResourceID, vImageStateID
>> from WPImageHeader
>> where WPImageStateID = 1
>> for update
>> limit 1;
>>
>> if (vWDResourceID is null) then
>> -- no record was available, so exit
>>
>> -- check if there are any more records that might be
>> -- available, if we run round the loop again
>> select count(*) into vCount
>> from WPImageHeader
>> where WPImageStateID = 1;
>>
>> if (vCount = 0) then
>> -- if there are not, then abort
>> return -2;
>> end if;
>> else
>> -- see if this record (that *may* have been locked)
>> -- is one that meets our criteria
>> -- if it is then leave the loop
>> if (vImageStateID = 1) then
>> exit;
>> end if;
>> end if;
>>
>> -- safety measure to make sure we do leave the loop
>> -- at some point, vAbortCounter initially assigned
>> -- a value of 10
>> vAbortCounter := vAbortCounter - 1;
>> if (vAbortCounter < 0) then
>> return -3;
>> end if;
>> end loop;
>>
>> Maybe someone can explain what the problem is with the second version -
>> I've puzzled it a bit, but am none the wiser.
>
> Maybe you need to re-initialise your variables before executing the
> select?
>
> --
> Paul Thomas
> +------------------------------+---------------------------------------------+
> | Thomas Micro Systems Limited | Software Solutions for the Smaller
> Business |
> | Computer Consultants |
> http://www.thomas-micro-systems-ltd.co.uk |
> +------------------------------+---------------------------------------------+
>