Sorting on DATE-field, specifically in TZReadOnlyQuery + fix

[geert]

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 :

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);      ///**********
[...]

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 :

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);        ///*********
[...]

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.

[marsupilami]

Hello geert,

I tried this change and it doesn't generate any errors in the test suite. But honestly I am way too tired to understand things right now.

Could you provide a small sample tabl where I can see the problem?

With best regards,

Jan

[marsupilami]

Checked into SVN for 7.2