Help with some bugs in UTF8 mode.

[MJFShark]

Hey all!

I've been doing some work in the Oracle part of Zeoslib relatd to clobs. I have changes almost ready to show but ran into a couple bugs when testing in UTF8 mode. It was driving me bonkers until I realized that the bugs also exist in the current code and weren't caused by my new stuff. It looks like both bugs relate to how clobs are handled depending on the codepage settings (this is not my area of expertise! lol) The two bugs are:

Bug #1: Reading a clob in "OnLoad" cachelobmode and UTF8 fails (it seems to return an empty string.)
Bug #2: Editing a clob field in a dataset in "None" cachelobmode and UTF8 gives the error:
ORA-22292: Cannot open a LOB in read-write mode without a transaction

My new version which I hope to have done soon fixes item #1 so maybe no need to spend much time looking into it. But it does not fix bug #2 and I haven't yet found out how to fix it, though I've narrowed things down to possibly the TZCodePageConversionStream stuff in function TZAbstractOracleBlob.CreateLobStream.

I have a small dpr sample program I'll try to include in the next message (I seem to have issues with the forum rejecting my sample code submissions.)

-Mark

[MJFShark]

Sample code to show the two bugs:

Code: Select all

program ZeosOraClobUTF8;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  System.Classes,
  System.Diagnostics,
  ZDbcIntfs,
  ZDbcProperties,
  ZConnection,
  ZDataset;

var
  ZConn: TZConnection;
  ZQuery: TZQuery;
  TempString: String;
  i: integer;
  RecID: Integer;
  RecAClob: String;
begin
  try
    ZConn := TZConnection.Create(nil);
    ZConn.Protocol := 'Oracle';
    ZConn.User := 'michelle';
    ZConn.Password := 'mycoolpw';
    ZConn.Database := 'benthic_tp';
    {$IFDEF WIN64}
      ZConn.LibraryLocation := 'C:\U\DbClients\Oracle\21.8\64Bit\oci.dll';
    {$ELSE}
      ZConn.LibraryLocation := 'C:\U\DbClients\Oracle\21.8\32Bit\oci.dll';
    {$ENDIF}
    // Set OCI to multithreaded (even though this test is not.)
    ZConn.Properties.Values[ConnProps_OCIMultiThreaded] := 'True';
    // Note that all of this works well in utf16 mode.
    // ZConn.Properties.Values[ConnProps_CodePage] := 'UTF16';
    ZConn.Connect;

    // Create table if it doesn't exist
    try try

      Writeln('Attempting to create sample table.');
      ZConn.ExecuteDirect('create table zclobtest (id number(38) primary key, aclob clob)');
      // Now fill some data.
      begin
        Writeln('Filling sample table.');
        ZQuery := TZQuery.Create(nil);
        try
          ZQuery.Connection := ZConn;
          ZConn.StartTransaction;
          ZQuery.SQL.Text := 'insert into zclobtest values (:id, :aclob)';
          for i := 1 to 10 do
          begin
            ZQuery.Params[0].AsInteger := i;
            ZQuery.Params[1].AsString := FormatFloat('000#',i) + ' ' + TempString.PadRight(Random(10000), 'Z');
            ZQuery.ExecSQL;
          end;
          ZConn.Commit;
        finally
          ZQuery.Free;
        end;
      end;
    except
      // Table probably just already exists.
    end;

    begin
      Writeln('');
      Writeln('Test bug #1: failure reading a clob in OnLoad LobCacheMode and utf8.');
      ZQuery := TZQuery.Create(nil);
      try
        ZQuery.Connection := ZConn;
        ZQuery.Properties.Values[DSProps_LobCacheMode] := 'OnLoad';
        ZQuery.SQL.Text := 'select id, aclob from zclobtest order by id';
        ZQuery.Open;
        if not ZQuery.Eof then
        begin
          RecID := ZQuery.Fields[0].AsInteger;
          RecAClob := ZQuery.Fields[1].AsString;
          if RecAClob = '' then
            Writeln('Clob failed: Empty.')
          else
          begin
            TempString := RecAClob.Split([' '])[0];
            if (TempString = '') or (TempString.ToInteger <> RecID) then
              Writeln('Clob failed: Integrity test.')
            else
              Writeln('Clob read correctly!');
          end;
        end;
      finally
        ZQuery.Free;
      end;
    end;
    
    begin
      Writeln('');
      Writeln('Test bug #2:  writing a clob in "None" LobCacheMode and utf8.');
      ZQuery := TZQuery.Create(nil);
      try
        ZQuery.Connection := ZConn;
        ZQuery.Properties.Values[DSProps_LobCacheMode] := 'OnNone';
        ZQuery.SQL.Text := 'select id, aclob from zclobtest order by id';
        ZQuery.Open;
        if not ZQuery.Eof then
        begin
          RecID := ZQuery.Fields[0].AsInteger;
          RecAClob := ZQuery.Fields[1].AsString;
          ZQuery.First;
          ZQuery.Edit;
          TempString := '0001 Edited: ' + DateTimeToStr(Now);
          // In OnNone and UTF8 the following line should fail with 
          // ORA-22292: Cannot open a LOB in read-write mode without a transaction
          ZQuery.Fields[1].AsString := TempString;
          ZQuery.Post;
          ZQuery.RefreshCurrentRow(True);
          if ZQuery.Fields[1].AsString <> TempString then
            Writeln('Clob write failed.')
          else
            Writeln('Clob write succeeded.');
         
        end;
      finally
        ZQuery.Free;
      end;
    end;

    Writeln('Done');

  finally
    ZConn.Disconnect;
    ZConn.Free;
  end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;

  Writeln('');
  Write('Press Enter to quit...');
  ReadLn;

end.

-Mark

[MJFShark]

I should add that my dev environment is Delphi 11.3 and my Oracle Client is 21.8 with an Oracle 19 server.

-Mark

[MJFShark]

Found it. In the constructor for TZCodePageConversionStream it checks the size of the stream. The problem is that this is before the TemporaryLob has been created (which happens in "BeforeWrite".) A quick fix is to check for this specific case in the lobsteam's GetSize and return zero. This seems to work and avoids changing TZCodePageConversionStream.

-Mark