TSQLQuery/TField bad UNSIGNED INT translation
Original Reporter info from Mantis: Cuz
-
Reporter name: Ryan Smith
Original Reporter info from Mantis: Cuz
- Reporter name: Ryan Smith
Description:
In 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.
Mantis conversion info:
- Mantis ID: 37979
- OS: Tested on Linux/Windows10 only
- Platform: c86_64
- Version: 3.2.0
- Fixed in version: 3.3.1
- Fixed in revision: 47237 (#78e7ea6f)
- Monitored by: » Cuz (Ryan Smith)