Author: Tomas Rutkauskas

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.

1   function dgGetUniqueNumber(LastNumberTbl: TTable): LongInt;
2   {Gets the next value from a one field one record table which stores the last used
3   value in its first field. The parameter LastNumberTbl is the table that contains 
4   the last used number.}
5   const
6     ntMaxTries = 100;
7   var
8     I, WaitCount, Tries: Integer;
9     RecordLocked: Boolean;
10    ErrorMsg: string;
11  begin
12    Result := 0;
13    Tries := 0;
14    with LastNumberTbl do
15    begin
16      {Make sure the table contains a record. If not, add one and set the first field 
17  to zero.}
18      if RecordCount = 0 then
19      begin
20        Insert;
21        Fields[0].AsInteger := 0;
22        Post;
23      end;
24      {Try to put the table that holds the last used number into edit mode. If 
25  calling Edit
26      raises an exception wait a random period and try again.}
27      Randomize;
28      while Tries < ntMaxTries do
29      try
30        Inc(Tries);
31        Edit;
32        Break;
33      except
34        on E: EDBEngineError do
35          {The call to Edit failed because the record could not be locked.}
36        begin
37          {See if the lock failed because the record is locked by another user.}
38          RecordLocked := False;
39          for I := 0 to Pred(E.ErrorCount) do
40            if E.Errors[I].ErrorCode = 10241 then
41              RecordLocked := True;
42          if RecordLocked then
43          begin
44            {Wait for a random period and try again.}
45            WaitCount := Random(20);
46            for I := 1 to WaitCount do
47              Application.ProcessMessages;
48            Continue;
49          end
50          else
51          begin
52            {The record lock failed for some reason other than another user has the
53            record locked. Display the BDE error stack and exit.}
54            ErrorMsg := '';
55            for I := 0 to Pred(E.ErrorCount) do
56              ErrorMsg := ErrorMsg + E.Errors[I].message + ' (' + 
57  IntToStr(E.Errors[I].ErrorCode) + '). ';
58            MessageDlg(ErrorMsg, mtError, [mbOK], 0);
59            Exit;
60          end;
61        end;
62      end;
63      if State = dsEdit then
64      begin
65        Result := Fields[0].AsInteger + 1;
66        Fields[0].AsInteger := Result;
67        Post;
68      end
69      else
70        {If the record could not be locked after the specified number of tries raise 
71  an exception.}
72        raise Exception.Create('Cannot get next unique number. (dgGetUniqueNumber)');
73    end;
74  end;