MySQL confusingly reports type of field and causes trouble with display formating
Original Reporter info from Mantis: mirce.vladimirov
-
Reporter name: Mirce Vladimirov
Original Reporter info from Mantis: mirce.vladimirov
- Reporter name: Mirce Vladimirov
Description:
in MySQL DB table a field is declared as DECIMAL(14,2). After executing a query which selects this field i tried to do
TFloatField(qveri.fieldbyname('saldo')).DisplayFormat:='0.00;-0.00;#';
and it works fine. But when my query is sumarising (like "select sum(a-b) as saldo group by..." ) the formating is not done. Tested same code under Delphi 7 with ODBC driver and with MyDAC driver and it worked OK. Tested under Lazarus with SQLite also worked OK. So now I wrote a small Lazarus program which answers the type of retrieved DB field:
//////////////////////////////////////////////////
Case qveri.FieldByName('saldo').DataType of
ftword : showmessage('1');
ftvariant : showmessage('2');
ftadt : showmessage('3');
ftbcd : showmessage('4');
ftFmtMemo : showmessage('5');
ftarray : showmessage('6');
ftunknown : showmessage('7');
ftautoinc : showmessage('8');
ftblob : showmessage('9');
ftboolean : showmessage('10');
ftbytes : showmessage('11');
ftcurrency : showmessage('12');
ftcursor : showmessage('13');
ftdataset : showmessage('14');
ftdate : showmessage('15');
ftdatetime : showmessage('16');
ftdbaseole : showmessage('17');
ftfixedchar : showmessage('18');
ftfixedwidechar : showmessage('19');
ftfloat : showmessage('20');
ftfmtbcd : showmessage('21');
ftgraphic : showmessage('22');
ftguid : showmessage('23');
ftidispatch : showmessage('24');
ftinteger : showmessage('25');
ftinterface : showmessage('26');
ftlargeint : showmessage('27');
ftmemo : showmessage('28');
ftorablob : showmessage('29');
ftoraclob : showmessage('30');
ftparadoxole : showmessage('31');
ftreference : showmessage('32');
ftsmallint : showmessage('33');
ftstring : showmessage('34');
fttime : showmessage('35');
fttimestamp : showmessage('36');
fttypedbinary : showmessage('37');
ftvarbytes : showmessage('38');
ftwidememo : showmessage('39');
ftwidestring : showmessage('40');
else showmessage('What ?');
end;
//////////////////////////////////////////////////
And the result is : non-sumarised field (the one that is formated succesefuly) is of type bcd, but the other one (that is not beeing formated) is of type fmtbcd.
When in my query i cast the field like
"select cast(sum(a-b) as DECIMAL(14,2)) as saldo group by..." then the resulting field is again reported as bcd and is succesefuly display formated.
Mantis conversion info:
- Mantis ID: 24096
- OS: windows
- OS Build: 7 and XP
- Version: 2.6.0
- Fixed in version: 2.6.4
- Fixed in revision: 25019 (#a0fa9605)
- Target version: 3.0.0