How to create unique numbers for a primary index field (Views: 708)
Problem/Question/Abstract: Using D4, Paradox 7 and a peer-to-peer network on Win95/ 98, I am currently thinking about the problems of using AutoInc fields as primary indexes to avoid key violations. On balance, I feel that it is probably best to avoid potential problems by choosing an alternative primary index system. But what are the alternatives? Using a DateTime field as the unique primary index or use a number that is incremented in code? Answer: If you need a unique number for a primary key create a single-field-single-record table to hold the last used value and call the following function when you need a new number. function dgGetUniqueNumber(LastNumberTbl: TTable): LongInt; {Gets the next value from a one field one record table which stores the last used value in its first field. The parameter LastNumberTbl is the table that contains the last used number.} const ntMaxTries = 100; var I, WaitCount, Tries: Integer; RecordLocked: Boolean; ErrorMsg: string; begin Result := 0; Tries := 0; with LastNumberTbl do begin {Make sure the table contains a record. If not, add one and set the first field to zero.} if RecordCount = 0 then begin Insert; Fields[0].AsInteger := 0; Post; end; {Try to put the table that holds the last used number into edit mode. If calling Edit raises an exception wait a random period and try again.} Randomize; while Tries < ntMaxTries do try Inc(Tries); Edit; Break; except on E: EDBEngineError do {The call to Edit failed because the record could not be locked.} begin {See if the lock failed because the record is locked by another user.} RecordLocked := False; for I := 0 to Pred(E.ErrorCount) do if E.Errors[I].ErrorCode = 10241 then RecordLocked := True; if RecordLocked then begin {Wait for a random period and try again.} WaitCount := Random(20); for I := 1 to WaitCount do Application.ProcessMessages; Continue; end else begin {The record lock failed for some reason other than another user has the record locked. Display the BDE error stack and exit.} ErrorMsg := ''; for I := 0 to Pred(E.ErrorCount) do ErrorMsg := ErrorMsg + E.Errors[I].Message + ' (' + IntToStr(E.Errors[I].ErrorCode) + '). '; MessageDlg(ErrorMsg, mtError, [mbOK], 0); Exit; end; end; end; if State = dsEdit then begin Result := Fields[0].AsInteger + 1; Fields[0].AsInteger := Result; Post; end else {If the record could not be locked after the specified number of tries raise an exception.} raise Exception.Create('Cannot get next unique number. (dgGetUniqueNumber)'); end; end; |