Board index » delphi » Autoincrement fields - the journey so far with the light at the end approaching!

Autoincrement fields - the journey so far with the light at the end approaching!

Hello,
It's me again with my auto increment fields.
Thanks to everyone for their advice it was much appreciated.

The story is that the auto increment field in the Paradox table header
(byte 73) was not being updated and since this field was a key field, the
next time you added a record, it would bring up a key violation cos the
next number already existed in the database.

I tried loads of things, switching off Optimistic Locking in NT and doing a
dbiSaveChanges after the post amongst others. All of this was to no avail.

However, I then realised that it was only this one table that was
corrupting time and time again. All the rest were fine. Odd, I thought? It
then occurred to me that this was the biggest table that we had. Aha!
Each record would take up, I believe 85 bytes - assuming $, L, I and D
fields ( and + fields) all take up 1 byte, I'm not sure what they actually
take.. There were a couple of A20's and one A10.
Getting rid of and A10 and an A20 field and reducing the other one to A5
seems to have fixed the problem! Hurrah! ( I have had 4 computers writing
to these tables every second for the past 14 hours and so far no
corruption. It would take a minute or so before.)

So, there in might be the solution - keep your record size as small as
possible!

Does this make sense to people? I guess if the record is fairly large, then
it takes longer to update, more time for interference and hence corruption.
Or does this indicate that I have some setting wrong, some buffer size or
something?

So, keeping records to about 60 bytes or so is essential for multi user
paradox environments. (If someone can tell me the exact sizes these fields
types are I'll be able to do an exact calculation of the maximum record
size. I'm too elated to look them up myself.)

Have fun,

Stephen

 

Re:Autoincrement fields - the journey so far with the light at the end approaching!


I did run this table through all sorts of table rebuild programs (Paradox 7
repair, TUtility and one other I think) every time the field got corrupted.

Would this not have rebuilt the hosed index? I'll try a test, put the
fields back in and see if it's ok.

Steve Green <gre...@diamondsg.com> wrote in article
<36C76C37.173F1...@diamondsg.com>...

Quote
> There is no direct correlation between any specific record size and
corruption,
> or record size and network performance.. using an autoincrement field in
itself
> can lead to many more problems than making the table narrower.. in your
case,
> you most likely had a hosed index that was blown away and rebuilt when
you did
> the restructure..

Re:Autoincrement fields - the journey so far with the light at the end approaching!


Quote
Stephen wrote:
> I did run this table through all sorts of table rebuild programs (Paradox 7
> repair, TUtility and one other I think) every time the field got corrupted.

As usual, I *must* point out something that remains "un-obvious" to most
people.. the table repair utility is *not* a guarantee..

--
Steve Green
Corel CTech - Paradox
Diamond Software Group, Inc.
Waldorf, Maryland  USA

http://www.diamondsg.com    <- please note our new web site
Gre...@DiamondSG.com        <- and my changed e-mail address

Re:Autoincrement fields - the journey so far with the light at the end approaching!


Re:Autoincrement fields - the journey so far with the light at the end approaching!


Quote
Steve Green wrote:

> RCarmichel wrote:

> > BTW - did you try ChimneySweep for the table repair?

> I can count on my fingers the number of damaged tables I've had to deal with over
> the years..

Lucky you.  ;-)

