Zeoslib Portal

Posted: **11.02.2009, 21:09**

I'm just trying to port some postgres DB to firebird 1.5 and found out that numeric(x,x) fields dont "show" correct results inside ZEOS componets when Firebird DB is used, eg:

Code: Select all

CREATE TABLE NUMTEST (
  NUMERIC6_3 NUMERIC(6, 3),
  DECIMAL6_3 DECIMAL(6, 3));
INSERT INTO NUMTEST(3.3, 3.3);

Result shown in dbGrid says: 3.29999995231628 instead 3.3

When underlying table is opened with IBX (or IBO), result is OK. Only with ZQuery/ZTable shows wrong values (but with postgres is fine).

This is huge error which makes ZEOSlib practically useless with Firebird, at least for applications that use decimal datatypes.
On the other hand I can't beleive that I'm only one who uses ZEOS/Firebird with tables with decimal fields.

Conclusion:I'm probably doing something wrong. Please help - enlighten me or confirm bug.

Posted: **12.02.2009, 22:29**

Only for numeric or also for decimal?
Problem probaby is about the precision of single/double datatypes somewhere in the flow of data in zeoslib.

Do you have some simple test project to show this? Unfortunately I have no firebird installed. So if I could run it against an embedded db that would be fine. (The disadvantage is that table creation should also be included in the test app for ease of use)

Mark

Posted: **13.02.2009, 15:08**

mdaems,

Do you have some simple test project to show this?

For some reason, Gmail won't allow me to send attached (zipped) source. Its small in size (<3kb), is it OK to attach it here, so everyone can test it?

Posted: **13.02.2009, 16:18**

Hi,
Usually I fix this using 7zip. Gmail doesn't block that.

It is fine to attach it to this message, BUT it would have been easier if you could mail me everything including an embedded server, of course.

You could also attach the small version to a (probably already existing) bug report in the bugtracker at zeosbugs.firmos.at . There the sample would not be lost in the (relative) mass of the forum posts.

Mark

Posted: **13.02.2009, 18:07**

Just sent everything, 7zipped

