MySQL v.5.0 .3+ and Decimals

waheed · Post by **waheed** » 19.06.2006, 11:27

Before the forum crash I posted a problem of zeos seeing aggregate results of decimal values as string. No one seems to be able to solve the problem then. I'm reposting the problem since it is a major PROBLEM to using 5.0.

Post by **mdaems** » 19.06.2006, 12:47

Hi Waheed.
I'll add your bug to the bug list, so we can't forget it when your post becomes old.
I can't promise somebody finds/takes time to figure it out immediately.

Mark

fduenas · Post by **fduenas** » 19.06.2006, 17:40

Hi waheed. what does this bug makes excatly?

can you share somne sample or explain it more widely?

fduenas · Post by **fduenas** » 20.06.2006, 01:11

what version are you using? did you tried latest rleased version or SVN testing branch version?

waheed · Post by **waheed** » 20.06.2006, 10:53

I'm using ZEOS ver 6.5.1 as indicated in the ZConnection and I updated on May 17, 2006.
I wasn't able to update from SVN today.

Mysql 5.0.20a
Delphi 6 update 2
WinXP SP2

noelc · Post by **noelc** » 20.06.2006, 11:32

Can you post some sample Delphi code and SQL DDL/DML?

waheed · Post by **waheed** » 20.06.2006, 12:17

Create this table on the MySQL 5 server and MySQL 4 server:

Code: Select all

CREATE TABLE `table1` (
  `field1` decimal(8,3) default NULL,
  `field2` tinyint(4) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

Here is some data:

Code: Select all

INSERT INTO `table1` (`field1`, `field2`) VALUES 
  (131, 2),
  (15359, 1),
  (11731, 1),
  (15174, 2),
  (14507, 1),
  (6794, 1),
  (11747, 2),
  (6741, 1),
  (18688, 1),
  (16715, 1),
  (16987, 1),
  (14170, 1),
  (10932, 1),
  (19756, 2),
  (5233, 1),
  (14605, 1),
  (12163, 1),
  (18071, 1),
  (6399, 2),
  (11739, 1),
  (14730, 1),
  (9256, 1),
  (10574, 1),
  (4159, 1),
  (10703, 1),
  (13618, 1);

Paste the following on a new form.

Code: Select all

object ZConnection1: TZConnection
  Protocol = 'mysql-4.1'
  HostName = 'localhost'
  Port = 3306
  Database = 'testdb'
  User = 'root'
  Connected = True
  Left = 120
  Top = 32
end
object ZQuery1: TZQuery
  Connection = ZConnection1
  SQL.Strings = (
    'select sum(case field2'
    '               WHEN 1 THEN field1'
    '               WHEN 2 THEN -1.0 * field1'
    '             END) AS AddDedSum'
    'from table1'
    'group by field2')
  Params = <>
  Left = 208
  Top = 32
  object ZQuery1AddDedSum: TStringField
    FieldName = 'AddDedSum'
    Size = 34
  end
end

Try to change protocols and you will get an error when you open the query in mysql-5 protocol and no error in mysql-4.1 protocol.

Also when in protocol mysql-4.1 add the field to the fields editor you will find that the field is recognized as string.
But when you have the same table in MySQL 4.1+ server the field is recognized as Float which is what it should be!!!

fduenas · Post by **fduenas** » 20.06.2006, 16:00

FIXED!!!

OK. This is a bug that was introduced in 5.0, ir returns a new field type named NEWDECIMAL. I have made the corrections and seems to work. I sent them to mdaems so he can post it to SVN testing branch

So if for anyone who can't download the latest SVN testing branch sources, you can do the next steps:

1) you have to add this new constant to the section of ZMySQLPlainDriver.pas Where all FIELD_TYPES are defined, add it after the FIELD_TYPE_NEWDATE constant.

Code: Select all

FIELD_TYPE_NEWDECIMAL = 246; //<--ADDED for 4.1 and UP 20-06-2006

2) Preferently also add this field type constant to all ZPlainMySQLXXX.pas files

3) In file ZDbcMySQLUtils.pas change a line in the function ConvertMySQLHandleToSQLType(PlainDriver: IZMySQLPlainDriver;
FieldHandle: PZMySQLField; FieldFlags: Integer): TZSQLType;

In the Case Block where the FIELD_TYPE_DECIMAL constant is located change:
this line

Code: Select all

 FIELD_TYPE_DECIMAL:
    begin

to this line

Code: Select all

 FIELD_TYPE_DECIMAL, FIELD_TYPE_NEWDECIMAL: {ADDED  20-06-2006}
    begin

4) save all files and recompile all the packages

I have tested it and works

Regards
Francisco Dueñas

Post by **mdaems** » 20.06.2006, 22:52

Hi, I'm right now uploading the newest SVN versions to mdaems . In a few minutes it should be there. This bug should be fixed in SVN testing revision 66 (second group of ZEOS files on my webspace).
Can somebody confirm succes? Then I can close the bug report.

Mark

waheed · Post by **waheed** » 24.06.2006, 12:28

Done thanks a lot.

fduenas · Post by **fduenas** » 25.06.2006, 19:08

Hi waheed. Can you confirm it in the buglist section?. so mdaems can close it.
Thanks

Post by **mdaems** » 25.06.2006, 20:23

Not needed to do this anymore. I've already seen this.
I read about everything that passes by and the 'bug' symbol in the bugreport has already been changed to a green check mark.
But indeed, I hoped to receive a quick message in the bugreports section.

Mark