A way show progress how many records were fetched so far?

[sel]

Hello,

I am using IBDac 3.5. I am fetching large amounts of records from a database (obviously firebird) in order to use them in a Devex PivotGrid.

Since sometimes I might have to fetch hundreds of thousands of records, I would like to show the user a progress message, displaying the number of records fetched so far.

Is there an event (I can't find any) or a way to fire an event that would update the message every (say) 1000 records fetched?

Thank you.

[AndreyZ]

Hello,

You can get such functionality using the FetchAll and FetchRows properties, and the AfterFetch event handler. Here is a code example:

Code: Select all

procedure TMainForm.BitBtnOpenClick(Sender: TObject);
begin
  IBCQuery.SQL.Text := 'select * from big_table';
  IBCQuery.FetchAll := False;
  IBCQuery.FetchRows := 1000;
  IBCQuery.Open;
  IBCQuery.Last; // fetch all table
end;

procedure TMainForm.IBCQueryAfterFetch(DataSet: TCustomDADataSet);
begin
  lProgress.Caption := 'Count of fetched records: ' + IntToStr(IBCQuery.RecordCount); // lProgress is the TLabel component
  lProgress.Refresh;
end;

[sel]

Isn't that going to trigger IBCQueryAfterFetch only once???

[AndreyZ]

The AfterFetch event is triggered when fetching of data is performed. When FetchAll is True, all data is fetched from the server at once, and that is why the AfterFetch event triggers only once. When FetchAll is False, data is fetched from the server in a batch according to the FetchRows number of rows. It means that the AfterFetch event will trigger every time when the subsequent row is fetched from the server.

[sel]

Thank you!

[AndreyZ]

Feel free to contact us if you have any further questions about IBDAC.

[sel]

One more idea came up following your help:

Since I have a progress form showing the number of records fetched so far, I would like to add the feature of being able to ABORT the fetch.

I implemented it easily by raising an exception in the AFTERFETCH event.

It works... 90% of the time.

But then in 10% of the cases, when I destroy the form I get "unknown ISC error 0" followed by an "invalid pointer operation".

I noticed that this only happens if I abort the fetch and then I open the TIBCQuery again and leave it to fetch all records normally.

Here is the code:

Code: Select all

procedure TfPivotBase.dbDataAfterFetch(DataSet: TCustomDADataSet);
begin
  if AbortFetch then
    begin
      AbortFetch:=False;
      raise Exception.Create(sFetchAborted);
    end;

  WaitForm.ShowAbortButton:=True;
  WaitForm.Display(Format(sProcessingData2, Dataset.RecordCount]));
end;

The AbortFetch boolean variable is set to true by my progressform...

I have also surrounded the TIBCQuery.Open with this:

Code: Select all

  dbData.Close;
  try
    dbData.Open;
  except
    dbData.Close;
    WaitForm.ShowAbortButton:=False;
    WaitForm.Hide;
    dbData.EnableControls;
    raise;
  end;

i.e. I am closing the IBCQuery on error.

What am I doing wrong?

[AndreyZ]

I cannot reproduce the problem. Please try aborting fetch operation in the BeforeFetch event handler in the following way:

Code: Select all

procedure TfPivotBase.dbDataBeforeFetch(DataSet: TCustomDADataSet;
  var Cancel: Boolean);
begin
  Cancel := AbortFetch;
end;

If it doesn't solve the problem, please specify the following:
- the exact version of your IDE;
- the exact version of Firebird. You can see it in the Info sheet of TIBCConnection Editor. Also you can try composing a small sample to demonstrate the problem and send it to andreyz*devart*com, including a script to create and fill a table.

[sel]

Hi,

Using the BeforeFetch event worked, BUT NOT AS EXPECTED.

For some reason, setting CANCEL to True does not cancel the fetch . It worked only after I raised an exception with ABORT.

Now I can really interrupt the fetches and therefore the execution of my long query without any complex treads/separate transaction etc.

Really useful for the amount of efforts! Thank you.

Still, you should check why Cancel:=True does not interrupt the fetch.

I am using the suggested (by you) idea:

db.FetchAll:=False;
db.FetchRows:=1000;
db.Open;
db.Last;

and handling BeforeFetch, setting Cancel:=True when the user presses the Abort fetch button on the form.
I am also handling Afterfetch to show the number of records fetched so far.

I am currently using CodeGear™ Delphi® 2007 for Win32® R2 Version 11.0.2804.9245, Firebird 2.1.3.18185.

[AndreyZ]

I cannot reproduce the problem. Please describe how you determined that fetching wasn't canceled.