Hello.
I have this problem:

My database (Firebird) has a table with the following fields: ...
rTitle DECIMAL(8,4) not null,
rDiameter DECIMAL(10,6) not null,
...

In my program I have a Form with a a TZTable object (ZEOS) called zData, bound to that table.
In a routine I have the following code:

zData.Insert;
...
zData.FieldByName('rTitle').AsFloat := 0.12;
zData.FieldByName('rDiameter').AsFloat := 0.12;
...
zData.Post;

Then, at the table, I see that the values ??are stored as follows:

rTitle = *0.1199*
rDiameter = 0.12

Do not know why rTitle is 0.1199 instead of 0.12
I need the saved values ??are accurate.
I've tried AsFloat, AsCurrency, Value, always with the same result.

Any idea?

Tks.
Daniel.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140603/d30ff7d8/attachment-0001.html>

generally, if one needs accurate values he does not use real values ... use integer (or word, longint, longword etc) ... then when needed make the conversion or formating (for computing or printing) ...

(it
may exists another solution ... someone else may help better for that!)

Philippe

Daniel Erles wrote

Hello. I

have this problem:

My database (Firebird) has a table with the

following fields:

... rTitle DECIMAL(8,4) not null, rDiameter

DECIMAL(10,6) not null,

... In my program I have a Form with a

a TZTable object (ZEOS) called zData, bound to that table.

In a

routine I have the following code:

zData.Insert; ...

zData.FieldByName('rTitle').AsFloat := 0.12;

zData.FieldByName('rDiameter').AsFloat := 0.12;

0.1199 instead of 0.12

I need the saved values ??are accurate.

I've tried AsFloat, AsCurrency, Value, always with the same result.

depending of the needs and the tool you may use string fields as well. (for example, Java will make the conversion in a numerical expression)

Philippe

Daniel Erles wrote

Hello.

I have this problem: My database (Firebird) has a table

with the following fields:

... rTitle DECIMAL(8,4) not null,

rDiameter DECIMAL(10,6) not null,

... In my program I have a

Form with a a TZTable object (ZEOS) called zData, bound to that table.

zData.FieldByName('rTitle').AsFloat := 0.12;

zData.FieldByName('rDiameter').AsFloat := 0.12;

0.1199 instead of 0.12

I need the saved values ??are accurate.

I've tried AsFloat, AsCurrency, Value, always with the same result.

That's pretty strange. I'm using zeos + postgresql with numerics and never had such problem. Are you sure that rTitle field is numeric ? If it's float then it could be something like 0.1199.

zeljko

A suggestion: Try to alter your column from: rTitle DECIMAL(8,4) not null *to* rTitle DECIMAL(8,2) not null.
I think the rounding occurs because you have four numbers after the decimal point. If you need that precision, you need to input 0.1200 for your database to store it at that exact value

I tried your case in PostgreSQL, and it store as is without the rounding problem.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140604/aa82a566/attachment-0001.html> -------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4267 bytes
Desc: S/MIME Cryptographic Signature
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140604/aa82a566/attachment-0001.bin>

My thinking is quite the opposite. I am not a mathematician though. :) What if we need a high degree of precision in our computation? So we need this type of figure: #####.##### the more precision(accuracy) we need, the more digits we add after the decimal point. Integer store whole numbers w/o any fraction components.
Since I am using PosgreSQL, according to the docs:

"The type numeric can store numbers with a very large number of digits and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required. However, arithmetic on numeric values is very slow compared to the integer types, or to the floating-point types described in the next section. "

The numeric data type must be equivalent of Firebird's decimal data type.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140604/7b4fb071/attachment.html> -------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4267 bytes
Desc: S/MIME Cryptographic Signature
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140604/7b4fb071/attachment.bin>

mysolution is
CREATE DOMAIN D_REAL AS DOUBLE PRECISION;
and always not null & default value exactly "0.0" use tfields.displayformat zeljko wrote, thats conversion problem. can problem be in longint too, tparam.aslongint or tparam.aslargeint and tfield.aslongint or tfield.aslargeint aswell

03-06-2014 23:39 tarihinde, Daniel Erles yazd?:

If you need 5 digits, i.e. decimals: multiply by 100.000 when storing and divide by 100.000 when loading the value. Computers work on binary numbers and decimal 1.2 (= 1+2/10) has no exact representation in binary.

Mattias

That's why numeric type is used. Saving field as integer and divide it while loading is stupid if you have correct numeric type in db.

zeljko

Le 3/06/2014 22:39, Daniel Erles a ?crit :

