Precision issue

[magnomp]

First, I'm using D2007, Zeos 6.6.3 and FB 2.0 (I don't think it's caused by FB, though).
I'm trying to insert a record in a table with a numeric(18,7) field. For some values on this field, everything goes fine, but there are some values which show an issue regarding precision. For exemple: 2.16. When I try to store this value on that field, the actual stored value is 2.1600001.
I managed to reproduce it this way:

Code: Select all

uses
  ZDbcIntfs, ZDbcInterbase6;

procedure TForm1.Button1Click(Sender: TObject);
var
  Stmt: IZPreparedStatement;
  RS: IZResultSet;
  Con: IZConnection;
begin
  Con := DriverManager.GetConnection('Put here your connection string to a FB 2.0 database');

  Stmt := Con.PrepareStatement('insert into test_table(test_field) values (?)');
  Stmt.SetFloat(1, 2.16);
  Stmt.ExecuteUpdatePrepared;

  RS := Con.CreateStatement.ExecuteQuery('select test_field from test_table');
  RS.Next;
  ShowMessage(RS.GetString(1));
end;

Before run this test, just create a table named test_table with one numeric(18, 7) field called test_field.

Unfortunatelly I can't look to another way to put the value on the prepared statement because in my real case I'm not acessing the prepared statement directly, instead i'm using data aware controls and clientdataset linked to a tzreadonlyfield, so all the prepared statement stuff happens behind the scenes.

[seawolf]

This indeed is a Firebird problem (present in FB 2.1.1 too)
If you don't believe me set, for that field, a default value, and then add a new record. For that field last number is a 1 as you noted.

[magnomp]

It haven't happened to me. I've set 10 has the default value for that field and inserted a new record without that field. After I did a select and saw the right value on the field. First I tried with IBExpert and after I tried with isql and both showed the right value.

In fact, I know it's not a FB issue because we did the same test on the same database using DBX and it was OK.

[magnomp]

In fact, I know it's not a FB issue because we did the same test on the same database using DBX and it was OK.

Just to clarify: Here I'm talking about the test that I described on my first post, not the one proposed by seawolf

[magnomp]

I've found a workarround and posted this issue on zeos bug tracker:
http://zeosbugs.firmos.at/view.php?id=162

[seawolf]

I think that solution can be good, but the problem is:

procedure MyProc
var
MySinglevar : single;
begin
MySingleVar := 2.16
Stmt.SetFloat(1, MySingleVar);
....

If you debug this this you will note that MySingleVar is equal to 2.160001
despite the fact you have assign it to 2.16

This behaviour is caused by the round calculation done by Delphi (4 bytes after the decimal point)
Using a float var in place of a Single does not change this behaviour

So using a variant variable in place of a single is what other components probably do

[magnomp]

The point is: Single is smaller than Double. So, when receiving a Double value, Zeos must use a Double var to store this value internally in order to support any possible value it can get.
In other words: If you call TParam.AsFloat (just to remember, it returns a Double), you must store the returned value in a Double var instead of a Single var because you just don't know whether Single is big enough to store that value.

[mdaems]

Patch from magnomp has been committed to SVN. Will probably be available in next 6.6 maintenance release.

Mark