Sorting on DATE-field, specifically in TZReadOnlyQuery + fix
Posted: 12.05.2017, 14:47
Database : MySQL
For TZReadOnlyQuery, not in TZQuery, sorting on a DATE field does not work correctly. So this problem is different from my topic posted yesterday "Sorting of TWideMemo-field"
The reason seems to be in ZDbcMySqlResultSet.pas, in method "GetTimestamp", which is called on sorting for a date, a time and a datetime field. I think it is remarkable that this only results in sorting problems, and not in date fields generally being read wrongly.
The original code :
seems to determine the type (date/time/datetime) by checking if the character at index 2 is a ':' , and comparing the field length to the length of a full datetime-string. My feeling is that this is not too robust, but apart from that, when the character at index 2 is NOT a ':', AND the field length is at least 4 less than the connection-settings-datetime-length, the field should be read as a DATE-field. But in that case the function 'RawSQLTimeToDateTime' is used.
I have changed this to :
which seems to solve my problem. (That is line 776 in r3986 )
Note that this same logic (and the same mistake) also occurs in the method TZAbstractMySQLPreparedResultSet.GetTimeStamp, in the case statement for the case FIELD_TYPE_STRING,FIELD_TYPE_ENUM,FIELD_TYPE_SET. So also line 2525 in r3986 should be changed accordingly, I think.
For TZReadOnlyQuery, not in TZQuery, sorting on a DATE field does not work correctly. So this problem is different from my topic posted yesterday "Sorting of TWideMemo-field"
The reason seems to be in ZDbcMySqlResultSet.pas, in method "GetTimestamp", which is called on sorting for a date, a time and a datetime field. I think it is remarkable that this only results in sorting problems, and not in date fields generally being read wrongly.
The original code :
Code: Select all
function TZAbstractMySQLResultSet.GetTimestamp(ColumnIndex: Integer): TDateTime;
[...]
if (Buffer+2)^ = ':' then
Result := RawSQLTimeToDateTime(Buffer, Len, ConSettings^.ReadFormatSettings, Failed{%H-})
else
if (ConSettings^.ReadFormatSettings.DateTimeFormatLen - Len) <= 4 then
Result := RawSQLTimeStampToDateTime(Buffer, Len, ConSettings^.ReadFormatSettings, Failed)
else
Result := RawSQLTimeToDateTime(Buffer, Len, ConSettings^.ReadFormatSettings, Failed); ///**********
[...]
I have changed this to :
Code: Select all
function TZAbstractMySQLResultSet.GetTimestamp(ColumnIndex: Integer): TDateTime;
[...]
if (Buffer+2)^ = ':' then
Result := RawSQLTimeToDateTime(Buffer, Len, ConSettings^.ReadFormatSettings, Failed{%H-})
else
if (ConSettings^.ReadFormatSettings.DateTimeFormatLen - Len) <= 4 then
Result := RawSQLTimeStampToDateTime(Buffer, Len, ConSettings^.ReadFormatSettings, Failed)
else
Result := RawSQLDateToDateTime(Buffer, Len, ConSettings^.ReadFormatSettings, Failed); ///*********
[...]
Note that this same logic (and the same mistake) also occurs in the method TZAbstractMySQLPreparedResultSet.GetTimeStamp, in the case statement for the case FIELD_TYPE_STRING,FIELD_TYPE_ENUM,FIELD_TYPE_SET. So also line 2525 in r3986 should be changed accordingly, I think.