TDbf Pack problem/issue ...

[1HuntnMan]

I have an issue with an almost completed app with 2 tables in a TDbf database. I moving soon to SQLite but got to fix this first.  This app has 8 main tables with several other lookup tables such as States/Provinces, Countries, etc. On 2 of the forms, Appointments (Appmts.dbf) and Photography (Photos.dbf) I have a major issue with packing these 2 tables. I have a system create procedure that creates all the tables and indices for the app and another procedure for table maintenance.  The issue is when on these 2 tables for some reason crater on the Pack code line, see the example code snipet below:

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

1.   try  //->Appointments table maintenance start... 1
2.     DbfAppmts.Exclusive:= True;
3.     DbfAppmts.Open;
4.     DbfAppmts.Active:= True;
5.     //->DbfAppmts.PackTable; //->Pack Appointments.Dbf table !!! Error here!
6.     DbfAppmts.RegenerateIndexes; //->Rebuild Appointments table indexes
7.     DbfAppmts.Exclusive:= False;
8.     DbfAppmts.Close;
9.   finally
10.     DbfAppmts.Free;
11.     PrgrsBarRebld.Position:= 10;
12.   end;
13.  

This is the error that bombs out below, I can comment out these 2 Pack and Regen Indexes for these 2 tables and no issue. I can comment out the 2 Pack lines and no problemo! Error attached which is this:
  "Project pms raised exception class 'External: FLT INVALID OPERATION'. At address 100626CF"

Anyone have a clue why just these 2 while the rest (6), no problems.
Tks...

[paweld]

The reason for the floating-point exception could be invalid values in the numeric columns, or an invalid data type for the indicated TableLevel - some numeric data types are only supported in specific TableLevel: https://wiki.lazarus.freepascal.org/Lazarus_Tdbf_Tutorial#Adding_fields

[wp]

Not solving your problem, but I noticed the following points:

• Dataset.Open and Dataset.Active := true do the same - use only one of them
• Shouldn't Dataset.Exclusive := false be done after closing the dataset?
• Immediately afterwards you destroy the dataset. Is this really intended? Where is it created? Are you sure that it exists when the procedure is entered next time?
• Is Dbf.Active true already in the object inspector? This may cause problem with exclusive access (https://forum.lazarus.freepascal.org/index.php/topic,38350.msg260201.html#msg260201).
  
• There is also a "FastPackTable" which only fills the holes in the file for an "in-place" pack (Dbf1.DbfFile.FastPackTable). This method already contains the call to RegenerateIndexes

[1HuntnMan]

So, it appears that I don't need to both Open the Dbf.Table and also set the Dbf.Table to Active.  Also, it appears that I can just set Exclusive to True first, then just OPEN table and then FastPackTable with no need to RegenerateIndexes and then just close DbfAppmts table. Lastly, set Exclusive to False.  I think my understanding is that table can't be open or active when adjusting the Exclusive property, correct and I don't need to Open and also set the table Active to True, just Open it.  The only properties I have set for all the tables in this maintenance Procedure is Active is OFF, FilePath/FilePathFull are set to the Data directory, TableLevel=7 and the TableName is set to the table file name, i.e. Appointments.dbf in the PMS Data folder below the executable per the FilePathFull.

This is what I'm going to test, I'll be back to let ya'll know...

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

1.   try  //->Appointments table maintenance start... 1
2.     DbfAppmts.Exclusive:= True;
3.     DbfAppmts.Open;
4.     //->DbfAppmts.Active:= True; // No need since it's OPEN
5.     DbfAppmts.FastPackTable; //->Pack Appointments.Dbf table PLUS Regenerate the indexes!
6.     //DbfAppmts.RegenerateIndexes; //->No need to Rebuild Appointments table indexes, done already
7.     DbfAppmts.Close;
8.     DbfAppmts.Exclusive:= False;
9.   finally
10.     DbfAppmts.Free;
11.     PrgrsBarRebld.Position:= 10;
12.   end;
13.  

[wp]

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

1.   try  //->Appointments table maintenance start... 1
2.     DbfAppmts.Exclusive:= True;
3.     DbfAppmts.Open;
4.     //->DbfAppmts.Active:= True; // No need since it's OPEN
5.     DbfAppmts.FastPackTable; //->Pack Appointments.Dbf table PLUS Regenerate the indexes!
6.     //DbfAppmts.RegenerateIndexes; //->No need to Rebuild Appointments table indexes, done already
7.     DbfAppmts.Close;
8.     DbfAppmts.Exclusive:= False;
9.   finally
10.     DbfAppmts.Free;
11.     PrgrsBarRebld.Position:= 10;
12.   end;
13.  

Let me add: Assuming that you really intend to Free the dbf at the end you can even skip the Exclusive := false. Maybe one important point: if DbfAppmts is a global variable rather than local to the current procedure you should nil DbfAppmts immediately after freeing (or call FreeAndNil(DbfAppmts)) in order to signal to possible calling methods that it does not exist any more (you must check DbfAppts there for nil before accessing it).

To understand the equivalence of Dataset.Open and Dataset.Active := true, just take a look at the source code of TDataset:

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

1. procedure TDataSet.Open;
2. begin
3.   Active:=True;
4. end;

Likewise for Close:

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

1. procedure TDataSet.Close;
2. begin
3.   Active:=False;
4. end;

[1HuntnMan]

Alright WP, that's great, I'll start looking more at the source code. Also, got-it, no need to Set Exclusive false since I'm freeing up the table.  The way I set this up, the form has a big warning that all users need to be out of the Application before running the System Maintenance OR the whole building will crash! LoL

I probably need to research how to catch the error and alert the user that's attempting to run the procedure if it errors on Setting Exclusive to True when someone else is using the app and another attempts to run Maintenance or will it error?  I just ran this complete procedure for all the tables in the app and no more errors. Thanks!

To test out the scenario if another user with the table(s) open in the Appointments Procedure and then went to the bathroom for an extended period of time and the head honcho tells everyone to get out of system and runs Maintenance.  I can test this by opening 2 instances of the application to imitate this without going to the john...