New float fields contain 6E-154 lazarus / 16-Databases ---------------------------------------------------------------------- SymbolicFrank · 2021-10-20 12:49 Or, 6.01347001699907E-154 to be exact. This is with a FoxPro database, accessed with TDbf. If I append a new record in an existing database, this is the default value for float fields. What could this be? A wrong TableLevel or Version? I set those to 25 and 7.0 respectively, but I'm not sure what they should be. ---------------------------------------------------------------------- Thaddy · 2021-10-20 13:13 Exact? ---------------------------------------------------------------------- SymbolicFrank · 2021-10-20 13:38 Well, no, probably not It's probably the smallest possible number. 0.0000000000000000 with 16 decimals, but not exactly 0. ---------------------------------------------------------------------- engkin · 2021-10-21 02:40 No, not the smallest. This number is $2020202020..... in hex I believe. More like spaces maybe? Try it here, check "Raw hexadecimal" https://www.exploringbinary.com/floating-point-converter/ ---------------------------------------------------------------------- SymbolicFrank · 2021-10-21 08:08 I was wondering if this should be the value used for a NULL. Well, everything seems to work and with correct formatting it looks fine (the TDBGrid defaults to scientific notation), but it's annoying. ---------------------------------------------------------------------- wp · 2021-10-21 08:38 Quote from: SymbolicFrank on October 21, 2021, 10:08:56 amI was wondering if this should be the value used for a NULL. No, NULL is not stored as an encoded data value but in a special location in the dataset data buffer outside the data value bytes. ---------------------------------------------------------------------- SymbolicFrank · 2021-10-21 11:57 I'll dig into it tomorrow. Perhaps it's a textfield at some point. ---------------------------------------------------------------------- SymbolicFrank · 2021-10-22 09:58 Yup, it's a string of spaces: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TDbfFile.InitDefaultBuffer; ..    // binary (non-text) field? (foxpro memo fields are binary, but dbase not)    if (TempFieldDef.NativeFieldType in ['I', 'O', '@', '+', '0', 'W', 'Y'])        or ((TempFieldDef.NativeFieldType = 'M') and (TempFieldDef.Size = 4) {Visual FoxPro?}) then      FillChar(PChar(FDefaultBuffer+TempFieldDef.Offset)^, TempFieldDef.Size, 0);  Which is defined as: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TDbfFieldDef.VCLToNative;begin  FNativeFieldType := #0;  // to do: look into ftBytes support; e.g. Visual FoxPro varbytes?  case FFieldType of..    ftFloat, ftSmallInt, ftWord{$ifdef SUPPORT_INT64}    , ftLargeInt{$endif}{$ifdef SUPPORT_LONGWORD}      , ftLongWord, ftShortInt, ftByte{$endif}               :      FNativeFieldType := 'N'; //numerical..    ftBlob     :      case DBFVersion of        xFoxPro:          FNativeFieldType := 'P'; //picture; best we can do      xVisualFoxPro:          FNativeFieldType := 'W'; //blob        xBaseIII,xBaseIV:          FNativeFieldType := 'M'; //memo; best we can do        xBaseV,xBaseVII:          FNativeFieldType := 'B'; //binary  It thinks it is a 'B' field, while it should be an 'F' (Float) field, "new" to FoxPro... Is there a better DataSet component / library for manipulating FoxPro databases? ---------------------------------------------------------------------- korba812 · 2021-10-22 10:40 Could you please attach a sample dbf file? ---------------------------------------------------------------------- SymbolicFrank · 2021-10-22 10:49 It's the default TDbf component, it should be part of any Lazarus install. The files are in: fpcsrc\packages\fcl-db\src\dbase . ---------------------------------------------------------------------- korba812 · 2021-10-22 10:53 I was referring to dbf file produced by FoxPro that you are trying to open with TDbf in Lazarus. ---------------------------------------------------------------------- wp · 2021-10-22 13:01 Quote from: SymbolicFrank on October 22, 2021, 11:58:18 amIs there a better DataSet component / library for manipulating FoxPro databases? I don't know. But during my work at MyDbfStudio I learned that the fpc version of TDbf is a fork of https://sourceforge.net/p/tdbf/code/HEAD/tree/. Both versions have diverged to some degree, and both do get some changes occasionally. So, there is some chance that the sourceforce TDbf can handle FoxPro files better - just a wild guess... I would recommend that you test this version of the component. But be aware that keeping two equally named packages within an installation calls for trouble; therefore, I would not install the source forge version, but - against all rules - add the path the its sources to the search path of your project. Create the TDbf component at runtime and remove the TDbf package from your program requirements. Then you can test the sourceforge version of TDbf. Of course these are very massive changes to your project, and it is absolutely necessary that you conserve the previous state in a backup. ---------------------------------------------------------------------- SymbolicFrank · 2021-10-22 13:47 Test .DBF. The last field is a float. ---------------------------------------------------------------------- SymbolicFrank · 2021-10-22 14:29 Quote from: wp on October 22, 2021, 03:01:48 pmI don't know. But during my work at MyDbfStudio I learned that the fpc version of TDbf is a fork of https://sourceforge.net/p/tdbf/code/HEAD/tree/ I just checked, but the FPC/Laz version supports more field types and is more complete. ---------------------------------------------------------------------- wp · 2021-10-22 15:04 I confirm the issue with this file. The file provided seems to be TableLevel 30 (Visual FoxPro). When I create such a dbf file with an ftFloat field myself, the ftFloat field behaves as expected. What's wrong here? ---------------------------------------------------------------------- korba812 · 2021-10-22 15:58 Record buffer is incorrectly initialized for field type 'B' (double). It is padded with spaces instead of binary zero. The solution is easy. I can create a patch. ---------------------------------------------------------------------- korba812 · 2021-10-22 16:19 I attach a patch. Please test it. ---------------------------------------------------------------------- SymbolicFrank · 2021-10-22 16:25 Thanks! I'll test it extensively. It's what XBase++ (a Clipper-successor, written in Delphi) produces, if you tell it to create a FoxPro level 31 database (with autonums). Perhaps the error is on their side? ---------------------------------------------------------------------- korba812 · 2021-10-22 16:40 No, this is definitely a problem with the TDbf component. ---------------------------------------------------------------------- winni · 2021-10-22 17:53 Hi! The Employee data is not correct. Floats in dBase are saved as ASCII and padded to the left with spaces $20. But the whole  field is filled with zeros $00. Winni ---------------------------------------------------------------------- korba812 · 2021-10-22 18:07 This is true for numeric fields (native type 'N'). We are talking about binary float field ('B') which was introduced in FoxPro (I think). ---------------------------------------------------------------------- winni · 2021-10-22 18:34 Hi! Okay. If it is a 'B' then the dBase definition is: B    Binary, a string    10 digits representing a .DBT block number. The number is stored as a string, right justified and padded with blanks. This collides with your info about FoxPro. Winni ---------------------------------------------------------------------- korba812 · 2021-10-22 18:40 The nature of DBF files - unified standard has never been developed and each vendor has freely implemented it. Here is the list of fields supported by Visual FoxPro: https://docs.microsoft.com/en-us/sql/odbc/microsoft/visual-foxpro- field-data-types?view=sql-server-ver15 ---------------------------------------------------------------------- winni · 2021-10-22 19:07 Hi! Gosh!  This collides with dBase definition: O    Double    8 bytes - no conversions, stored as a double. But: If we assume that this is Foxpro the "TESTFLOAT" with definition "B" has a hexvalue of $ 08 00 04 00 If you move this value into a double it results in 6.6....E-316 which might be a "special kind of zero" Winni ---------------------------------------------------------------------- korba812 · 2021-10-22 19:21 Quote from: winni on October 22, 2021, 09:07:40 pmBut: If we assume that this is Foxpro the "TESTFLOAT" with definition "B" has a hexvalue of $ 08 00 04 00 If you move this value into a double it results in 6.6....E-316 which might be a "special kind of zero" This means that the field is 8 bytes long and has a precision of 4 decimal places. I am not sure what the number of decimal places means, it may be for formatting purposes. ---------------------------------------------------------------------- winni · 2021-10-22 19:48 Hi! I assume that Foxpro does it like dBase and saves the binary value. So we need another 4 bytes for a double. If we pad the zeros to the right we get $0800040000000000 This results in a double of 3.78....E-270 Also not so far away from zero ... But who knows. It is 30 years ago that I did this stuff the last time. Winni ---------------------------------------------------------------------- korba812 · 2021-10-22 19:59 Yes, the values are stored as double so they are 8 bytes size. Note that the attached dbf file does not contain any data. The values you provide come from definition of table structure. ---------------------------------------------------------------------- SymbolicFrank · 2021-10-22 20:01 In XBase++, if you specify the precision of a float ("F"), it gives an error message. Floats always seem to be 8 bytes as well. It's just a double. While a numeric value ("N") is more flexible, it has strict rules about the amount of bytes you need for a specific precision and amount of decimals. And a binary value ("B") seems to have been a blob, which was different form a memo ("M"), which is a blob that always contains text. Both reside in their own file. FoxPro first introduced the float ("F"), which we would call a double, and later (Visual FoxPro?), the "B" data type was repurposed as a double (?) and memos could contain blobs as well. Or, that's what I understood. dBase IV and V, XBase++ and numerous other databases that use variants of DBF files, seem to more or less disagree with the above. ---------------------------------------------------------------------- winni · 2021-10-22 20:29 Quote from: SymbolicFrank on October 22, 2021, 10:01:24 pmIn XBase++, if you specify the precision of a float ("F"), it gives an error message. Floats always seem to be 8 bytes as well. It's just a double. Hi! No, the dBase definition is more funny. The width definition connects to - in Pascal terms - floatToStr(myFloat)  : F    Float    Number stored as a string, right justified, and padded with blanks to the width of the field. I can only advise what I did 30 years ago: Take the original software, export it as ASCII, ANSI or IBM8. Or whatever. Import it to SQL. And never touch it again. The only good part of dBase and friends was the index file with a balanced  Beyer tree. The rest was a lot of hacks. Winni ---------------------------------------------------------------------- SymbolicFrank · 2021-10-22 20:40 So, is the proposed fix going to work for all the dBase-alike DBF files? Or would it only work for XBase and/or (Visual) FoxPro? How should we fix TDbf? ---------------------------------------------------------------------- korba812 · 2021-10-22 20:54 Is not so bad. Dbf file header has information that makes it possible to 'guess' what we are dealing with. As you noticed, the TDbf component has the "TableLevel" property, which allows it to be (more or less) compatible with the various versions. Patch I attached only affects files compatible with "FoxPro" and "Visual FoxPro" versions. ---------------------------------------------------------------------- winni · 2021-10-23 08:15 Hi! The first byte in the dBase file contains the version information: byte 0    1 byte    Valid dBASE for Windows table file, bits 0-2 indicate version number: 3 for dBASE Level 5, 4 for dBASE Level 7. Bit 3 and bit 7 indicate presence of a dBASE IV or dBASE for Windows memo file; bits 4-6 indicate the presence of a dBASE IV SQL table; bit 7 indicates the presence of any .DBT memo file (either a dBASE III PLUS type or a dBASE IV or dBASE for Windows memo file). Now get the info how Foxpro and other clones treats this byte. Then you got what is needd. Winni ---------------------------------------------------------------------- SymbolicFrank · 2021-10-25 07:46 I added it to the bugtracker.