I avoid table locks like the plaugue.
Here is a routine that assumes a one row table with a column containing
the next number.
SET VAR vNextNum INT
WHILE #PI IS NOT NULL THEN
SELECT NumColumn INTO vNextNum FROM OneRowTable
UPDATE OneRowTable SET NumColumn = (.vNextNum + 1) +
WHERE NumColumn = .vNextNum
IF SQLCODE = 0 THEN
BREAK
ENDIF
ENDW
I've tested this in a multiuser environment with many fast computers
running this code at the same time and never get a duplicate.
One could retrieve a number from a table of cached deleted numbers in a
similar manner.
SET VAR vNextNum INT
WHILE #P IS NOT NULL THEN
SELECT CachedNum INTO vNextNum FROM CachedTable WHERE LIMIT = 1
if sqlcode <> 0 then -- no numbers left
BREAK
ENDIF
DELETE FROM CachedTable WHERE CachedNum = .vNextNum
IF SQLCODE = 0 THEN -- Success
BREAK
ENDIF
ENDW
If the variable is null then the routine failed and you need to use the
first routine to get a number.
I believe these routines work so well because of the inherent row lock
imposed by RBase automatically.
KISS
Dennis McGrath
--- axelrod parts <[EMAIL PROTECTED]> wrote:
> Why not just set up a table with the begging #,lock the table when
> doing
> its thing add 1 to the # update it and unlock the table?
> ----- Original Message -----
> From: "Steve Sweeney" <[EMAIL PROTECTED]>
> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]>
> Sent: Wednesday, June 09, 2004 8:46 AM
> Subject: [RBASE-L] - Re: Consecutive numbering
>
>
> > The easiest approach is to pre-populate the table with data. That
> way you
> > can have absolute control over consecutively numbered PK values.
> When a
> > user wants to add (claim) the next available row UPDATE a flag
> field
> (which
> > has a DEFAULT value "New") with the user's login id. Then, if the
> user
> > successfully updated their user login id, you can UPDATE the
> remaining
> > columns of the table and set the flag column to "Done"..
> >
> > Steve
> >
> > ----- Original Message -----
> > From: "Lawrence Lustig" <[EMAIL PROTECTED]>
> > To: "RBASE-L Mailing List" <[EMAIL PROTECTED]>
> > Sent: Monday, June 07, 2004 11:45 AM
> > Subject: [RBASE-L] - Consecutive numbering
> >
> >
> > > Usually I just use autonumber columns and don't worry
> > > about missing values (in the event a user deletes a
> > > row). Now I need to write an application that
> > > consecutively numbers records (no gaps in the
> > > sequence). I also have an autonumber field to use as
> > > the PK.
> > >
> > > My inclination is to handle the numbering in an ON
> > > AFERT INSERT trigger, using the NEXT function against
> > > a "dummy" autonumber table to get the value.
> > >
> > > Does anyone have any thoughts or other suggestions on
> > > how to handle this task?
> > > --
> > > Larry
> > >
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Tired of spam? Yahoo! Mail has the best spam protection around
> > > http://mail.yahoo.com
> > >
> > >
> >
>
