AfterFetch handler being called too early / usage of NonBlocking mode / run code when loading of all records is finished

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
pcz
Posts: 81
Joined: Tue 04 Aug 2015 12:53

Re: Proper usage of NonBlocking query mode / fire code when all records are fetched

Post by pcz » Tue 26 Apr 2016 13:13

Unit1.dfm

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 198
  ClientWidth = 543
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 360
    Top = 123
    Width = 81
    Height = 13
    Caption = 'Records in query'
  end
  object Memo1: TMemo
    Left = 8
    Top = 8
    Width = 320
    Height = 169
    Lines.Strings = (
      'Memo1')
    TabOrder = 0
  end
  object Button1: TButton
    Left = 360
    Top = 152
    Width = 162
    Height = 25
    Caption = 'Open'
    TabOrder = 1
    OnClick = Button1Click
  end
  object SpinEdit1: TSpinEdit
    Left = 447
    Top = 120
    Width = 75
    Height = 22
    MaxValue = 0
    MinValue = 0
    TabOrder = 2
    Value = 24
  end
  object OraQuery1: TOraQuery
    SQL.Strings = (
      'SELECT sysdate a, sysdate b'
      'FROM dual'
      'CONNECT BY LEVEL <= :param')
    AfterFetch = OraQuery1AfterFetch
    Left = 144
    Top = 72
    ParamData = <
      item
        DataType = ftInteger
        Name = 'param'
        Value = nil
      end>
    object OraQuery1A: TDateTimeField
      FieldName = 'A'
    end
    object OraQuery1B: TDateTimeField
      FieldName = 'B'
    end
  end
  object OraSession1: TOraSession
    ConnectDialog = ConnectDialog1
    Left = 144
    Top = 16
  end
  object ConnectDialog1: TConnectDialog
    Caption = 'Connect'
    ConnectButton = 'Connect'
    CancelButton = 'Cancel'
    Server.Caption = 'Server'
    Server.Visible = True
    Server.Order = 1
    UserName.Caption = 'User Name'
    UserName.Visible = True
    UserName.Order = 2
    Password.Caption = 'Password'
    Password.Visible = True
    Password.Order = 3
    Home.Caption = 'Home Name'
    Home.Visible = False
    Home.Order = 0
    Direct.Caption = 'Direct'
    Direct.Visible = False
    Direct.Order = 6
    Schema.Caption = 'Schema'
    Schema.Visible = False
    Schema.Order = 4
    Role.Caption = 'Connect Mode'
    Role.Visible = False
    Role.Order = 5
    Left = 248
    Top = 64
  end
end

Unit1.pas

Code: Select all

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, DBAccess, OdacVcl, OraCall,
  Vcl.Forms, Data.DB, Ora, MemDS, Vcl.Samples.Spin, Vcl.StdCtrls, Vcl.Controls;

type
  TForm1 = class(TForm)
    OraQuery1: TOraQuery;
    Memo1: TMemo;
    OraSession1: TOraSession;
    Button1: TButton;
    ConnectDialog1: TConnectDialog;
    SpinEdit1: TSpinEdit;
    OraQuery1A: TDateTimeField;
    OraQuery1B: TDateTimeField;
    Label1: TLabel;
    procedure OraQuery1AfterFetch(DataSet: TCustomDADataSet);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
    procedure Log(const s: string);
    procedure LogVariable(const name, value: string);
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  OraQuery1.Close;
  Memo1.Lines.Clear;

  OraQuery1.FetchAll := True;
  OraQuery1.NonBlocking := True;

  OraQuery1.Params[0].Value := SpinEdit1.Value;
  OraQuery1.Open;
end;

procedure TForm1.Log(const s: string);
begin
  Memo1.Lines.Add(s);
end;

procedure TForm1.LogVariable(const name, value: string);
begin
  Memo1.Lines.Add(name + ': ' + value);
end;

procedure TForm1.OraQuery1AfterFetch(DataSet: TCustomDADataSet);
begin
  Log('##### AfterFetch #####');
  LogVariable('  FetchRows', IntToStr(OraQuery1.FetchRows));
  LogVariable('  Fetching', BoolToStr(DataSet.Fetching, True));
  LogVariable('  Fetched', BoolToStr(DataSet.Fetched, True));
  LogVariable('  OraQuery1A.Value', OraQuery1A.AsString);
  case DataSet.Fetched of
    False: Log('DATASET NOT READY');
    True: Log('DATASET READY');
  end;

  if SpinEdit1.Value < OraQuery1.FetchRows then
  begin
    if OraQuery1A.AsString = '' then
      Log('>> BUG <<')
    else
      Log('The problem is fixed!');
  end;end;

end.
The code above reproduces problem every time without any data-aware controls
OraQuery1A.Value should not be empty for all param values below 25

pcz
Posts: 81
Joined: Tue 04 Aug 2015 12:53

Re: Proper usage of NonBlocking query mode / fire code when all records are fetched

Post by pcz » Mon 23 May 2016 09:19

[just bumping this subject...]

Any comment?...

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Proper usage of NonBlocking query mode / fire code when all records are fetched

Post by AlexP » Mon 30 May 2016 09:11

