Problem and Solution in Using dbf Tables lazarus / 16-Databases ---------------------------------------------------------------------- ecitoyen · 2015-06-07 10:42 Look that. Suppose that you have a dbf table named N-A.dbf(Name & Address) Datafield name  -First Name  -Last Name  -Address 1  -Address 2 with thousand records and more if you want to delete some records  you have to use 2 functions  delete (for each records to delete/remove)  and at the end you have to use the  function  packtable. Sometimes the packtable function does not work and it's corrupt dbf table.(on Linux and Windows) The Result: you loose all data. Solution: First: Create a new table named ModelN-A.dbf with the same datafields:  First Name  Last Name  Address 1  Address 2 This MODELN-A.dbf table will always be an empty table Second: If you have many record to delete in the N-A.dbf: Get a array RECORD_TO_DELETE(x) where x is the qty to delete (If you want to delete 10 records, x=10) In this array, put the record number to delete (If you want to delete record numbers 1, 4, 5) RECORD_TO_DELETE[0]=1 RECORD_TO_DELETE[0]=4 RECORD_TO_DELETE[0]=5 QTYRECORDTODELETE=3 Now it's time to process the deletion: a) Copy table(CopyFile) MODElN_A.dbf to TEMP.dbf b) open Table N_A.dbf    FOR N=1 to QTY_RECORD_N_A_TABLE     V1=First Name     V2=Last Name     V3=Address 1     V4=Address 2     j=0     FOR M=1 to QTYRECORDTODELETE      IF RECORD_TO_DELET[M-1]=N then        j=1;     ENDFOR     IF j=0 then      OPEN TEMP.dbf      INSERT      First Name=V1      Last Name=V2      Address 1 = V3      Address 2 = V4      Close temp.dbf     endif    ENDFOR ...    At this time, all the records 'not to delete'    will be copied in the TEMP.dbf table.   Now... it's easy..     Delete Table(N_A.dbf)     Copy Table(CopyFile) TO N_A.dbf(a new version)     Delete Table TEMP.dbf In this example, you have to check the syntax of this procedure to follow the pascal language Look that. Suppose that you have a dbf table named N-A.dbf(Name & Address) Datafield name  -First Name  -Last Name  -Address 1  -Address 2 with thousand records and more if you want to delete some records  you have to use 2 functions  delete (for each records to delete/remove)  and at the end you have to use the  function  packtable. Sometimes the packtable function does not work and it's corrupt dbf table.(on Linux and Windows) The Result: you loose all data. Solution: First: Create a new table named ModelN-A.dbf with the same datafields:  First Name  Last Name  Address 1  Address 2 This MODELN-A.dbf table will always be an empty table Second: If you have many record to delete in the N-A.dbf: Get a array RECORD_TO_DELETE(x) where x is the qty to delete (If you want to delete 10 records, x=10) In this array, put the record number to delete (If you want to delete record numbers 1, 4, 5) RECORD_TO_DELETE[0]=1 RECORD_TO_DELETE[0]=4 RECORD_TO_DELETE[0]=5 QTYRECORDTODELETE=3 Now it's time to process the deletion: a) Copy table(CopyFile) MODElN_A.dbf to TEMP.dbf b) open Table N_A.dbf    FOR N=1 to QTY_RECORD_N_A_TABLE     V1=First Name     V2=Last Name     V3=Address 1     V4=Address 2     j=0     FOR M=1 to QTYRECORDTODELETE      IF RECORD_TO_DELET[M-1]=N then        j=1;     ENDFOR     IF j=0 then      OPEN TEMP.dbf      INSERT      First Name=V1      Last Name=V2      Address 1 = V3      Address 2 = V4      Close temp.dbf     endif    ENDFOR ...    At this time, all the records 'not to delete'    will be copied in the TEMP.dbf table.   Now... it's easy..     Delete Table(N_A.dbf)     Copy Table(CopyFile) TO N_A.dbf(a new version)     Delete Table TEMP.dbf In this example, you have to check the syntax of this procedure to follow the pascal language Summary... Copy the structure of the table in a new table(Model) The Deletion Process is a copy of all records that you want to keep in a TEMP.dbf(a copy of the model table). At the end of the copy process, just delete the original table, copy TEMP.dbf in the new original table and delete the TEMP.dbf. (This bug is coming since lazarus-1.0.10 under Linux Ubuntu 14.4 and Windows8.1 Compiling on previous lazarus ide solve this problem. Bug is in the binary of packtable function) ---------------------------------------------------------------------- rvk · 2015-06-07 11:07 Or find out why packtable corrupts your table. It shouldn't. And if it does... find a more robust and reliable DB-engine (like Firebird). I wouldn't trust a DB-engine (or library) where I need to copy a table because a function in it corrupts my table. ---------------------------------------------------------------------- ecitoyen · 2015-06-07 11:21 I understand your point of view... With dBase, the function 'packtable' is the 'pack' and it always ok. The problem is that dBase is not a free solution-not multi os and the bug is in the binary of packtable (lazarus-free pascal). I dont'know if a programmer in your team know the code of this 'packtable' but the problem is there. Since 2010, using dbf table with lazarus was a very great thing... execpt that. (I can manage table with more than 50,000 records and it's running very well and the table can be use under multi-os and be usable with dBase-From 2003-2010 i used dBase and all the dbf tables are ok with Lazarus(more than 250000 records) Just one other little things... dBase is also available for those are using the old DOS (dbDOS-not free)... Sorry, but i can't help lazarus team with this problem 'cause i don't know how to program in assembly language. ---------------------------------------------------------------------- MichaelBM · 2015-06-07 13:44 As an old dBASE specialist I used another approach to handle deleted records in DBF files (before I switched to InterBase and Firebird). Delete the record and do not pack the table. In dBASE on a multiuser platform it required an exclusive read/write access to table(s).Instead, reuse deleted records by finding them via an index (like "Index on XX for deleted()"). Undelete the record, and overwrite the record with the new data.This way of doing it prevented tables from being completely fragmented. ---------------------------------------------------------------------- BitBangerUSA · 2015-06-07 16:37 is this really a problem? you are sure it's a bug in Lazarus/dbf code? i've used dBase in Lazarus - admittedly with quite small Datasets and not needing to table pack often. additionally, i only allow one instance of my app to run - no 'multi- user' conditions. @ecitoyen - can you say more about the conditions where you have had corruption? ---------------------------------------------------------------------- ecitoyen · 2015-06-08 08:39 Supposed a table A.dbf with 100 records... i load all records in a array I close table I put array in a combobox and i let the user chose item in the list this item choosen have to be deleted At this time i know the content of this item(to be deleted) and the position in the table. The position in the table is equal to to position in the array+1(Array variable begin with 0 to...in the table it's begin with 1) Suppose that this item is located at 50th record. I open the table, scan and stop to the 50th record I delete this record...following by 'packtable' and close. And the next time i want to get access the table i got an error. Is it possible that the packtable function is requiring too much time and if i close table before the packing process will be completed? In this case, i get an opportunity to insert a loop in between packtable and close. I have two to solve this problem: An approach close to the one suggest by MichaelIBM.  Instead of delete a record i can write in the field a value 'DELETED', a logical deletion and no need to use index. In that way, when i want to read the table in an array, i have to skip my logical record = "DELETED". If an user add and delete to much record, the table will be a very strange one. After a lot of time i will need to clean table. The other is just to rewrite the table on a new one without deleted record, following by Delete old table and rename the new table with the old name. (Rewriting a table of 100 records, it is very fast and the user will not see the difference). I want to keep dbf table 'cause on Windows i can mix dBase program and lazarus program. On Windows, i can use a dBase program to do that, not with linux. ---------------------------------------------------------------------- rvk · 2015-06-08 08:54 I'm still not sure what's going wrong at your end. I had a look at the tDBF component in Lazarus. Are you using that??? It has a PackTable function (which calls RestructureTable internally). If I look at the code there is does the same as what you do. It creates a temporary table and copies all existing records to that table after which the names are switched and the old table is deleted. So I'm not sure why you are referring to a packtable "binary". Second... What you suggest in your last post... getting record number 50 to write in DELETE... Don't do it like that !! Always look for some unique identifier of your record and always look for that record (and check is you really got that record before writing in DELETED). For single-user DBF you can get away with scrolling to the 50th record but if you're in a multi-user environment what happens if another user has deleted record 40 (for example). Then if your don't check your record and you delete number 50... you delete the wrong one. ---------------------------------------------------------------------- ecitoyen · 2015-06-08 11:03 First, i understand that the word 'DELETE' seems 'confusing'... ex. in a name & address table, in the datafield 'name' i write 'DELETE'... Second... when i talk about packtable function, you say thats it's make a call to the RestructureTable function(probably a function in language object). It's astonishing! With the same program compiled and linked with lazarus IDE < 1.0.10, all it's OK! At this time i use the older version of my program(no changes), until i choose another solution. Is'it possible that the new version of lazarus has to deal with stronger security measures when we want to write to a file... and sometimes it's slow the execution of the packtable function... and the program flow, back to the next following line...close the table. I suppose that when we use the packtable function, the main program call the function, and this function call Restructure table function and return to the main program. If the restructuration is finished there's no problem... we can close the table... but otherwhise, closing the table may compromise the restructuration process. ---------------------------------------------------------------------- rvk · 2015-06-08 11:30 Quote from: ecitoyen on June 08, 2015, 01:03:11 pmFirst, i understand that the word 'DELETE' seems 'confusing'... ex. in a name & address table, in the datafield 'name' i write 'DELETE'... No, I understood that. But you mentioned that you wanted to load in 100 records and after choosing number 50th from that list/array you also wanted to delete the 50th in your table. But you should never assume that the table hasn't changed. So always use a unique ID-field (like ContactID or something) which is autoincremented. Then, when loaded in your array and you choose a contact to delete you should look for that ContactID and not assume it's on some predefined place in your table. Perhaps in single-user DBF it won't be a problem but when you're going to upscale your application (multi-user or to another DB_Engine) you should really work with unique identifier- fields. QuoteSecond... when i talk about packtable function, you say thats it's make a call to the RestructureTable function(probably a function in language object). It's astonishing! With the same program compiled and linked with lazarus IDE < 1.0.10, all it's OK! //.. Is'it possible that the new version of lazarus has to deal with stronger security measures when we want to write to a file... and sometimes it's slow the execution of the packtable function... and the program flow, back to the next following line...close the table. Yes, that's strange. As far as I can see Packtable only returns when it has done it's job. You can check out the code yourself. In Lazarus set your cursor on .Packtable; call. Press Alt+Arrow-up. You'll jump to the packtable-function in the TDbf-definition. Now press Ctrl+Shift+Arrow-down. Now you'll jump to the implementation- part of PackTable. You'll see the line FDbfFile.RestructureTable(nil, true);. Set the cursor on RestructureTable. Again press Alt+Arrow-up followed by Ctrl+Shift+Arrow-down. Now you're in the function RestructureTable where you can see a temporary table being build and the table being copied. The Alt+Arrow-up and Ctrl+Shift+Arrow-down is very useful for jumping through your code but also jumping through the source of Lazarus. You can learn a lot from jumping though the code. I looked at an older revision of dbf_dbffile.pas (in .\fpc\packages\fcl-db\src\dbase) where the RestructureTable resides and couldn't find much different. Is the corruption of the table something you can reproduce every time? Or does it only happen occasionally? Does it also happen with relative small table (like 100 records)? Maybe writing a small test-program filling a table with 100 records and randomly deleting some records and doing a packtable (and repeating it a few times) will show us there is really something wrong in the code. ---------------------------------------------------------------------- sfeinst · 2015-06-08 11:44 I don't know if it helps, but thought I would throw it out.  According to wiki tutorial for DBF, the following are the steps to pack: MyDbf.Exclusive := True; MyDbf.Open; MyDbf.PackTable; // let's also rebuild all the indexes MyDbf.RegenerateIndexes; MyDbf.Close; MyDbf.Exclusive := False; I don't know if you really need to rebuild indexes or just the tutorial giving advice to do so.  But the tutorial seemed very clear that exclusive needs to be set.  So wondering if you are doing that. ---------------------------------------------------------------------- ecitoyen · 2015-06-08 11:49 I will check it this week... I will create a table a with a 50 records fill with First name and lastname field After i will copy this table to a working table I will open this working table and i will delete randomly 25 five records. At the end I will use these 2 lines packtable.... close... after a delay(10 secondes) i will reopen the working table... if i got an error i will see it. And i will erase the working table and i will launch again this program test (25 times)... and report result. ---------------------------------------------------------------------- rvk · 2015-06-08 12:03 Quote from: ecitoyen on June 08, 2015, 01:49:14 pmI will check it this week... I will create a table a with a 50 records fill with First name and lastname field I already made some small test-program for PackTable. (See attached) If you want to experiment with that... It makes 100 records. You can use Delete in the DBGrid to delete records. Press Backspace to PackTable. And Space to repopulate it with 100 records (it empties the table beforehand). I didn't even use Close/Open etc. before using PackTable. A full table is about 33Kb (filesize) and after packtable you'll see it decreasing. ---------------------------------------------------------------------- ecitoyen · 2015-06-08 22:10 I make a little program with a memo field (and 2 button... but not useful for now) The file source.txt is the unit1.pas The table is name.dbf... these 2 files is in the test.zip (attachment) And this code giving me the error. Compile on Ubuntu 14.04 et lazarus-ide-1.0.10 ---------------------------------------------------------------------- ecitoyen · 2015-06-09 00:54 i try another test: In the same program, i delete all the lines after theses lines ... Quote    for I:=1 to 50 do      begin       if A[I-1]=1 then        begin         Dbf1.Next;        end       else        begin         Dbf1.Delete;        end;      end;     Dbf1.Close; After the program is terminated,(in the table, we get records and deleted records) I use MyDbfStudio and open the table. I press the packtable button... At the end it's display the same error message. ----------------------------------------- I made other verifications... Ubuntu i have an original table name.dbf size: 3K I run the program without packtable and i get an output table with deleted records 'workname.dbf' size: 3k i rename workname.dbf workname0.dbf with MyDbfStudio i pack the table and i get a new table workname1.dbf size: 2k(corrupted records)1,24 Ko (1 272 octets) Windows 8.1 I verify with Windows 8.1 what's going on with tables. I use dBase Plus 9 and i try to open workname1.dbf (corrupted records) it does not work. I use dBase Plus 9 and i try to open workname0.dbf (OK with all records, deleted one's and not deleted. in dBase Plus 9 i use the function pack and it's ok (size 1273 octets) If you're looking... the size of the 2 packed tables (the size of the packed table with corrupted records is 1272 octets) (the size of the packed table without corrupted records is 1273 octets) The packtable function seems to me that one byte is lost. (Maybe the EOF byte) ---------------------------------------------------------------------- LacaK · 2015-06-09 05:55 Can you attach full compilable project (all needed files like LFM, PAS, LPI etc.) with DBF file (zipped together) which when runs shows error ? ---------------------------------------------------------------------- rvk · 2015-06-09 07:36 Quote from: ecitoyen on June 09, 2015, 02:54:49 amwith MyDbfStudio i pack the table and i get a new table workname1.dbf size: 2k(corrupted records)1,24 Ko (1 272 octets) Uuuuh? With MyDbfStudio you pack the table and get a corrupted file? I thought we were talking about the packtable function in Lazarus? Did you see the same problem with my small test-program? Your test-program gave me a workname.dbf of 1273 bytes, every time. (laz1.4/fpc2.6.4) Also... you are working with Lazarus 1.0.10. It's a 2 year old version. If you upgraded, why didn't upgrade direct to Lazarus 1.4 with FPC 2.6.4? ---------------------------------------------------------------------- ecitoyen · 2015-06-09 09:52 Yes... This morning i try to know which bytes is loosen... Xworkname0.dbf is the one where Packtable is ok workname1.dbf the corrupted table name.dbf the full table without deletion cmp --verbose Xworkname0.dbf workname1.dbf i get this report Quote   4  11  10    5  33   0   34 102   0   35 127 102   36 111   0   37 116 127   38 125   0   39 123 111   40  60   0   41   0 116   43   0 125   45   0 123   47   0  60 In the test.zip package name.dbf,Xworkname0.dbf,workname1.dbf I also include the test project! In the file unit1.pas the only lines that you shall change is the 3 following lines   varnom1:='/.../name.dbf';   varnom2:='/.../workname.dbf';   pathdbf:='/.../';   namedbf:='workname.dbf'; You have to put the true location of the tables. Don't delete the resulting tables Xworkname0.dbf and workname1.dbf ---------------------------------------------------------------------- rvk · 2015-06-09 10:27 I still can't reproduce your problem with Debian/Lazarus 1.4/FPC 2.6.4. I'm getting a 1273 bytes file every time. Could you try this on your setup with a later version of Lazarus? Version 1.0.10 is quite old. B.T.W. you can just use this: Code: [Select]  varnom1:='name.dbf';   varnom2:='workname.dbf';   pathdbf:='';   namedbf:='workname.dbf'; In that case all your test-files are in the same directory als your executable/project. ---------------------------------------------------------------------- ecitoyen · 2015-06-09 12:10 Yes, i will try it on my windows version of lazarus(the old 1.0.14). I also downoad the last version of lazarus for windows(32 bit). It will be easier to debug ... and use dBase 9 if it's required. ---------------------------------------------------------------------- ecitoyen · 2015-06-09 19:10 My latest tests: I verify the same program on windows 8.1 with lazarus 1.0.14 and i get the same error. I also to install the latest ide for windows 8.1(32 bits) with lazarus 1.4... and i will recompile the program, run it and now... Surprise: No more error... the length is now the same ...1273. ---------------------------------------------------------------------- rvk · 2015-06-09 20:38 Bingo... found the fix in SVN 24187 at april 7th 2013 * fix writing $1A EOF marker at end of dbf file Very old code: Code: [Select]  EofTerminator := $1A;   WriteBlock(@EofTerminator, 1, CalcPageOffset(RecordCount+1)); Newer code: Code: [Select]  // Write terminator at the end of the file, after the records:   EofTerminator := $1A;   // We're using lDataHdr to make sure we have the latest/correct version   WriteBlock(@EofTerminator, 1, CalcPageOffset(lDataHdr.RecordCount+1)); And I think FPC 2.6.2 is from before that time. So... yeah... use the newer version and your good ---------------------------------------------------------------------- ecitoyen · 2015-06-09 20:59 With Ubuntu 14.04... FPC version is: fpc 2.6.2-8 and lazarus-ide-1.0.10 Now, with Windows 8.1 this problem is solved. Do you will change version of lazarus & FPC in the Ubuntu Software Manager? (Ubuntu Software Manager is also used by Linux Mint) I'm very happy to find it and i appreciate the way we find it. ---------------------------------------------------------------------- rvk · 2015-06-09 21:05 Quote from: ecitoyen on June 09, 2015, 10:59:13 pmDo you will change version of lazarus & FPC in the Ubuntu Software Manager? (Ubuntu Software Manager is also used by Linux Mint) I'm not sure who is responsible for putting Lazarus/FPC in the Ubuntu Software Manager. You could download the newest version from http://www.lazarus-ide.org/ (Download button) and choose the .DEB files. You could follow this page to install it in Ubuntu http://wiki.lazarus.freepascal.org/Install_on_Ubuntu_from_.deb_files But... you're right... If the Ubuntu Software Manager has such an old version it should be investigated how to supply it with a newer copy. (Of course you could also try fpcup or getlazarus.org to install the latest trunk version)