I have tested this method using several computers hitting this code in a
continuous while loop for several minutes. All computers inserted the
numbers they received into a table. At the end of the test, I tested the
table to see if any duplicates were generated. There were none. I have
used this method ever since. The secret is trying to update your source
number table with the next number where the number is the one you just got.
There is no way for two people to validate the same number. Two might get
the same number from the table but only one will be able to update to the
next number. I've updated the routine below to use pure SQL. No error
variable is needed. I've added sample code to show how to avoid missing
numbers.
Label TryAgain
SET VARIABLE vNextID INTEGER = NULL
WHILE vNextID IS NULL THEN
SELECT RowNum INTO vNextID FROM CacheNumb WHERE LIMIT = 1
IF SQLCODE <> 0 THEN
BREAK
ENDIF
DELETE FROM CacheNumb WHERE RowNum = .vNextID
IF SQLCODE <> 0 THEN
BREAK
ENDIF
SET VAR vNextID = NULL
ENDW
WHILE vNextID IS NULL THEN
SELECT NewRowNumb +
INTO vNextID +
FROM NextRowNumber
UPDATE NextRowNumber SET +
NextRowNumb = (NextRowNumb + 1) +
WHERE NextRowNumb = .vNextID
IF SQLCODE = 0 THEN
BREAK
ENDIF
ENDW
--DOUBLE CHECK THAT NO DUPLICATE WAS GENERATED IN ERROR -- KILL MURPHY'S LAW
SELECT COUNT(*) INTO vCount FROM tablename WHERE ID = .vNextID AND LIMIT = 1
IF vCount > 0 THEN
GOTO TryAgain
ENDIF
-- form defaults ID to vNextID
ENTER USING formname for one row
-- Cache unused numbers
SELECT COUNT(*) INTO vCount FROM tablename WHERE ID = .vNextID AND LIMIT = 1
IF vCount = 0 THEN -- ROW WAS DELETED
INSERT INTO CacheNumb (RowNum) VALUES (.vNextID)
ENDIF
-- Dennis McGrath
mailto:[EMAIL PROTECTED]
-- Productivity Tools for R:Base Programmers
http://www.enteract.com/~mcgrath/dennis
-- Full time consultant with:
SQL Resources Group
Steve Hartmann
Oak Park, IL
mailto:[EMAIL PROTECTED]
