View Issue Details

IDProjectCategoryView StatusLast Update
0037979FPCDatabasepublic2020-10-28 15:33
ReporterRyan Smith Assigned ToLacaK  
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
Platformc86_64OSTested on Linux/Windows10 only 
Product Version3.2.0 
Fixed in Version3.3.1 
Summary0037979: TSQLQuery/TField bad UNSIGNED INT translation
DescriptionIn TField (as used by the mysql connector in TSQLQuery), when encountering an UNSIGNED INT DB field, it internally parses it as a normal INT32 (aka "Integer" in Pascal) and so a value in the DB of 3356830306 is returned as -938136990.

For TField.AsInteger this is confusing but not detrimental because the byte-pattern is maintained and it can be cast. though I'd prefer there to be a "TField.AsCardinal" or "TField.AsUInt" or some such.

However, TField.AsLargeint (which returns an Int64) still shows -938136990 for a DB value of 3356830306. This is bad. This also means that currently there is no TField.AsXxxxxx to retrieve the real value for that field and the only option is to cast it.

To make matters worse, in TField.DataType, there is no distinction for Unsigned fields, there are ftInteger and ftLargeint, but no ftUnsignedInteger or such so unless the programmer has full foreknowledge of the Schema, the parsing is guesswork.
(PS: If there is another way to know if the field is unsigned, please let me know).
Steps To Reproduce
In any MySQL/MariaDB DB:
CREATE TABLE UTest(a INT UNSIGNED);

INSERT INTO UTest(a) VALUES
 (1)
,(3356830306) -- This is the culprit, any value between 2147483648 and 4294967295 will do.
,(1000);

Connect to the DB with TSQLQuery and do a simple "SELECT a FROM UTest;" query, walk the rows and debug/writeln the "xxx.Fields[0].AsLargeint" value. For that second row, it will show -938136990.
Additional Information
In contrast, TField.AsFloat (which is a Double) correctly translates it to a float value of 3.356830306e+09 and indeed my current workaround is to do:
myInt64 := Round(TField.AsFloat);
but this needs a whole lot of extra checking isn't full INT64 precision, but for UINT32 values it is enough.
TagsNo tags attached.
Fixed in Revision47237
FPCOldBugId
FPCTarget-
Attached Files

Activities

LacaK

2020-10-26 08:33

developer   ~0126564

IMO here we must follow Delphi:
- property AsLongWord: Cardinal read GetAsLongWord write SetAsLongWord;
Extend TFieldType with additional types like
- ftLongWord, ... etc.

LacaK

2020-10-27 14:41

developer   ~0126583

TLongWordField and TField.AsLongWord added in rev.47219-47220
Now it can be used in TMySQLConnection for unsigned integer data type (of course TMySQLConnection must be adjusted)

Issue History

Date Modified Username Field Change
2020-10-24 12:22 Ryan Smith New Issue
2020-10-26 08:33 LacaK Note Added: 0126564
2020-10-27 14:41 LacaK Note Added: 0126583
2020-10-28 15:33 LacaK Assigned To => LacaK
2020-10-28 15:33 LacaK Status new => resolved
2020-10-28 15:33 LacaK Resolution open => fixed
2020-10-28 15:33 LacaK Fixed in Version => 3.3.1
2020-10-28 15:33 LacaK Fixed in Revision => 47237
2020-10-28 15:33 LacaK FPCTarget => -