Sequential Part Number Generator...with a twist

[James B. Byrne]

On 18 Feb 00, at 12:00, Chris Gassett wrote:

> My problem is if two people are accesssing this screen at one time
> two or more users can grab the same sequentially generated number. 
> What I need to do is do commit the record after the second field of
> three characters is passed.  If anyone knows of something remotely
> close to what I am attempting to do I would greatly appreciate the
> feedback. 

This is a common problem with distributed database applications.  The 
only reliable method that I have ever come up with involves holding 
the original source value in a program variable and then during the 
update process, lock the recordset, re-read the source value, compare 
the old source to the current source, calculate a delta, apply the 
delta to the local assigned part number, commit the part-number, 
commit the source, unlock the recordset.

So say we have a table file that has a record indexed by "next part 
number", and a value containing the next part number. A program that 
wishes to reliably keep that number valid, and prevent duplicate part 
numbers from being created, while maintaining an acceptable level of 
user access needs to do something like this:

Start Screen

Get and display next number. 

Store next number in local variable 
(or use OLDVALUE but I have had problems with this in the past, I 
prefer to control the value that I am using myself)

accept client input

accept update request

apply local edits

begin update procedure

lock record set (includes system table)

read next number

let delta = current next number - old next number

If delta eq 0 then
 commit part record

else
  our part number = our part number + delta
  commit (PUT) part record
	WARN = "Part number changed."
end

 next number = current next number + 1
 commit (PUT) systable

unlock all

display our part number

end update


To make this work you will need to write your own update procedure, 
and possibly a pre-update procedure. You may need to declare various 
files as "DESIGNER" and you may need a "NEED ALL" qualifier on some 
of them as well.


Regards,
Jim
--
James B. Byrne                      ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited                http://www.harte-lyne.ca
9 Brockley Drive       
Hamilton, Ontario                   fax:+1 905 561 0757
Canada    L8E 3C3                   vox:+1 905 561 1241
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.