For those who want to confirm my suspicions that Firebirds NUMERIC/DECIMAL code is broken, here is attached "lite" test version, without embedded server (I'ts inside ZeosLib source).

Posted: **14.02.2009, 05:02**

I use with decimal and I always use formatFloat in Delphi for the numeric type eg: #,##0.## so it will be rounded by Delphi and user see the correct value. And for update that field from Delphi it also correct in the backend value. I think the problem is Zeos use single or double type for numeric field, I think it should be BCD type or currency type in Delphi??

trupka wrote:Just sent everything, 7zipped
For those who want to confirm my suspicions that Firebirds NUMERIC/DECIMAL code is broken, here is attached "lite" test version, without embedded server (I'ts inside ZeosLib source).

Posted: **19.02.2009, 13:57**

cipto_kh wrote:I use with decimal and I always use formatFloat in Delphi for the numeric type eg: #,##0.## so it will be rounded by Delphi and user see the correct value. And for update that field from Delphi it also correct in the backend value. I think the problem is Zeos use single or double type for numeric field, I think it should be BCD type or currency type in Delphi??

Thank you for hint but your solution is not adequate for me - precision inst high enough. I did few tests in last couple of days - now I'm sure it's a bug: http://zeosbugs.firmos.at/view.php?id=2.
Fieds in Delphi should be TBCD type but currently they are TFloat's.
I will try to locate and fix this in next few weeks.

Posted: **08.03.2009, 11:55**

That problem is located in unit ZDbcInterbase6Utils

function TZResultSQLDA.GetFloat(const Index: Integer): Single;
var
SQLCode: SmallInt;
begin
CheckRange(Index);
{$R-}
with FXSQLDA.sqlvar[Index] do
begin
Result := 0;
if (sqlind <> nil) and (sqlind^ = -1) then
Exit;
SQLCode := (sqltype and not(1));

if (sqlscale < 0) then
begin
case SQLCode of
SQL_SHORT : Result := PSmallInt(sqldata)^ / IBScaleDivisor[sqlscale];
SQL_LONG : Result := PInteger(sqldata)^ / IBScaleDivisor[sqlscale];
SQL_INT64,
SQL_QUAD : Result := PInt64(sqldata)^ / IBScaleDivisor[sqlscale];
SQL_DOUBLE : Result := PDouble(sqldata)^;
else
raise EZIBConvertError.Create(Format(SErrorConvertionField,
[GetFieldAliasName(Index), GetNameSqlType(SQLCode)]));
end;
end
else
case SQLCode of
SQL_DOUBLE : Result := PDouble(sqldata)^;
SQL_LONG : Result := PInteger(sqldata)^;
SQL_D_FLOAT,
SQL_FLOAT : Result := PSingle(sqldata)^;
SQL_BOOLEAN : Result := PSmallint(sqldata)^;
SQL_SHORT : Result := PSmallint(sqldata)^;
SQL_INT64 : Result := PInt64(sqldata)^;
SQL_TEXT : Result := StrToFloat(DecodeString(SQL_TEXT, Index));
SQL_VARYING : Result := StrToFloat(DecodeString(SQL_VARYING, Index));
else
raise EZIBConvertError.Create(Format(SErrorConvertionField,
[GetFieldAliasName(Index), GetNameSqlType(SQLCode)]));
end;
end;
{$IFOPT D+}
{$R+}
{$ENDIF}
end;

If you ask Delphi to evaluate it, it gives you '3.3' (which is correct)
but if you Result, at the end of the procedure is 3.29999995231628

Really strange behaviour

Posted: **09.03.2009, 23:02**

I did some research and now I can tell that problem is that TZResultSQLDA.GetFloat(const Index: Integer): Single;
returns (obviously

) single float value which isn't precise enough. All other databases returns double precision. Changing function result type to double would solve the problem, but currently I'm not sure that this will completely resolve bug.
I'm studying related code to see what other changes are necessary - currently I'm only sure that interface declarations must also be changed. Patch will probably be a little bit large.

BTW, can anyone tell me what is BCD Field type status ins Zeoslib? It seems that Zeos dont work with T*BCD* field types but I dont know is it by design or it is missing feature?

Posted: **15.04.2009, 14:42**

trupka,

I first compiled your sample program yesterday. (BTW : this was a very nice sample, thanks for that).

I added a second connection component to a mysql database for comparison and there it works like expected because the stDouble is used for these fields.

As a quick test for this case I did this modification:

Code: Select all

function TZSQLDA.GetFieldSqlType(const Index: Word): TZSQLType;
var
  SqlScale: Integer;
  SqlSubType: Integer;
begin
  SqlScale := GetFieldScale(Index);
  SqlSubType := GetIbSqlSubType(Index);

  case GetIbSqlType(Index) of
    SQL_VARYING, SQL_TEXT: Result := stString;
    SQL_LONG:
      begin
        if SqlScale = 0 then
          Result := stInteger
//mdaems        else if Abs(SqlScale) <= 4 then
//mdaems          Result := stFloat
        else
          Result := stDouble;
      end;
    SQL_SHORT:
      begin
        if SqlScale = 0 then
          Result := stShort
        else
          Result := stFloat;
      end;
    SQL_FLOAT,//mdaems: Result := stFloat;
    SQL_DOUBLE: Result := stDouble;
    SQL_DATE: Result := stTimestamp;
    SQL_TYPE_TIME: Result := stTime;
    SQL_TYPE_DATE: Result := stDate;
    SQL_INT64:
      begin
        if SqlScale = 0 then
          Result := stLong
        else if Abs(SqlScale) <= 4 then
          Result := stDouble
        else
          Result := stBigDecimal;
      end;
    SQL_QUAD, SQL_ARRAY, SQL_BLOB:
      begin
        if SqlSubType = isc_blob_text then
          Result := stAsciiStream
        else
          Result := stBinaryStream;
      end;
    //SQL_ARRAY: Result := stBytes;
  else
      Result := stString;
  end;
end;

This makes your test program work and doesn't break the test suite. Now we must find out if this is a reasonable solution and what we should do with the SQL_SHORT case in the code above. Another question remains what should be done with function ConvertInterbase6ToSqlType as this one does a similar conversion, I believe.

Mark

Posted: **16.04.2009, 00:36**

Did some more research as to where stFloat is used. Seems for all drivers except for interbase it's not used as datatype to receive database data unless the presision in the database is VERY low.
I suppose my proposed change will do the trick and is simlar to the implementatios for other databases.
Remaining questions:
- ConvertInterbase6ToSqlType behaviour
- Would ftBCD be possible? Recent fpc versions also support this. So probably yes.
- would the proposed change be safe for the 6.6-patches branch?

Mark

Posted: **18.04.2009, 00:36**

I committed my changes to testing branch. (http://fisheye2.atlassian.com/changelog/zeos/?cs=628) Can somebody encourage me to put them in the 6.6-patches branch as well?

Concerning the ConvertInterbase6ToSqlType behaviour : I replaced the stFloats by stDouble there too.

About adding ftBCD : will somebody give this a try?

Mark

BTW : this corresponds to bug report 2

Posted: **18.04.2009, 13:46**

If you explain to me where download it, I can give a try or write a test

Posted: **19.04.2009, 19:55**

Seawolf, there's no packaged version yet, but I propose you patch it yourself in your current version to try. It's no 'nuclear science'. If you look at the url above you can see exactly what I changed to solve the issue.

Concerning the test : I think there was already a test for this problem before, but it has long been failing and was ignored because the result was wrong for firebird. When looking into this problem I found how to repair the test to make it relevant again and indeed:
- before the float/double fix the test fails correctly
- after the fixthe test passes.
The patch to repair the test can you see here

I'm currently experimenting with Bcd fields, but that isn't ready yet.

Mark

Zeoslib Portal

Firebird NUMERICs problem

Firebird NUMERICs problem