Display zero value in TZFMTBCDField

[brick08]

Hi all.
I have MS SQL Server 2019, Lazarus 2.0.10 and Zeos 8.0 last trunk.
I create fields on the server, for example NUMERIC(18,5). It is defined as TZFMTBCDField. When displaying in grid, the value is 0, its look like as '00000000.00'. Although a TZBCDField look like as '0'.
I noticed that after update ZQuery it look like as '0' and ZQuerystNUMERIC18_5.AsBCD.Precision equal 0 and ZQuerystNUMERIC18_5.AsBCD.SignSpecialPlaces equal 0. But after refresh ZQuerystNUMERIC18_5.AsBCD.Precision equal 10 and ZQuerystNUMERIC18_5.AsBCD.SignSpecialPlaces equal 2, and so its look like as '00000000.00'. Is it bug?

And question 2.
Why fields created as NUMERIC(18,3) and NUMERIC(18,5) in ZQuery autocreated as TZFMTBCDField, but field created as NUMERIC(18,4) autocreated as TZBCDField?
P.S. Sorry for my English.

[marsupilami]

Hello brick

Hi all.
I have MS SQL Server 2019, Lazarus 2.0.10 and Zeos 8.0 last trunk.
I create fields on the server, for example NUMERIC(18,5). It is defined as TZFMTBCDField. When displaying in grid, the value is 0, its look like as '00000000.00'. Although a TZBCDField look like as '0'.
I noticed that after update ZQuery it look like as '0' and ZQuerystNUMERIC18_5.AsBCD.Precision equal 0 and ZQuerystNUMERIC18_5.AsBCD.SignSpecialPlaces equal 0. But after refresh ZQuerystNUMERIC18_5.AsBCD.Precision equal 10 and ZQuerystNUMERIC18_5.AsBCD.SignSpecialPlaces equal 2, and so its look like as '00000000.00'. Is it bug?

This looks like a bug to me. Could you try to create a little example program that shows the problem? That usually greatly helps solving the problem.

And question 2.
Why fields created as NUMERIC(18,3) and NUMERIC(18,5) in ZQuery autocreated as TZFMTBCDField, but field created as NUMERIC(18,4) autocreated as TZBCDField?
P.S. Sorry for my English.

This is because of the precision. TBCDField internally uses the Currency data type. It can hold up to 4 decimal digits. So Zeos uses it for Numeric(18,1) to Numeric(18,4). If there are more than 4 decimal digits, we have to use TFMTBCDField because then we need the extra precision of the TBCD type. Zeos mirrors these decisions in its own fields. TZBCDField uses the currency data type internally and TZFMTBCDField uses TBCD internally.
Even though we could use TZFMTBCDField for all Numeric type fields, we use TZBCDField if we can because it usually is faster.

Best regards,

Jan

[brick08]

TBCDField internally uses the Currency data type. It can hold up to 4 decimal digits. So Zeos uses it for Numeric(18,1) to Numeric(18,4)

No. Zeos autocreated as TBCDField only Numeric(18,4), and Numeric(18,1),(18,2),(18,3) it created as TFMTBCDField.

I attached small example. See it, please

[EgonHugeist]

@brick08

all values having a precision of 18 and decimals <> 4 can't be represented as BCD(binary coded decimal alias System.Currency).
Reason is simple because the Currency is a Int64 having a fixed scale of 4.
Means a values with a Scale of 5 f.e. can't fit into the Currency whithout precision loss. Values having a scale < 4 can simply overrun the Int64 because we can save only 18 didgits total, and the scale needs to be multiplied by 10 by each missing scale digit.
You can use Values like NUMERIC(17,3) and NUMERIC(16,2) and NUMERIC(15,1) to keep the BCD range. Everything else will be mapped to the slow FMTBCD.

Anyway the Delphi defaults for the TBCD records are desturbing you. The FPC TBCD representation is scientific wheras the Delphi representation is compact/packed. I'll check your example as soon as i've time. As an alternative you can define your own format to represent the values i guess?

[brick08]

As an alternative you can define your own format to represent the values i guess?

Even if i use DisplayFormat = '#0.#', zero value in FMTBCD still shows how '00000000.00'.
I just want to display '0' for default in FMTBCD fields.

[marsupilami]

Hello brick08,

Egonhugeist has committed a fix for your problem in Zeos trunk in revision 7227. Could you please check if that solves your problem?

Best regards,

Jan

[brick08]

Could you please check if that solves your problem?

Yes, now it's all right. thank you.

[brick08]

In order not to create a new topic, I will write here.
When setting a column property DisplayFormat = '0.#', for FMTBCD type fields, integer values are displayed with a separator, although BCD type fields are displayed without it.
Can you fix this?

[EgonHugeist]

Hi,

i'm not willing to fix or replace the FPC FormatBCD method. But i did override the SetAsLargeInt/SetAsCurrency methods in the TZFMTBCDField. I'm always leftpacking the values. So test https://sourceforge.net/p/zeoslib/code-0/7317/. Did that help? Just a hint: remove the display format.

To be fair it would be nice if you file a bugreport to: https://bugs.freepascal.org/my_view_page.php

[brick08]

Did that help?

No, Integer values are still displayed with a separator. The program does not stop at a breakpoint in the SetAsBCD procedure, although it does stop at TZFMTBCDField.GetText. I think the procedure TZFMTBCDField.SetAsBCD is not being executed.

To be fair it would be nice if you file a bugreport to: https://bugs.freepascal.org/my_view_page.php

I wrote there. https://bugs.freepascal.org/view.php?id=38489
I'll wait.