New fiture request: ZQuery OnProgress event

[zpatyi]

Hi all !

I would like to add a new event to TZAbstractRODatas. Included is the procedure that fires an event when the rows are read by the component. This would be good for updating a ProgressBar during a long-lasting query. Unfortunately, my knowledge is not enough to create the OnProgress event declarations, so I ask for help.

Code: Select all

function TZAbstractRODataset.FetchRows(RowCount: Integer): Boolean;
begin
  Connection.ShowSQLHourGlass;
  try
    if RowCount = 0 then begin
      while FetchOneRow do begin
        if Assigned(OnProgress) then      // modification, addition 
           OnProgress(Self);                     // to orgonal code
      end;
      Result := True;
    end else begin
      while (CurrentRows.Count < RowCount) do begin
        if FetchOneRow then begin
          if Assigned(OnProgress) then]    // modification, addition 
            OnProgress(Self);                     // to orgonal code
         end else
           Break;
      end;
      Result := CurrentRows.Count >= RowCount;
    end;
  finally
    Connection.HideSQLHourGlass;
  end;
end;

So, I want to create a new public event: OnProgress, is fired every time when a row is fetched.
On this event i can update and redraw the PrograssBar or similar control.

[marsupilami]

Hello zpatyi,

you just need to declare things Your code would look similar to this:

Code: Select all

interface

type 

  // some type declarations here 

  TZAbstractTZAbstractRODataset = Class({Whatever class it derives from})
  private
    // here are all the existing private declarations...
  protected
    // here are all the exiting protected declarations
    FOnProgress: TNotifyEvent; // this is te added variable
  public
    // all the public declarations are here
   published
     // all the existing properties are here
     property OnProgress: TNotifyEvent read FOnProgress write FOnProgress;
   end;

   // more type declarations here   

implementation

// a lot of procedures and functions here...

function TZAbstractRODataset.FetchRows(RowCount: Integer): Boolean;
begin
  Connection.ShowSQLHourGlass;
  try
    if RowCount = 0 then begin
      while FetchOneRow do begin
        if Assigned(FOnProgress) then      // modification, addition // note - I used the new variable here
           FOnProgress(Self);                     // to orgonal code
      end;
      Result := True;
    end else begin
      while (CurrentRows.Count < RowCount) do begin
        if FetchOneRow then begin
          if Assigned(FOnProgress) then    // modification, addition // note - I used the new variable here
            FOnProgress(Self);                     // to orgonal code
         end else
           Break;
      end;
      Result := CurrentRows.Count >= RowCount;
    end;
  finally
    Connection.HideSQLHourGlass;
  end;
end;

// even more procedures and functons here

Note: Unfortunately using TNotifyEvent doesn't allow to transport information like how many rows are read and how many rows there are still to read. You might want to add your own notification declaration. Also depending on your database and network connection it might be the client library that takes a lot of time to wait for the complete result set from the server. Also calling FOnProgress on every row might slow things down a lot. You might want to introduce some code that makes sure FOnProgress only gets called ever 100 rows or something like that.

Best regards,

Jan

[Fr0sT]

Pretty nice idea!
Some more ideas:
- call event with currentrow/rowcount parameters
- add Cancel flag as well (though it should be carefully checked!)
- add a field / option to fire only on N-th iteration (nevertheless, I doubt that firing for every row will slow things down too much for usual cases with hundreds of rows. But for cases of huge datasets we should supply the option).

[aehimself]

Please, please, please!!!!

I had to re-write my DB management app to use datasources / DBGrids because of performance issues (manually building up a ListView with each row was painfully slow on 10-100k rows) but with this I lost the chance to show progress indicators. I was just looking for a way to achieve this two days ago!

[marsupilami]

Please, please, please!!!!

I had to re-write my DB management app to use datasources / DBGrids because of performance issues (manually building up a ListView with each row was painfully slow on 10-100k rows) but with this I lost the chance to show progress indicators. I was just looking for a way to achieve this two days ago!

Hello, I think, that the usefulness of this feature highly depends on the database in use:
- the dblib driver caches the result set data before the result set is returned by the statement. Effectively that means - on progress would only work after all the data has been transferred from the server.
- PostgreSQL ans maybe MySQL cache the whole result set in their libraries before we get to access it. So this leads to the same problem.
- Firebird might work. I am not sure how the cached result set and the native Firebird result set work on this.

So - which drivers are we talking about?

Best regards,

Jan

[aehimself]

Nice, I did not get a notification about a reply in the thread...

At home I'm using MySQL and at work we mainly use Oracle and a small fraction of MSSQL.
But I guess IF this will be implemented, it'll be implemented for all / most of the supported protocols, no?

[marsupilami]

At home I'm using MySQL and at work we mainly use Oracle and a small fraction of MSSQL.
But I guess IF this will be implemented, it'll be implemented for all / most of the supported protocols, no?

I meant to say that I have doubts wether this feature would help anybody at all - at least for database systems where all data needs to be transferred from the server to the client before we get to read the first row. Currently we use MySQL in exactly that way. For Oracle I am not sure. Most drivers in Zeos currently work like this:

1. Send Query to server
2. Server transfers whole result set into client memory
3. Zeos reads result step by step into its own cache.

We could work with an OnProgress event only during the last step. I am not sure if this really helps anybody who has large result sets.

Currently this feature is not high on my list of priorities. But I am willing to support anybody who wants to have a go at implementing this.