Zeoslib Portal

Posted: **11.05.2017, 16:35**

Database used : MySQL.

When in a TZQuery, the field name of a ftWideMemo-column (from a MySQL TEXT datatype) is assigned to SortedFields, sorting does not work. It did work in 7.1.4 though, and it also works in a TZReadOnlyQuery.

The problem can be tracked down to the following code in ZDbcCache, I think, where in case of a stAsciiStream or an stUnicodeStream (to which the memo-field translates) no alternative is given for CompareNothing when CompareKind is ckAscending or ckDescending. Only the ckEquals case is considered there.

Can anyone point me in the correct direction to fix this ? I am not sure if I can copy some code of the stString/stUnicodeString case to the stAsciiStream/stUnicodeStream case to make sorting work again ?

Code: Select all


function TZRowAccessor.GetCompareFunc(ColumnIndex: Integer;
  const CompareKind: TComparisonKind): TCompareFunc;
begin
  Result := CompareNothing;
  case FColumnTypes[ColumnIndex{$IFNDEF GENERIC_INDEX}-1{$ENDIF}] of
    stBoolean:
      case CompareKind of
[...]

    stString, stUnicodeString:

[...]

    stAsciiStream, stUnicodeStream:
      if CompareKind = ckEquals then
        if ConSettings^.CPType in [cCP_UTF16, cCP_UTF8] then
          Result := CompareUnicodeCLob_Equals
        else
          Result := CompareNativeCLob_Equals;
  end;
end;

Posted: **11.05.2017, 20:55**

Hello geert,

I would assume you can use the same functions that are used for Unicode strings. Just make sure that the MemoFields are retrieved as Widestrings when they are compared.

With best regards,

Jan

Posted: **12.05.2017, 10:33**

Jan,

thank you for your response. About your remark

Just make sure that the MemoFields are retrieved as Widestrings

Since all this happens in ZDbcCache, as far as I understand, everything has already been retrieved at that point.

So what I did (and what seems to work in my setup) is :

1) Taking the case stString, stUnicodeString as an example In ZDbcCache.pas, method GetCompareFunc, I replaced

Code: Select all

   stAsciiStream, stUnicodeStream:
      if CompareKind = ckEquals then
        if ConSettings^.CPType in [cCP_UTF16, cCP_UTF8] then
          Result := CompareUnicodeCLob_Equals
        else
          Result := CompareNativeCLob_Equals;

by :

Code: Select all

    stAsciiStream, stUnicodeStream:
        if ConSettings^.CPType in [cCP_UTF16, cCP_UTF8] then
            case CompareKind of
              ckAscending: Result := CompareUnicodeCLob_Asc;
              ckDescending: Result := CompareUnicodeCLob_Desc;
              ckEquals: Result := CompareUnicodeCLob_Equals;
            end;
        else
            case CompareKind of
              ckAscending: Result := CompareNativeCLob_Asc;
              ckDescending: Result := CompareNativeCLob_Desc;
              ckEquals: Result := CompareNativeCLob_Equals;
            end;
       end;

Then I added :

Code: Select all

function CompareUnicodeCLob_Asc(const {%H-}Null1, Null2: Boolean; const V1, V2): Integer;
var
  Blob1, Blob2: IZBlob;
  BlobEmpty1, BlobEmpty2: Boolean;
  {$IFDEF MSWINDOWS}
  ValuePtr1, ValuePtr2: Pointer;
  {$ENDIF}
begin
  Blob1 := IZBlob(PPointer(V1)^);
  BlobEmpty1 := (Blob1 = nil) or (Blob1.IsEmpty);
  Blob2 := IZBlob(PPointer(V2)^);
  BlobEmpty2 := (Blob2 = nil) or (Blob2.IsEmpty);
  if BlobEmpty1 and BlobEmpty2 then Result := 0
  else if BlobEmpty1 then Result := 1
  else if BlobEmpty2 then Result := -1
  else if Blob1.IsClob and Blob2.IsClob then
    begin
      {$IFDEF MSWINDOWS}
      ValuePtr1 := Blob1.GetPWideChar;
      ValuePtr2 := Blob2.GetPWideChar;
      SetLastError(0);
      Result := CompareStringW(LOCALE_USER_DEFAULT, 0,
        ValuePtr1, Blob1.Length, ValuePtr2, Blob2.Length) - 2{CSTR_EQUAL};
      if GetLastError <> 0 then RaiseLastOSError;
      {$ELSE}
      Result := WideCompareStr(Blob1.GetUnicodeString, Blob2.GetUnicodeString);
      {$ENDIF}
    end
  else
      Result := {$IFDEF WITH_UNITANSISTRINGS}AnsiStrings.{$ENDIF}AnsiCompareStr(Blob1.GetString, Blob2.GetString)
end;


function CompareUnicodeCLob_Desc(const {%H-}Null1, Null2: Boolean; const V1, V2): Integer;
begin
  Result:=-CompareUnicodeClob_Asc(Null1,Null2,V1,V2);
end;

and also :

Code: Select all


function CompareNativeCLob_Asc(const Null1, Null2: Boolean; const V1, V2): Integer;
var
  Blob1, Blob2: IZBlob;
  BlobEmpty1, BlobEmpty2: Boolean;
begin
  Blob1 := IZBlob(PPointer(V1)^);
  BlobEmpty1 := (Blob1 = nil) or (Blob1.IsEmpty);
  Blob2 := IZBlob(PPointer(V2)^);
  BlobEmpty2 := (Blob2 = nil) or (Blob2.IsEmpty);
  if BlobEmpty1 and BlobEmpty2 then Result := 0
  else if (BlobEmpty1) then Result := 1
  else if (BlobEmpty2) then Result := -1
  else
    Result := {$IFDEF WITH_UNITANSISTRINGS}AnsiStrings.{$ENDIF}AnsiCompareStr(Blob1.GetString, Blob2.GetString);
end;

function CompareNativeCLob_Desc(const Null1, Null2: Boolean; const V1, V2): Integer;
begin
  Result := -CompareNativeCLob_Asc(Null1, Null2, V1, V2);
end;

I must note that in both Compare...Clob_Asc-functions, for which I have taken the Compare...Clob_Equals functions as an example, I have removed the "if Blob1.IsUpdated or Blob2.IsUpdated" which is present in the ..._Equals functions before the "WideCompareStr". I do not fully understand why it is present there, so maybe I was not correct in removing it ?

However, this now seems to work for me.
(I will create a new topic later on today about a similar problem in TZROQuery and TDate fields)

Posted: **17.05.2017, 18:51**

Hello geert,

I checked your change into the SVN today. Thank you for helping make Zeos better.
With best regards,

Jan

Zeoslib Portal

Sorting of TWideMemo-field

Sorting of TWideMemo-field

Re: Sorting of TWideMemo-field

Re: Sorting of TWideMemo-field

Re: Sorting of TWideMemo-field