Topic: PHP filepro_rowcount (blank "deleted" records)

[mpacker99]

Please excuse my ignornance when it comes to filepro as I'm just trying to pull data from a filepro database.

i'm using php and have a line that says:

$rowcnt = filepro_rowcount();

It properly works and tells me there are 7430 records (data records)
Unfortunately there are evidently deleted records in there too, i'm not seeing the deleted records, just blank fields in every column.

when I go into filepro (version 5.0.14D4) (here's what I did)

Inquire/Update/Add records
Selected the database
selected by screen number i'm using
on the choose record operation i'm chosing 1 (Record Number)

It says "Enter Record Number (1-183935)

when i try random record numbers some come up with data 3456 has data, but when i did 8888 it's all blank (it had data but has been deleted) but record 8900 has data in it.

As I understand it filepro leaves the blank records there when the data base been deleted until you do something.  Can someone please explain to me what I need to do to get this down to the real 7430 records I actually have (hence making my PHP program only have to scan 7430 records instead of 183935).

Greatly appreciated.
mpacker

[kenbrody]

As you noted, when you delete a record in filePro, the record still exists as a blank, deleted record.  These records will be reused by filePro as you add records in the file.

If you want to remove all of the deleted records from a file, there are utilities available.

There is a free program from Robert Repko called "squeeze" which is available at the bottom of http://www.hvcomputer.com/download/public.html.  However, the comment says it's from 1995, so I don't know if it works with filePro 5.0.  (Though I see no reason why it wouldn't, as the format of the map and key files haven't changed.)

There is a commercial program available from Jean-Pierre Radley called "fpkd", which I believe is available on http://www.jpr.com.

There may be other similar utilities avaiable, but these are the two that I know of.

Note that these programs cause the record numbers of the records to change.  Some people have coded their databases to use the record number as a key value of some sort.  If this is the case with this database, you need to make sure that changing the record numbers won't affect the database.

[mpacker99]

As you noted, when you delete a record in filePro, the record still exists as a blank, deleted record.  These records will be reused by filePro as you add records in the file.

If you want to remove all of the deleted records from a file, there are utilities available.

so there are no filepro utilities to do this?  it would seem that if the record number isn't being used there should be some easy way to compress (remove deleted records).

[Alan]

if you are not using the record number as a key field you could use fpcopy to make an exact copy of the database.  Then after making sure you have a good backup access the filePro Directory command from the main filepro menu and delete the key and data. Option "L" after selecting the database you are working on and pressing "D".

after that is done this goto to the backup copy of the database and write a little processing table to copy the non blank records back to the original file.

example:

lookup file=database r=free -nx:
copy file:
end

Just remember when you run this output format to only select records that are not blank.