Key violation for auto increment Primary keys

[Ashish]

Key Violation is constantly coming for the Paradox 7.0 Tables, if an
autoincrement
field is set as the Primary Key . The only way I find to sort this Problem
is to go to
Database Desktop Change that field to Integer and then change it back to
AutoInc.
But I can,t give this instructions to my customers. There is any way to sort
it out and
why this is coming. Please help me..

Thanks in advance
Ashish

[Sundial Services]

It sounds like the "next autoincrement field value" in the table header
is incorrect. Try downloading our ChimneySweep product and running it
against the table. With the AVF feature turned on, it will detect the
maximum value used in the autoincrement field and ensure that the "next
field value" in the header actually exceeds the maximum field-value
found. It'll reset the counter in the header if the value is wrong.

[I make this suggestion quite seriously... no spamming is involved: I'm
not sure that Table Repair even performs this test, but I know for
certain that our product does 'cuz I wrote it.] :-)

Now, you still need to try to figure out -why- this problem is happening
especially if it is happening "regularly." There should be no problems
at all with using autoincrement fields as primary-keys... if there ever
was a reason for having autoincrement fields in the first place, this is
it.

--------------------------------------------------------------------
Sundial Services :: Scottsdale, AZ (USA) :: (480) 946-8259
mailto:in...@sundialservices.com (PGP public key available.)
> Why =shouldn't= it be quick and easy to keep your database online?
> ChimneySweep(R): "Click click, it's fixed!" {tm}
> http://www.sundialservices.com/cs3web.htm

[Steve F (Team B)]

Can you give a little more info on what you are trying to do? When you
create a record with an autoincrement key, the key is not filled in until
you post. Therefore, if you try to use a reference to the record before it
is posted, then you may get such an error as the key will be zero until it
is posted. Could this be your problem or is it something else?

--
Steve F (Team B)
"Ashish" <da...@vsnl.com> wrote in message
news:84tdlb$g6...@bornews.borland.com...

> Key Violation is constantly coming for the Paradox 7.0 Tables, if an
> autoincrement
> field is set as the Primary Key . The only way I find to sort this Problem
> is to go to
> Database Desktop Change that field to Integer and then change it back to
> AutoInc.
> But I can,t give this instructions to my customers. There is any way to
sort
> it out and
> why this is coming. Please help me..
>
>

> Thanks in advance
> Ashish
>
>

[Bill Todd (TeamB)]

This is a symptom of table corruption. If this is a multiuser app make sure
the BDE is configured as described in TI247B at
www.borland.com/devsupport/bde/ti. Other possible causes are system crashes
or network hardware problems.

--
Bill

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

[Roman Krejci]

Hi Ashish,
Pdxrbld utility (free) stored at my website
http://www.rksolution.cz/delphi/delphi.htm
does repair this kind of Paradox table corruption
--
Roman
(please remove 'stopspam' in header when replying)
mail: in...@rksolution.cz
URL: www.rksolution.cz

Ashish <da...@vsnl.com> píąe v diskusním
příspěvku:84tdlb$g6...@bornews.borland.com...

[Ashish]

when the Table is corrupted, I tried adding a record in Database desktop
but there also autoincrement primary key is not updating and gives me key
violation

>> Thanks in advance
>> Ashish
>>
>>
>
>

[Tim Davies]

Ashish <da...@vsnl.com> wrote in message

news:852iam$qb...@bornews.borland.com...

> when the Table is corrupted, I tried adding a record in Database desktop
> but there also autoincrement primary key is not updating and gives me key
> violation

I also had this problem more time than I care to remember until I managed to
get the Session.PrivateDir set correctly at application load time. You might
also find the following code useful. It sets an AutoInc to Integer and back.
The TfmCMSReindex is real the rest is the examples that I found via the
Borland BDE site. You will need to have deleted the promary index, then
opened the table and finally recreated the primary index (on the AutoInc
field) before you do the DbiDoRestructure.

<<<Snip Start>>>
{Example 3: Alter a field in a Paradox or dBASE table.
This example will alter an existing field in a Paradox or dBASE table.
NOTE: You must fill in all options in the ChangeRec with 0 or '' if the
option
is not used in the restructure. FillChar can be used to do this:
Fillchar(MyChangeRec, sizeof(MyChangeRec), 0);
This example uses the following input:

ChangeField(Table1, Table1.FieldByName('FOO'), MyChangeRec)
ChangeRec is defind as follows:
ChangeRec = packed record
szName: DBINAME;
iType: word;
iSubType: word;
iLength: word;
iPrecision: byte;
end;
The function is defined as follows:
}
procedure TfmCMSReindex.FixAutoIncrement(nIndexPtr : integer);
var ChangeRecord : TChangeRec;
begin
try
with ChangeRecord do
begin
szName := 'ITEMNO';
iType := fldINT32;
iSubType := 0;
iLength := 0;
iPrecision := 0;
end;
ChangeField(tblReindex, tblReindex.FieldByName(ChangeRecord.szName) ,
ChangeRecord);
finally
tblReindex.Open;
with ChangeRecord do
begin
szName := 'ITEMNO';
iType := fldINT32;
iSubType := fldstAUTOINC;
iLength := 0;
iPrecision := 0;
end;
ChangeField(tblReindex, tblReindex.FieldByName(ChangeRecord.szName) ,
ChangeRecord);
end;
end;

procedure TfmCMSReindex.ChangeField(Table: TTable; Field: TField; Rec:
TChangeRec);
var
Props: CURProps;
hDb: hDBIDb;
TableDesc: CRTblDesc;
pFields: pFLDDesc;
pOp: pCROpType;
B: byte;

begin
// Initialize the pointers...
// pFields := nil; pOp := nil;
// Make sure the table is open exclusively so we can get the db handle...
if Table.Active = False then
raise EDatabaseError.Create('Table must be opened to restructure');
if Table.Exclusive = False then
raise EDatabaseError.Create('Table must be opened exclusively to
restructure');

Check(DbiSetProp(hDBIObj(Table.Handle), curxltMODE, integer(xltNONE)));
// Get the table properties to determine table type...
Check(DbiGetCursorProps(Table.Handle, Props));
// Make sure the table is either Paradox or dBASE...
if (Props.szTableType <> szPARADOX) and (Props.szTableType <> szDBASE)
then
raise EDatabaseError.Create('Field altering can only occur on Paradox' +
' or dBASE tables');
// Allocate memory for the field descriptor...
pFields := AllocMem(Table.FieldCount * sizeof(FLDDesc));
// Allocate memory for the operation descriptor...
pOp := AllocMem(Table.FieldCount * sizeof(CROpType));
try
// Set the pointer to the index in the operation descriptor to put
// crMODIFY (This means a modification to the record is going to
happen)...
Inc(pOp, Field.Index);
pOp^ := crMODIFY;
Dec(pOp, Field.Index);
// Fill the field descriptor with the existing field information...
Check(DbiGetFieldDescs(Table.Handle, pFields));
// Set the pointer to the index in the field descriptor to make the
// midifications to the field
Inc(pFields, Field.Index);

// If the szName portion of the ChangeRec has something in it, change
it...
if Length(Rec.szName) > 0 then
pFields^.szName := Rec.szName;
// If the iType portion of the ChangeRec has something in it, change
it...
if Rec.iType > 0 then
pFields^.iFldType := Rec.iType;
// If the iSubType portion of the ChangeRec has something in it, change
it...
if Rec.iSubType > 0 then
pFields^.iSubType := Rec.iSubType;
// If the iLength portion of the ChangeRec has something in it, change
it...
if Rec.iLength > 0 then
pFields^.iUnits1 := Rec.iLength;
// If the iPrecision portion of the ChangeRec has something in it,
change it...
if Rec.iPrecision > 0 then
pFields^.iUnits2 := Rec.iPrecision;
Dec(pFields, Field.Index);

for B := 1 to Table.FieldCount do begin
pFields^.iFldNum := B;
Inc(pFields, 1);
end;
Dec(pFields, Table.FieldCount);

// Blank out the structure...
FillChar(TableDesc, sizeof(TableDesc), 0);
// Get the database handle from the table's cursor handle...
Check(DbiGetObjFromObj(hDBIObj(Table.Handle), objDATABASE,
hDBIObj(hDb)));
// Put the table name in the table descriptor...
StrPCopy(TableDesc.szTblName, Table.TableName);
// Put the table type in the table descriptor...
StrPCopy(TableDesc.szTblType, Props.szTableType);
// The following three lines are necessary when doing any field
restructure
// operations on a table...

// Set the field count for the table
TableDesc.iFldCount := Table.FieldCount;
// Link the operation descriptor to the table descriptor...
TableDesc.pecrFldOp := pOp;
// Link the field descriptor to the table descriptor...
TableDesc.pFldDesc := pFields;
// Close the table so the restructure can complete...
Table.Close;
// Call DbiDoRestructure...
Check(DbiDoRestructure(hDb, 1, @TableDesc, nil, nil, nil, FALSE));
finally
if pFields <> nil then
FreeMem(pFields);
if pOp <> nil then
FreeMem(pOp);
end;
end;

<<<Snip End>>>

[Ashish]

This time I can't even open that Corrupted table. I tried re-indexing the
keys but the table is not opening. when i tried rebuild it gives me an error
block size mismatch.

Ashish