[bug_report] FIREBIRD and NUMERIC/DECIMAL

In this forum all bug reports concerning the 6.x branch will be gahtered. You have the possibility to track the bug fix process.

Moderators: EgonHugeist, mdaems

Post Reply
ElPedro
Fresh Boarder
Fresh Boarder
Posts: 1
Joined: 21.11.2006, 11:29

[bug_report] FIREBIRD and NUMERIC/DECIMAL

Post by ElPedro »

Hi,

Could you please help me. I am using Delphi 6 / Zeos 6.5.1 / Firebird 1.5

CREATE TABLE xxx (
...
CENA NUMERIC(5, 2 NOT NULL,
CENADPH NUMERIC(5, 2) NOT NULL
...
);
OK

INSERT INTO xxx VALUES (... , 415.80, 625.90, ...);
OK

SELECT * FROM xxx;

DBGrid show :
CENA = 415.8000015654
CENADPH = 625.9000154654
:(

with IBDatabase component
DBGrid show
CENA = 415.8
CENADPH = 625.9

OK


Thanks very much
klchin
Senior Boarder
Senior Boarder
Posts: 65
Joined: 02.09.2005, 06:27

Post by klchin »

Hi,

I have the same problem for MySQL with older Zeos 6.15.
I think due to the convertion.

Regards,
KL Chin
Jay
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 03.03.2006, 03:16
Location: Mexico

Post by Jay »

When the TField component is generated, it generates a TFloatField component which is equivalent to a DOUBLE PRECISION. This only affects the Delphi component and not the actual database. It should be generating a TBCDField or a TFMTBCDField component instead.

Regards
Jose Ostos
klchin
Senior Boarder
Senior Boarder
Posts: 65
Joined: 02.09.2005, 06:27

Post by klchin »

Hi Jay,

Thx for your info.
I used C++Builder 5.0. I conncet to DBGrid, and it shown the
wrong value. I obtained the value by Field->AsString, it gave
the same problem also.

Regards,
KL Chin
davidb
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 21.11.2006, 04:42

Post by davidb »

I use Delphi, but I believe the concept is the same.
Right click on you Table or Query Component.
Add all the DB Fields.
Select your field and set the EditMask and FormatMask to 0.00

Your DBGrid will now display the way you want.
klchin
Senior Boarder
Senior Boarder
Posts: 65
Joined: 02.09.2005, 06:27

Post by klchin »

Hi Davidb,

Thx for your suggestion. It was good idea.
The had problem, bcoz the query was dynamic, table unknown.

If it do not connect to DBGrid, will the result correct if it obtain
from query.FieldByName( 'xxx' ).AsString?

Regards,
KL Chin
davidb
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 21.11.2006, 04:42

Post by davidb »

With Delphi you can create a dynamic Query and then add the field definition.
I will see if I can code this in delphi for you, so you can get an idea.
Will get back to you.
Regards
Dave
davidb
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 21.11.2006, 04:42

Post by davidb »

OK, Set up your Query Dynamically
then in Delphi but you can convert to C++

procedure TForm1.DataSource1DataChange(Sender: TObject; Field: TField);
begin
ADODataset1St_Amount.Editmask:='#.##';
end;
klchin
Senior Boarder
Senior Boarder
Posts: 65
Joined: 02.09.2005, 06:27

Post by klchin »

Hi Dave,

Thx for your code and idea, sound ok to me.
So, I needed to check for every field type b4 I apply the setting.

Regards,
KL Chin
User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Post by mdaems »

I move this to the bug reports forum. The test suite also finds this problem. (In my case for mysql), so it may be a general problem. I hope somebody finds out where it's going wrong.

Mark
Jay
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 03.03.2006, 03:16
Location: Mexico

Post by Jay »

The situacion is the TYPE of variable or FieldType that is being generated, which is a TFloatField. This is NORMAL behaviour for most data access components.

The fix being mentioned in this thread is ONLY functional for displaying purposes, e.g. display the value correctly, but internally, the value is still 415.8000015654 instead of 415.80 for use in calculations. If the the program reads many records and summing a total with these values, there will can diferences in the results.

This is a problem that has ALLWAYS existed in computers since the first computer ever invented (1950's).

The only REAL solution is to be able to OPTIONALLY specify in ZEOS to create TBCDField or TFmtBCDField types (which are more precise but slower) instead of TFloatField in case of NUMERIC or DECIMAL database field types.

The TFloatField type is equivalent to a "double" type variable and a TBCDField is equivalent to a "currency" type variable. In the help files you should be able to investigate the diferences between these types of variables.

Maybe next week I may be able to look into the sources and determine exactly what changes are necessary to be able to make this option available to developers. However, no matter what changes can be made to ZeosLib, the default behaviour still has to be same (Create TFloatField types) and be only overriden by a property specifying the Numeric Type desired.

Regards
Jose Ostos
Jay
Fresh Boarder
Fresh Boarder
Posts: 20
Joined: 03.03.2006, 03:16
Location: Mexico

Post by Jay »

I have looked deep into the sources and have not quite found exactly where the problem is, but it does appear to be a small bug in the interbase/firebird driver.

background: Decimal types are stored internally as some variation of an integer type depending on the precision and scale of the decimal. In the given example "DECIMAL(5,2)" it uses an integer (4 bytes) but a "DECIMAL(13,2" uses a Int64 (8 bytes).

I made tests with variables with both these diferent types and only the "DECIMAL(5,2)" produces this situation. In other words, the problem is only with decimals that are stored as a (4 byte) integer.

I have already spend about 16 hours with this without success. This requires someone with a lot of knowledge of the internal workings of the interbase/firebird driver to determine exactly at what point the data is converted from the record buffer to the actual field values and how this conversion is being done which is what needs to be fixed.

Sorry I could not be of more help or maybe someone can orient me in the right direction.

Regards
Jose Ostos
Post Reply