Recent

Author Topic: TDBF fails to open DBF files with ftfloat correctly  (Read 8536 times)

magu

  • New Member
  • *
  • Posts: 29
TDBF fails to open DBF files with ftfloat correctly
« on: June 12, 2021, 11:41:21 am »
Hello,

It appears that TDbf fails to correctly read floating numbers, I am not sure why (and i'm not sure if this problem is exclusive to me).
for example in the attached dbf in row 1 the values should be "12345.67" and "201.14" but instead are displayed as "1.2E3" and "2". While in row 2 the values "345.67" and "12.114" are displayed as 34 and 1
I have tried to set TDbf.TableLevel manually to see if this makes a difference but the TableLevel changes when you open the DBF.
The DBF fields are defined as numeric with size(24) and Precision(3).
However when opening the file TDF does not appear to recognise these attributes and I'm not sure how to change them programmatically one the dbf file is opened.
Thanks


wp

  • Hero Member
  • *****
  • Posts: 8749
Re: TDBF fails to open DBF files with ftfloat correctly
« Reply #1 on: June 12, 2021, 04:31:33 pm »
In the attached project I played a bit with the dbf float field. I don't know if it is a bug (I doubt) or a matter of definition. It looks to me that the "Size" in the fielddef is largely ignored, and the "Precision" determines the total count of digits - not just the decimal places. So, when you say the Precision is 3 then you only have 3 digits to display the value, and I would expect 12345.67 to be displayed as 1.2E4 (three digits: 1, 2, and 4). But you say it is displayed a 1.2E3 (exponent smaller by 1), and I can confirm that when I display your table in MyDbfStudio. So what is going on here? Can you show the code how you create the table and in particular the fielddefs?
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

magu

  • New Member
  • *
  • Posts: 29
Re: TDBF fails to open DBF files with ftfloat correctly
« Reply #2 on: June 13, 2021, 10:29:28 am »
The database was taken from a legacy system that I am trying to transfer to a more modern db.
So it could be some strange dialect however Ms Access reads the file correctly (if it helps the MS connection string automatically generated states: "dBASE III;HDR=NO;IMEX=2;ACCDB=YES;").
I have tried to open it with another program (DBF Viewer 2000 - dbf2002.com) and this worked correctly.

The fields have been reported as being the following:

Name       Type    Size Dec
DRef    Character    8    0
SAMPLEDATE Date    8    0
Notes    Character    30    0
Amt2    Numeric    24    2
AMOUNT    Numeric    25    3

magu

  • New Member
  • *
  • Posts: 29
Re: TDBF fails to open DBF files with ftfloat correctly
« Reply #3 on: June 13, 2021, 11:52:11 am »
The issue seems to be with the Numeric Size.
Lowering the Numeric size to a value under 18 (and increasing the second attribute - which is precision) will provide a correct result.

The issue arises when the Numeric Size is greater than 18.
Ms Access reads the value correctly by treating the number as a Double.

Having seen the TDbf fields source it appears that the numeric size is limited (there) to 18 digits, but I'm not sure how to extend this / treat this as a special case or flag an error...

winni

  • Hero Member
  • *****
  • Posts: 2664
Re: TDBF fails to open DBF files with ftfloat correctly
« Reply #4 on: June 13, 2021, 12:17:59 pm »
Hi

In dBase III the maximum length of a numeric field is restricted to 15 digits - perhaps that is the reason for the error.

Winni

wp

  • Hero Member
  • *****
  • Posts: 8749
Re: TDBF fails to open DBF files with ftfloat correctly
« Reply #5 on: June 13, 2021, 04:34:19 pm »
In the attached project I copied the TDbf sources of FPC3.2.0 into the folder of a simple test project reading your file. This way it is very easy to debug the TDbf and to see the effect of changes made.

I am rather sure that the size of the float field is somehow screwed up. Since dbf writes the records as text I suppose that "Size" is the amount of bytes occupied by a field in a record.  Your "Amt2" field has Size=24 and Precision=2 where I interpret "Precision" now as "number of decimal places".

Setting a breakpoint in TDbfFile.GetFieldDataFromDef (unit dbf_dbffile) and following the code (which is called from Field.AsString) I can see that the field size is only 20 here. I can also see the contents of the record which matches your description. Therefore, 4 characters are lost for the Amt2 value along the way which becomes now '1234' (rather than '12345.67').

In  TDbfFile.ConstructFieldDefs which is called earler, from dbf.Open, the Size is read correctly (24), but one of the calls to TDbfFieldDef.CheckSizePrecision (unit dbf_fields) chops off 4 characters: there is a line "if FSize >= 20 then FSize := 20" for the case of a numeric field (native field type "N").

Commenting this line fixes the issue.

Not sure of course, if this does not introduce sideeffects somewhere else.

The DBGrid still does not display two (three) decimal places, but this can be fixed by adjusting the DisplayFormat of these columns (see attached "Project1").

Waiting for your feedback. Then I should submit a patch for both the fpc and original version of TDbf.
« Last Edit: June 13, 2021, 04:38:00 pm by wp »
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

LacaK

  • Hero Member
  • *****
  • Posts: 637
Re: TDBF fails to open DBF files with ftfloat correctly
« Reply #6 on: June 15, 2021, 07:45:48 am »
I only doubt whether the exponential form of the number is supported in the specification of DBF files for "F" field types.
I can not find relevant documentation - may be that support differs between various vendors.
(in some documantation is stated, that only "-","." and "0"-"9" are valid characters for "F" fields)

wp

  • Hero Member
  • *****
  • Posts: 8749
Re: TDBF fails to open DBF files with ftfloat correctly
« Reply #7 on: June 15, 2021, 04:30:12 pm »
When you open the dbf file in a hex editor or even in a normal text editor you can see that the values are written as text in normal, non-exponential notation.

That the float field is displayed in exponential notation has nothing to do with TDbf, but with the way how TFloatField works (I get the same exponential display also with a TBufDataset). TFloatField.GetText in unit DB is the method which determines how a floating point value is displayed:
Code: Pascal  [Select][+][-]
  1. procedure TFloatField.GetText(var AText: string; ADisplayText: Boolean);
  2. Var
  3.     fmt : string;
  4.     E : Double;
  5.     Digits : integer;
  6.     ff: TFloatFormat;
  7. begin
  8.   AText:='';
  9.   If Not GetData(@E) then exit;
  10.   If ADisplayText or (Length(FEditFormat) = 0) Then
  11.     Fmt:=FDisplayFormat
  12.   else
  13.     Fmt:=FEditFormat;
  14.    
  15.   Digits := 0;
  16.   if not FCurrency then
  17.     ff := ffGeneral
  18.   else
  19.     begin
  20.     Digits := CurrencyDecimals;
  21.     if ADisplayText then
  22.       ff := ffCurrency
  23.     else
  24.       ff := ffFixed;
  25.     end;
  26.  
  27.   If fmt<>'' then
  28.     AText:=FormatFloat(fmt,E)
  29.   else
  30.     AText:=FloatToStrF(E,ff,FPrecision,Digits);
  31. end;
A "normal" (i.e.: non-currency) float field is displayed by FloatToStrF with format ffGeneral, and the help file says about this format: If Value is less than 0.00001 or if the number of decimals left of the decimal point is larger than Precision then scientific notation is used, and Digits is the minimum number of digits in the exponent.

The overall behaviour is in contradiction to Delphi which uses the Precision parameter to define the number of decimal places. A lot of other programs do it like that. It is not clear why FPC decided to go its own way. Maybe there should be a property to allow switching to the Delphi-conformal presentation.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

wp

  • Hero Member
  • *****
  • Posts: 8749
Re: TDBF fails to open DBF files with ftfloat correctly
« Reply #8 on: June 16, 2021, 11:37:48 am »
Filed bug reports for the FPC (https://bugs.freepascal.org/view.php?id=39009) and sourceforge versions (https://sourceforge.net/p/tdbf/bugs/106/)

[EDIT] Michael applied the FPC patch after 6 minutes...
« Last Edit: June 16, 2021, 11:55:44 am by wp »
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

marcov

  • Administrator
  • Hero Member
  • *
  • Posts: 9534
  • FPC developer.
Re: TDBF fails to open DBF files with ftfloat correctly
« Reply #9 on: June 16, 2021, 12:23:20 pm »
(to my best knowledge TDBF was a Delphi package originally)

LacaK

  • Hero Member
  • *****
  • Posts: 637
Re: TDBF fails to open DBF files with ftfloat correctly
« Reply #10 on: June 16, 2021, 12:36:40 pm »
I reffered to bug report https://bugs.freepascal.org/view.php?id=38999
Where if there is no sufficient space for fixed format , exponential format us used as fallback.
(I understand that in case of large number there is probably no way how to handle it; either exponential form or raise exception)

wp

  • Hero Member
  • *****
  • Posts: 8749
Re: TDBF fails to open DBF files with ftfloat correctly
« Reply #11 on: June 16, 2021, 02:22:39 pm »
I see your point now. Checked LibreOffice Base, Access, the tool DBFPlus, as well as Delphi7+BDE - they all can read the file of the bug report (this file is written with the float field in exponential notation). I also varied the TableLevel when creating the file, and again all of the programs could read it (except for LibreOffice Base which failed reading the TL 7 file). Also tried to create such a file with Delphi7+BDE, which works as well.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

korba812

  • Full Member
  • ***
  • Posts: 187
Re: TDBF fails to open DBF files with ftfloat correctly
« Reply #12 on: June 16, 2021, 10:26:12 pm »
If I remember correctly in earlier versions of dbf format, the maximum size of a numeric field is just 20 characters (including sign and decimal separator). It was only increased to 32 in newer versions. However, it would be a good idea to control the correct size of fields when creating table with appropriate level.

LacaK

  • Hero Member
  • *****
  • Posts: 637
Re: TDBF fails to open DBF files with ftfloat correctly
« Reply #13 on: June 17, 2021, 07:51:59 am »
Checked LibreOffice Base, Access, the tool DBFPlus, as well as Delphi7+BDE - they all can read the file of the bug report (this file is written with the float field in exponential notation).
Ok then please mention this also in bug report.
As you can see also Michal commented your bug report, where complains that exponential form is not supported by "standard" DBF format.
From my POV it is acceptable use exponential form if it is supported by many other tools (of course best will be if officialy documented, but I think that DBF format is no more officialy maintained)

 

TinyPortal © 2005-2018