Sequential Part Number Generator...with a twist

[Walker, Chris]

A general solution is something like this:

TRANSACTION 1
-------------
This transaction is typically executed at the start of the entry sequence
and allows the number to be displayed at this point.

1) A number is allocated from a counter stored in a system table, and the
counter is incremented.

2) When a number is allocated, a record is stored in an "in use" table,
together with an expiry timestamp (an hour, a day or whatever after the
allocation time), and perhaps the user name.

TRANSACTION 2
-------------
This forms part of the update transaction - ideally it executes as an insert
trigger on the primary record.

3) The allocation record is deleted when the primary record is saved.

Any unused numbers (caused when the entry sequence is aborted) are left in
the allocation list.  There are several ways to deal with these:

a) Periodically run an audit report and delete them.

b) Change step 1 above, so that any expired records are reallocated instead
of incrementing the counter.

Chris Walker
Tate & Lyle
London


> -----Original Message-----
> From: James B. Byrne [mailto:ByrneJB@Harte-Lyne.ca]
> Sent: Friday, February 18, 2000 6:49 PM
> To: Chris Gassett; Powerhouse Forum (E-mail)
> Subject: Re: Sequential Part Number Generator...with a twist
> 
> 
> 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.
> 
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
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.