I gave Paradox autoincrement fields one more try for a log file that only
has one writer task.

After running a while, I get a "Key violation" exception when the program
trys to execute a Table.Insert.  A fix that has worked so far has to been to
delete the last record written.

Should I program the deletion fix in or should I give up on autoincrement
and go back to an integer field and and determine the next index value by
doing a query to find the highest index value already in the table?

TIA, for your ideas, John H

Your table may be corrupt. You may want to verify and rebuild it using the
table repair utility at www.borland.com/devsupport/bde/utilities.html. If
you decide to use an integer field do not use a query. It is both slow and,
in a multiuser environment, unsafe. Instead, use a single field single
record table to hold the last used value and the following function to get
the next value.

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; file://if
    {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; file://if
          end;
      end; {try}
    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;

Bill

--
Bill Todd (TeamB)
(TeamB cannot respond to questions received via email)

Steve, Thank you for trying.  Please find my responses to your ideas
below.  Thanks, John H

Steve Fischkoff wrote ...

The only unique index is the single primary key consisting of the
autoincrement field.

There is no network involved.

Well maybe!  I need to check to see if my writes to this "Log"
routine are protected from reentrancy.  Thanks again.

Bill, Thanks for the ideas.  My comments below.  John H

www.borland.com/devsupport/bde/utilities.html. If

Why is a select max() on a primary index field slow?

And why is it unsafe?  In a repeat loop, I have put a fetch max,
append, set values, and try post except ... cancel until ok
and never had a problem (that I knew about).

Thanks for the function code, I will save and look at it.  Regards.

On Wed, 10 Nov 1999 15:50:52 -0600, "John Herbster"

You shouldn't have to do this type of deletion unless something is
wrong. First, what is the structure of the index which is giving you
the key violation? Is it possible that some other factor is creating a
duplicate key? The other thing that can happen (should only be rare!)
is that the Paradox file loses track of the last autoincrement number
used. This problem does fix with deletion of the last record. However,
it is usually a sign of some other problem such as a network problem
or a programming problem, especially if it happens often. Is there
something else wrong that you might not think is related but might
give a clue?

Steve F (Team B)

it's "unsafe" because the value could change during/after the query before you
use it.  this could easily happen in a multi-user environment.   since you're
without a network i gather you're in a single user environment with only 1
process/thread so you're probably ok.  (unless it's "unsafe" for another
reason i haven't thought of).

it's easier and safer just to use a single record table to store the value,
just like if it were a variable in memory.  we've done it for a couple of our
apps (single record in a table), and now that we're making these apps
multi-user capable it's one less concern.  as a habit i write all my code as
if it will one day be used for a multi-user environment.

dave

David Beardwood wrote

Of course it can change, David, but won't the database give an error on
attempt to post?  My normal scheme would use that exception to increment the
key and try again.  So where is the problem?  Infinite loop or corrupted
table -- which?  Thanks, John H

Slow is relative and how slow depends on the size of the table. A select Max
on the primary key still requires the primary index to be read across the
network. It is unsafe in a multi-user environment because it is quite
possible for two users to select max at the same time and get the same
value. Whoever posts second will get a key violation error.

Bill

Bill Todd (TeamB)
(TeamB cannot respond to questions received via email)

Bill Todd (TeamB) wrote ...

Bill, I am sorry that I did not better explain the problem in the first
post.
I understand getting a "Key violation" on an attempt to Post a record
with what would be a duplicate key.  So I do a cancel and change the
key and try to Post again. But what what I was seeing was a corrupted
table that would give the "Key violation" by any attempt execute the
Append or Insert statements before any data was assigned or any Post
was attempted.  Thanks for trying to help explain things. I have
developed the appropriate work-arounds for now.  Regards, John H

so you don't think you're nuts, i have a table with exactly the same
problem, which i fix in exactly the same way.  repacking does not seem to
help, even once i delete the last record.  using 5.01, hoping 5.1 will fix
it.  i am going to try tutil.

best,
gregg

Gregg R. Zegarelli <g...@zegarelli.com> wrote in message

Gregg, My problem was with an activity logging table.  I think that it might
have been possible to attempt a second Append before the first Post.  Let me
know if this is your problem!  I put reentrancy preventers and exception
trapping around the log routines and kept the autoincrement index field as
primary key. (I don't want logging problems to bring down the system.)  I
haven't had any problems yet, where as before they were every day.
Regards, John H