The AfterFetch event occurs immediately after data is retrieved, and after that the DataSet is filled in. We will consider the possibility to change this behavior.

pcz
Posts: 81
Joined: Tue 04 Aug 2015 12:53

Re: Proper usage of NonBlocking query mode / fire code when all records are fetched

Post by pcz » Mon 30 May 2016 09:52

AlexP wrote:We will consider the possibility to change this behavior.
Thanks for answer

1) Is there any hope that any potential changes could be contained in next ODAC update?

2) Is it possible to make some temporary McGyvering to have some code executed after DataSet has been "filled"

3) If You will not want to change current behaviour (for example bacause some backward compatibility reasons) maybe it is worth considering to just add one extra event fired once (in opposite to AfterFetch) when all of requested data is fetched and DataSet is "100% ready".
It could make a bit easier to use NonBlocking mode... (no need for bloating code with "if not DataSet.Fetching then...")

pcz
Posts: 81
Joined: Tue 04 Aug 2015 12:53

Re: Proper usage of NonBlocking query mode / fire code when all records are fetched

Post by pcz » Mon 20 Jun 2016 10:08

Any news on this subject?...


Sorry for constant asking but current behaviour is just making the NonBlocking mode a bit useless feature

I am unable to to use TOraQuery for queries with long execution time because I can't give my customers application that works random depending on number of records

Code: Select all

procedure TForm1.TOraQuery1AfterFetch(DataSet: TCustomDADataSet);
begin
  if not DataSet.Fetching then
  begin

    if not DataSet.IsEmpty then
      // enable buttons here...    => not working when too few records

    // field content based resize of grid columns    => not working when too few records...

  end;
end;
I think it's just a bit pointless to create another threaded wrapper to fix class that currently supports threads
Maybe there is some possible clean and quick patch?

pcz
Posts: 81
Joined: Tue 04 Aug 2015 12:53

Re: Proper usage of NonBlocking query mode / run code when loading of all records is finished

Post by pcz » Mon 04 Jul 2016 13:54

Updating ODAC to version 9.7.25
(9.7.25-27-Jun-16 Bug with Refresh and Locate in the NonBlocking mode is fixed)
is very likely to cause that code

Code: Select all

procedure TForm1.OraQuery1AfterFetch(DataSet: TCustomDADataSet);
begin
  if not DataSet.Fetching then
  begin
    // [field content based resize of grid columns goes here]
    // I guess it probably uses <TDataSet>.Locate...
  end;
end;
is always raising EDatabaseError "TOraQuery1: Record not found" after calling

Code: Select all

OraQuery1.Refresh;
when returned record count is 1 or when I just add

Code: Select all

...and rownum = 1
into WHERE clause... :(

pcz
Posts: 81
Joined: Tue 04 Aug 2015 12:53

Re: Proper usage of NonBlocking query mode / run code when loading of all records is finished

Post by pcz » Fri 29 Jul 2016 09:03

Anything new on this subject?...

I'd be really grateful if You can really do something about this :roll:

I'd really like to take advantages of NonBlocking mode without bothering about hacks to prevent output randomness caused by race condition...

pcz
Posts: 81
Joined: Tue 04 Aug 2015 12:53

Re: AfterFetch handler being called too early / usage of NonBlocking mode / run code when loading of all records is finished

Post by pcz » Fri 29 Jul 2016 13:45

Ok.... another attempt because this issue gets really annoying for me

Can You tell me if this nasty hack will fix every case of premature AfterFetch handler call?

Code: Select all

unit OraFix;

interface

uses
  Data.DB,   // TResyncMode
  DBAccess,   // TAfterFetchEvent
  Ora;

type
  TFixedOraQuery = class(TOraQuery)
  private
    FInvokeAfterFetchHandler: Boolean;
  protected
    procedure DoOnAfterFetch; override;
  public
    procedure Resync(Mode: TResyncMode); override;
  end;

implementation

procedure TFixedOraQuery.DoOnAfterFetch;
var
  EventHandler: TAfterFetchEvent;
begin
  if NonBlocking and FetchAll and Fetched and (RecordCount < FetchRows) then
  begin
    FInvokeAfterFetchHandler := True;
    EventHandler := AfterFetch;
    AfterFetch := nil;
  end;
  try
    inherited;
  finally
    if FInvokeAfterFetchHandler then
      AfterFetch := EventHandler;
  end;
end;

procedure TFixedOraQuery.Resync(Mode: TResyncMode);
begin
  inherited;
  if FInvokeAfterFetchHandler then
  begin
    FInvokeAfterFetchHandler := False;
    if Assigned(AfterFetch) then
      AfterFetch(Self);
  end;
end;

end.

Code: Select all

unit Unit1;

interface

uses
  ...
  OraFix;

type
  TOraQuery = class(TFixedOraQuery);

  TForm1 = class(TForm)
    OraQuery1: TOraQuery;
...

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: AfterFetch handler being called too early / usage of NonBlocking mode / run code when loading of all records is finished

Post by AlexP » Mon 12 Sep 2016 08:47

Yes, you can use this approach to get the needed behavior.

Post Reply