Sorting on DATE-field, specifically in TZReadOnlyQuery + fix

The BETA tester's forum for ZeosLib 7.2 Report problems. Ask for help, post proposals for the new version and Zeoslib 7.2 features here. This is a forum that will be edited once the 7.2.x version goes into RC/stable!!

My personal intention for 7.2 is to speed up the internals as optimal a possible for all IDE's. Hope you can help?! Have fun with testing 7.2
geert
Fresh Boarder
Fresh Boarder
Posts: 10
Joined: 17.08.2005, 09:16
Location: Belgium
Contact:

Sorting on DATE-field, specifically in TZReadOnlyQuery + fix

Postby geert » 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 :

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
Gold Boarder
Gold Boarder
Posts: 625
Joined: 17.01.2011, 14:17

Re: Sorting on DATE-field, specifically in TZReadOnlyQuery + fix

Postby marsupilami » 17.05.2017, 18:54

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
Gold Boarder
Gold Boarder
Posts: 625
Joined: 17.01.2011, 14:17

Re: Sorting on DATE-field, specifically in TZReadOnlyQuery + fix

Postby marsupilami » 10.08.2017, 18:39

Checked into SVN for 7.2


Return to “ZeosLib 7.2 Betatest Forum”

Who is online

Users browsing this forum: No registered users and 1 guest