I suspect that this is just an unusual variation of the old problem
where, e.g. in a Delphi application, one page of the database did not
get updated along with the rest.  In this case it was the file-header
page (block #0).  And the nature of the change was to lose the
autoincrement-value.  Presumably a block was neither inserted nor
removed by the update that consumed the autoincrement-value, so the
header continued to be structurally good while the value was not.

In specific reference to ChimneySweep, autoincrement-value corrections
are a new addition to the repairs that we can do.  I actually don't know
if the interim-update that includes this fix has been uploaded to our
web-site yet.  If not it soon will be. (This -is- the Internet, after
all, and we update the download-file on our site very frequently if the
need arises.)

Re:Autoincrement fields - the journey so far with the light at the end approaching!


Re:Autoincrement fields - the journey so far with the light at the end approaching!


Below is the basics of the source code for the program that I am using to
repair the Autoinc field. (The assumtion is made that the index is on the
autoinc field.)

Is there anything wrong with the code? (Apart from the fact that it was
done in a hurry.)

It works, but I guess could cause things to go wrong as it is only updating
the one area of the header which might cause problems. (The danger caused
by wading in to something one doesn't understand.)

I spark of lighting has just hit me and made me wonder if it is by
repairing the table in this way that might be causing the records to go
missing. Is that possible?

ps. Apologies for going on about this problem. One may detect the hint of
desparation in my typing.

====================

const
    cStartByte = 73;  

implementation

{$R *.DFM}

procedure TfrmRepair.btnFixClick(Sender: TObject);
var
    lLastNo : LongInt;
    lTableAutoInc : LongInt;
begin
    ShowStatus('Reading table ...');
    lLastNo := getAutoInc(edTable.text);
    lTableAutoInc := getTableAutoInc(edTable.text, edAutoIncField.text);

    edLastIndex.text := IntToStr(lLastNo);
    edTableAutoInc.text := IntToStr(lTableAutoInc);

    if lLastNo = lTableAutoInc then
        ShowStatus('Table ok')
    else
    begin
        // Need to fix the table
        ShowStatus('Fixing table ...');
        if SetAutoInc(edTable.text, lTableAutoInc) then
            ShowStatus('Table fixed');
    end;
end;

function TfrmRepair.getAutoInc(filename : string) : LongInt;
var
   mystream : tfilestream;
   buffer : longint;
begin
     mystream := tfilestream.create(filename,
                 fmOpenread + fmShareDenyWrite);
     mystream.Seek(cStartByte, soFromBeginning);
     mystream.readbuffer(buffer, 4);
     mystream.Free;
     getAutoInc := buffer;
end;

function TfrmRepair.getTableAutoInc(stable, sAutoIncField : string) :
LongInt;
var
    tblTable : TTable;
begin
    tblTable := TTable.create(nil);

    tblTable.tablename := stable;
    tblTable.active := True;

    tblTable.last;

    result := tblTable.fieldbyname(sAutoIncField).AsInteger;
end;

function TfrmRepair.SetAutoInc(filename : string; lAutoInc : LongInt) :
boolean;
var
   mystream : tfilestream;
   bRet : boolean;
begin
    bRet := False;
    mystream := nil;

    try
        mystream := tfilestream.create(filename,
                     fmOpenReadWrite + fmShareDenyNone);
        mystream.Seek(cStartByte, soFromBeginning);
        mystream.writebuffer(lAutoInc, 4);
        bRet := True;
    except
        on e: exception do
            ShowStatus(e.message);
    end;

    if assigned(mystream) then
        mystream.Free;

    result := bRet;
end;

procedure TfrmRepair.ShowStatus(sStatus : string);
begin
    lblStatus.caption := sStatus;
    Application.ProcessMessages;
end;            

end.

Re:Autoincrement fields - the journey so far with the light at the end approaching!


Quote
Stephen wrote:
> Below is the basics of the source code for the program that I am using to
> repair the Autoinc field. (The assumtion is made that the index is on the
> autoinc field.)

> It works, but I guess could cause things to go wrong as it is only updating
> the one area of the header which might cause problems. (The danger caused
> by wading in to something one doesn't understand.)

Just to keep the record straight.. any operation that you use that directly
changes the table structure itself is *not* supported or condoned in any
manner..

--
Steve Green
Corel CTech - Paradox
Diamond Software Group, Inc.
Waldorf, Maryland  USA

http://www.diamondsg.com    <- please note our new web site
Gre...@DiamondSG.com        <- and my changed e-mail address

Re:Autoincrement fields - the journey so far with the light at the end approaching!


Re:Autoincrement fields - the journey so far with the light at the end approaching!


Quote
> > I can count on my fingers the number of damaged tables I've had to deal
with over
> > the years..

> Lucky you.  ;-)

I seem to run out of fingers on a daily basis.

Quote

> I suspect that this is just an unusual variation of the old problem
> where, e.g. in a Delphi application, one page of the database did not
> get updated along with the rest.  In this case it was the file-header
> page (block #0).  And the nature of the change was to lose the
> autoincrement-value.  Presumably a block was neither inserted nor
> removed by the update that consumed the autoincrement-value, so the
> header continued to be structurally good while the value was not.

Was there any solution to this problem?

Other Threads