Well it's not easy indeed. In the case of Firebird (it's salso my case) you have to take into account the sql dialect which makes a big difference when interfacing numeric values. With sql dialect 1 all numeric (and decimal) types are output as binary floating point (sirgle or double dependgng on column precision) figures. In sql dialect 3 decimal, numeric, money and dates are output as integer values ranging from single byte to int64 with all integer values in between, all depending on database presions (dates are always output as int64). This means that there are always fixed implied decimal positions. Have a look in http://ibexpert.net/ibe/index.php?n=Doc.SQLDialect . There are some other differences as well.

On the other side with Lazarus (even Delphi) the recipent containers are always double or extended depending on the interface used, leading to TFloatField definitions anyway. Sometimes a TBCDField is implemented. In all this stuff I've been used to use the math function RoundTo as the number of decimals is always known (think on accounting computations, invoicing, etc.)..

As a matter of fact, In my developments I always use sql dialect 3 in the database along with numeric or decimal types when decimals are needed (read http://www.firebirdfaq.org/faq340/), extended type in Lazarus and Delphi variables and RoundTo (where applicable) for intermediate results. There is also a subtle difference in rounding whether you use bank figures or acconting figures. But that is another story

Antonio.

---
Ce courrier ?lectronique ne contient aucun virus ou logiciel malveillant parce que la protection avast! Antivirus est active. http://www.avast.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140604/59ee8194/attachment-0001.html>

zeljko: the rTitle is defined as DECIMAL(8,4), for sure. Allan Registos: I need 4 decimal precision on that field. And I tried to input 0.1200, got same wrong value inserted: 0.1199

Strange. I tried many values and y found this happens:

...
inserting 0.06 stored as 0.0599
0.07 --> 0.07
0.08 --> 0.08
0.09 --> 0.0899
0.10 --> 0.10
0.11 --> 0.11
0.12 --> 0.1199
0.13 --> 0.13
0.14 --> 0.14
0.15 --> 0.1499
0.16 --> 0.16
0.17 --> 0.17
0.18 --> 0.1799
...

there's a patron here. Could this be a bug ?

Lazarus: 1.0.10
FPC: 2.6.2
Firebird: 2.5
ZeoisLib: 7.0.4

2014-06-04 5:08 GMT-04:00 zeljko :

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140604/cc913993/attachment.html>

Yep, there's a pattern. And Mattias explained the pattern in his post.

And no, this is not a bug. But as designed. You'll need a TBCDField to read these decimal-type values. Or you can use a currency-field. (That is: if Zeos did implement those fields right)

Joost.

Hi Daniel,
have you tried to use function Round in te assignment of the value?? RegardsPoncho

Date: Tue, 3 Jun 2014 16:39:30 -0400
From:
To:
Subject: [Lazarus] Problem rounding values at post

Hello. I have this problem:
My database (Firebird) has a table with the following fields: ... rTitle DECIMAL(8,4) not null,rDiameter DECIMAL(10,6) not null, ...
In my program I have a Form with a a TZTable object (ZEOS) called zData, bound to that table. In a routine I have the following code: zData.Insert;
...zData.FieldByName('rTitle').AsFloat := 0.12; zData.FieldByName('rDiameter').AsFloat := 0.12; ...zData.Post; Then, at the table, I see that the values ??are stored as follows:

rTitle = 0.1199rDiameter = 0.12
Do not know why rTitle is 0.1199 instead of 0.12 I need the saved values ??are accurate. I've tried AsFloat, AsCurrency, Value, always with the same result.

Any idea?
Tks. Daniel.

Yes, I tried with round too.

Now I understand the problem, thanks to the explanation of Mattias. But I could not find a solution yet.

The problem only occurs when I insert data using INSERT and POST functions. I tried creating an SQL INSERT, and running it with ExecuteQuery (), and the values ??are saved correctly. The problem apparently is not in Firebird, but in ZEOS classes implementation, or their ancestors.

I think maybe I'll look for a different approach to solve my problem, perhaps with the use of BCD fields. We'll see.

2014-06-04 11:55 GMT-04:00 Poncho Vel?zquez :

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140604/5de0dac9/attachment-0001.html>

Okay. So, my confusion starts with what the data type the DB recommends when precision is needed and the data type built with Lazarus/fpc. If accuracy and decimals are needed, one must use in Lazarus Integer, and deal with the digits with another integer and merge it as one data type and becomes numeric for storing at the DB side?

Or anyway, as Joost mentioned in his post, one must deal with a TBCDField to read the input values if Zeos provided this correctly.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4267 bytes
Desc: S/MIME Cryptographic Signature
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140605/24476be5/attachment.bin>

I think you can try to do SQL by hand and connect to Firebird directly with your SQL queries as a workaround without using Zeos or sqldb.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140605/1099b18e/attachment-0001.html> -------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4267 bytes
Desc: S/MIME Cryptographic Signature
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140605/1099b18e/attachment-0001.bin>