Problem with ZeosLib 8.00 Delphi Alexandria ZDbcCache

[sglodek]

Hello,

my code worked fine with Delphi 10.4 and ZeosLib 7.2.14 and Firebird 2.5. But I need ZeosLib for Delphi 11 and only found 8.00-beta. Maybe there is a stable version I didn't find?

In ZeosLib 8.00 I get a problem with ZDBcCache using this code which uses a prepared SQL-String, adds a where-clause and reopen the query:

Code: Select all

    with theDataset as TZQuery do
    begin
      Close;
      SQL.Clear;
      SQL.Add(ReaderSQL +
          ' where upper(coalesce(Barcode,'''') || coalesce(LastName,'''') ||coalesce(firstname,'''')||coalesce(username,'''')) like ''%' + theFilter + '%''');
      open;

The resulting sql is this if theFilter is 'JOHN':

Code: Select all

select r.*,
 (select cu.lastname from readers cu where cu.id = r.createuser) as CreateUserName,
 (select uu.lastname from readers uu where uu.id = r.updateuser) as UpdateUserName, 
 left(cast(r.info as varchar(4000)),100) InfoAsString 
 from readers r
 where upper(coalesce(Barcode,'') || coalesce(LastName,'') ||coalesce(firstname,'')||coalesce(username,'')) like '%JOHN%';

The open of the query leads to an access violation in Unit ZDbcCache procedure TZRowAccessor.SetNull(ColumnIndex: Integer);

Code: Select all

procedure TZRowAccessor.SetNull(ColumnIndex: Integer);
var Data: PPointer;
begin
{$IFNDEF DISABLE_CHECKING}
  CheckColumnIndex(ColumnIndex);
{$ENDIF}
  {$R-}
  if (FBuffer.Columns[FColumnOffsets[ColumnIndex{$IFNDEF GENERIC_INDEX} - 1{$ENDIF}]] = bIsNotNull) then begin  <<-- Access violation in this line
    Data := @FBuffer.Columns[FColumnOffsets[ColumnIndex{$IFNDEF GENERIC_INDEX} - 1{$ENDIF}] + 1];
    FBuffer.Columns[FColumnOffsets[ColumnIndex{$IFNDEF GENERIC_INDEX} - 1{$ENDIF}]] := bIsNull;
    {$IFDEF RangeCheckEnabled}{$R+}{$ENDIF}
    case FColumnTypes[ColumnIndex{$IFNDEF GENERIC_INDEX} - 1{$ENDIF}] of
      stAsciiStream, stBinaryStream, stUnicodeStream:
          PIZLob(Data)^ := nil;
      stBytes, stString, stUnicodeString:
        if Data^ <> nil then begin
          System.FreeMem(Data^);
          Data^ := nil;
        end;
      {$IFDEF WITH_CASE_WARNING}else ;{$ENDIF}
    end;
  end;
end;

I use the same way of coding (take a prepared sql, close the query, add "where" to the sql and reopen it) with ZeosLib 8.0.0-beta for 5 other tables and it workes. Any Idea what goes wrong here?

Thanks for any hint to solve this problem.
Siegbert

[aehimself]

Siegbert,

8.0 is a beta only because the documentation is incomplete as far as I'm aware; technically it's as close to stable as it gets. I'm not saying the issue is not in Zeos (from the information you given I do suspect something on this side) but the changes between 7.2 and 8.0 are enormous. It's also noteworthy that Zeos heavily relies on the library to provide information, which they can do incorrectly (I recall MySQL 5.5, older Oracle libs, even some FreeTDS builds had severe issues).
Therefore, until you can give a viable test case team Zeos is in the blind, basically. Protocol, client version, server version, codepages, data types, amount of records, usually contents of fields can make a difference.

If you can make the exception to be thrown consecutively you can try to put a breakpoint on the line and check each and every condition which can raise an AV:

Code: Select all

if (FBuffer.Columns[FColumnOffsets[ColumnIndex{$IFNDEF GENERIC_INDEX} - 1{$ENDIF}]] = bIsNotNull)

- What is the amount of items in FBuffer.Columns?
- What is the amount of items in FColumnOffsets?
- What does ColumnIndex ( -1 ) return?
Once you nailed it down, it's only a matter of backtracking... where that comes from and why that amount? Is it correct?

If you don't have the time or urge to look into it, the minimum what will be needed is:
- Library version in use
- DB create script with dummy data loaded if needed
- A minimal Delphi application to make the call to .Open to fail each and every time

[miab3]

As an aside, Zeos 7.2.14 patch has packages for Delphi 11.

Michał

[sglodek]

Hello,

@miab3:thank you for this hint - i found the patch in the subversion-tab :-)

@aehimself: thank you for thie hint to get some more information. I was able to find out that the error is not happening in the suspected SQL, but in a query that is connected as a slave to the master query.
Here is then columnindex = 37 and ColumnCount=1

I am trying to find out more details. As a workaround, I adjusted the affected line in the code so that if ColumnIndex-1>ColumnCount, the procedure is exited without further code execution.

regards,
Siegbert

[sglodek]

... with 7.2.14 patch for Delphi 11 all works fine!
Thanks to miab3 for the hint to that patch