SOLVED---TDbf - Is there anyway to change PhysicalRecNo

[KieferThomas]

Hello Again!,

And thank you all for your responses!

I came across a stupid, stupid error on my part! When I exported all of my records out from a different version of my program which was Xcode by the way and I hate it! But that is a different story.

I used Open Office Calc to generated my new Dbf file from the CSV export. Well, I come to find out Open Office Calc automatically sticks in a self updating auto include for indexing which TDBf just automatically accepts and continues updating the index no questions asked!

By actually finding a freeware program I was able to get rid of the index which I actually did not want in the first place and my dbf file was generated in the way I wanted as I was not wishing to use a self generated index as WP was kind enough to give me the example & I remember using this type of formulae else where when I was toying around with different ways of doing things. Thanks for the memory jog WP!

Again, thank you all for you help.


Hello All!,

I am working on a project for my own personal use and I am still not totally familiar with Lazarus/FPC.

What I am trying to find out is:

When working with a Dbf file is there any way to change the PhysicalRecNo loacated in the Dbf.

Such as when you pack the table all the records marked for deletion are removed but the physical record indexes are not updated such as physical records 1,3,5 were removed and the PhysicalRecNo shows the count of 2 & 4. Is there away to get the PhysicalRecNo back to being sequential again? ie-1,2

Thanks in Advance!

[wp]

I don't think so.

Do you need a reliable, continuous record number? Add an integer field "RecordNumber" to the dataset. Iterate through all records and write a counter value which increments with every record into the field.

Untested:

Code: Pascal  [Select][+][-]

1. procedure UpdateRecordNumber(ADataset: TDataset; ARecordNumberField: String);
2. var
3.   counter: Integer;
4.   F: TField;
5. begin
6.   F := ADataset.FieldByName(ARecordNumberField);
7.   ADataset.First;
8.   counter := 1;
9.   while not ADataset.EoF do begin
10.     ADataset.Edit;
11.     F.AsInteger := counter;
12.     ADataset.Post;
13.     inc(counter);
14.     ADataset.Next;
15.   end;
16. end;

   

[Thaddy]

When working with a Dbf file is there any way to change the PhysicalRecNo loacated in the Dbf.

Yes, you can overwrite the PhysicalRecNo by writing a small tool to do so.
You don't want that, though, because you also have to re-generate all indices.

Such as when you pack the table all the records marked for deletion are removed but the physical record indexes are not updated such as physical records 1,3,5 were removed and the PhysicalRecNo shows the count of 2 & 4. Is there away to get the PhysicalRecNo back to being sequential again? ie-1,2

The order will still be sequential:1,5,7,8,9,100 is still sequential....
Dbf is a technically naive format and real databases have no real notion of record numbers. They should technically (and theoretically) be opaque.. They should not even be accessible.....

Thanks in Advance!

I hope you understand. (I rather doubt it, but I hope)
See: https://www.loc.gov/preservation/digital/formats/fdd/fdd000325.shtml and declare as a packed record.
Note Blobfields and memofields will be broken too: you need to take care of their recno's separately.
And regenerate any indices.

[six1]

Hi,
you can generate an incrementing number with SQL:

SET @counter:=0;
select (@counter:=@counter+1) AS 'AutoIncNumber', c.* from custom c



Best, Michael

[Thaddy]

Hi,
you can generate an incrementing number with SQL:

SET @counter:=0;
select (@counter:=@counter+1) AS 'AutoIncNumber', c.* from custom c



Best, Michael

WRONG AND BAD!
Dbf is not an Sql format. See my reply. You will destroy the Dbf Database .... Please be careful (friendly for now) There is a risk you can not use your database with other Dbf compliant software.

[six1]

ok, didn't got the point...

but how can one destroy a table by getting a result set? Nonsense...

[Thaddy]

@six1
In the context of dbf format!!
- You will change the recnum?, You *must* change the associated recnums in blob/memofields too and you *must* re-generate all indexes.... If you don't you just destroyed your dbf database.
It does not mean you lost data, but records, memo's, blob's are all disconnected and can only be restored by hand: you can not guess which picture originally belonged to which record. All data is still readable but all data is not associated with:
- indexes
- memo's
- blob fields

Unless you know the dbf formats and take care of that. I do, you don't. , 2018: as promised, and you are the first to deserve it: <not grumpy but angry    >
Better first 1. ask for explanation and 2. don't assume things without any knowledge or having read any documentation: such thing pollute and can be done in a rather better way. My aim is to educate, not spend time on bollocks.
This kind of nonsense makes it confusing for OP to read a decent answer. Which I gave. 

Note the solution goes for any language, not just Lazarus/FPC.

If there is a vote on it I might be back to grumpy instead of angry.

[GAN]

Hi,
you can generate an incrementing number with SQL:

SET @counter:=0;
select (@counter:=@counter+1) AS 'AutoIncNumber', c.* from custom c



Best, Michael

DBF = dBase https://en.wikipedia.org/wiki/DBase

Not all DB are SQL.

[Thaddy]

Correct. He was not paying attention.