[bug_fixed] MySQL and FLOAT fields (precision)

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

AHUser
Fresh Boarder
Fresh Boarder
Posts: 12
Joined: 11.04.2006, 00:33

[bug_fixed] MySQL and FLOAT fields (precision)

Postby AHUser » 24.11.2006, 22:08

Zeos Version: SVN/testing

I have a table that contains FLOAT fields. These fields contains values like '4.85'.
The MySQL server returns '4.85' but the result set puts this string into a single precision data type that cannot hold the information. So I get something like 4.84999990463 what is not 4.85 when converted back into a string (with Field.AsString)
I know that the MySQL FLOAT data type represents a Pascal Single, but when using a Double the precession problem doesn't occur.

Would there be any problem with the change from stFloat to stDouble in ZDbcMySqlUtils.ConvertMySQLHandleToSQLType and ZDbcMySqlUtils.ConvertMySQLTypeToSQLType ?
You only get more precision. The dataset's TFloatField class type remains.

klchin
Senior Boarder
Senior Boarder
Posts: 65
Joined: 02.09.2005, 06:27

Postby klchin » 25.11.2006, 07:36

Hi,

Pls check this link http://zeos.firmos.at/viewtopic.php?t=919

Regards,
KL Chin

bangfauzan
Senior Boarder
Senior Boarder
Posts: 50
Joined: 31.08.2006, 10:41
Contact:

Postby bangfauzan » 26.11.2006, 04:57

Hi AHUser.

the problem you mentioned comes from MySQL it self, not from Zeoslib.
Please read MySQL manual regarding the foloat and double type.
MySQL AB recognized about the problem

currently, the only solution of the problem is changing the float type
to double type in your each table.

Regards.

klchin
Senior Boarder
Senior Boarder
Posts: 65
Joined: 02.09.2005, 06:27

Postby klchin » 26.11.2006, 07:37

Hi ALLs,

FYI, the same problem did happended with MsSQL also.

Regards,
KL Chin

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

Postby mdaems » 30.11.2006, 01:06

Hi,
I received this private message today:
The problem reported with MySql was with the "FLOAT" type and not with the "DECIMAL" type. I created a test table with fields

Data1 DECIMAL(5,2),
Data2 DECIMAL(13,2),
Data3 FLOAT,
Data4 DOUBLE,

The problem ocurrs only with "Data3" (FLOAT type) as reported.

In this case I made 2 modifications to make it work, both are in the ZDbcMySqlUtils.pas source file:

function ConvertMySQLHandleToSQLType(PlainDriver: IZMySQLPlainDriver;
FieldHandle: PZMySQLField; FieldFlags: Integer): TZSQLType;
begin
case PlainDriver.GetFieldType(FieldHandle) of
FIELD_TYPE_TINY:
begin
if (UNSIGNED_FLAG and FieldFlags) = 0 then
Result := stByte
else Result := stShort;
end;
FIELD_TYPE_YEAR, FIELD_TYPE_SHORT:
begin
if (UNSIGNED_FLAG and FieldFlags) = 0 then
Result := stShort
else Result := stInteger;
end;
FIELD_TYPE_INT24, FIELD_TYPE_LONG:
begin
if (UNSIGNED_FLAG and FieldFlags) = 0 then
Result := stInteger
else Result := stLong;
end;
FIELD_TYPE_LONGLONG:
begin
if (UNSIGNED_FLAG and FieldFlags) = 0 then
Result := stLong
else Result := stBigDecimal;
end;
FIELD_TYPE_FLOAT:
Result := stFloat; // Line Number 178

Change Line Number 178 to

Result := stDouble;


The second Change is in:
function ConvertMySQLTypeToSQLType(TypeName, TypeNameFull: string): TZSQLType;



else if TypeName = 'FLOAT' then
begin
if IsUnsigned then
Result := stDouble
else Result := stFloat; // Line number 276
end

replace line number 276 with :

else Result := stDouble;

These changes appear to fix the problem. Again, I don't know it these changes break anything else.

Klchin also reports this happening in MSSQL. I don't have MSSQL so I can't check this.

I think that what should be done is to check the source code of ALL drivers (ZDbcXXXXXUtils.pas) looking for any assignment of (stFloat) to a Type and replace them to a (stDouble) Type and that should fix the problem for whatever driver is being used.

I hope you can check these fixes and apply them to the next version.

Please advise me if you have further doubts or comments.

Regards
Jose Ostos


I did this and it works very well (D7 on Mysql5), so I committed this change to SVN Testing branch (Rev 186).

Mark

klchin
Senior Boarder
Senior Boarder
Posts: 65
Joined: 02.09.2005, 06:27

Postby klchin » 30.11.2006, 06:44

Hi Mark,

Thx for your help. I will verify the code.

Regards,
KL Chin

klchin
Senior Boarder
Senior Boarder
Posts: 65
Joined: 02.09.2005, 06:27

Postby klchin » 07.12.2006, 06:03

Hi Mark,

Sorry for the late testing.

How about the type was Real for ConvertMySQLTypeToSQLType ?

For MsSQL, I was using the ADO connection, but under the pas I did
not see any similar convertion, what needed to be changes?


Regards,
KL Chin

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

Postby mdaems » 13.12.2006, 13:41

Hi klchin,
Can you explain what you mean by your last message?

Can I close this bug report? It at least fixed the bug we were getting for some time with the test suite.

Mark

klchin
Senior Boarder
Senior Boarder
Posts: 65
Joined: 02.09.2005, 06:27

Postby klchin » 13.12.2006, 17:49

Hi Mark,

Sorry for the un-clear message, what I meant was for MySQL I can easy
patch the code, but for ADO/MsSQL I cannot get the similar code.

Yes, you can close for bug for MySQL.

Thx.

Regards,
KL Chin


Return to “Bug Reports”

Who is online

Users browsing this forum: No registered users and 1 guest