I get wrong recordcount after TBufDataSet or TSQLQuery  is filtered,
what should
lazarus / 16-Databases

----------------------------------------------------------------------
ipws · 2017-05-09 07:26

I get wrong recordcount after TBufDataSet or TSQLQuery  is filtered,
what should I do?
so write a function as follow:
function getRecordCount(cds:TDataSet):integer;
  var
    myfield:TField;
    sFName,sVal,sE:String;
begin
  try
    result := 0;
    if cds = nil then
     result := -1;
    if cds.Active = false then
      result := -2;
    if result < 0 then
      exit;
    result := cds.RecordCount;
    if result = 0 then
      exit;

    myfield := nil;
    sVal := '';
    with cds do
    begin
      sFName := 'M001';
      myfield := Findfield(sFName);
      if myfield <> nil then
      begin
        sVal := FieldByName(sFName).AsString;
      end;
      myfield := nil;
      if sVal = '' then
      begin
        sFName := 'D001';
        myfield := Findfield(sFName);
        if myfield <> nil then
        begin
          sVal := FieldByName(sFName).AsString;
        end;
      end;
      if sVal = '' then
      begin
        sFName := 'FBRNO';
        myfield := Findfield(sFName);
        if myfield <> nil then
        begin
          sVal := FieldByName(sFName).AsString;
        end;
      end;
      if sVal <> '' then
        result := 1
      else
      begin
        result := 0;
        exit;
      end;

      Next;
      if eof then
        result := 1
      else
      begin
       result := 2;
       Prior;
      end;
    end;
  except
    on E:Exception do
    begin
      sE := E.Message;
      try
       
SaveTextToDisk(DefaultPath+'log\'+formatdatetime('yymmddhhmmss',
          now) + '_getRecordCount.txt','; error:'+sE);
      except

      end;
    end;
  end;
end;

----------------------------------------------------------------------
Thaddy · 2017-05-09 08:03

What do you mean? The record count of the result set or the actual
record count? Because I don't see anything wrong that warrants an
extra piece of code....?

----------------------------------------------------------------------
Handoko · 2017-05-09 09:40

Hi ipws.

I followed your code's flow and found that this code below will be
processed at the end if no prior exit executed:

Quoteif eof then
  result := 1
else
begin
  result := 2;
  Prior;
end;

It means your getRecordCount function will always give you result = 1
or 2 if nothing interrupted in the middle. Are you sure it is what you
want?

----------------------------------------------------------------------
ipws · 2017-05-10 00:11

thanks all, i mean that filtered TBufDataSet.RecordCount is not
right,so i write a function to replace it and get one record or
not,but anyway filtered BufDataSet's record count is still no
change,the recordcount always equal total record count. TSQLQuery is
as same as bufdataset. is it a bug?

----------------------------------------------------------------------
Handoko · 2017-05-10 02:07

Bugs can be hidden anywhere, we need to inspect your code. So can you
please show us your whole code?

I understand, for some reasons you may not willing to publicize your
project. It's okay, you can write a simple compile-able and runnable
project that showing the bug, compress all the related files and
attach the zip file to this forum.

----------------------------------------------------------------------
Handoko · 2017-05-10 02:20

I just found an old discussion about TBufDataset filtering issue you
may interested to read:
http://forum.lazarus.freepascal.org/index.php?topic=16518.0

You can try to update your Lazarus/FPC to the latest version. It the
problem still exists, it can be a missing feature or bug. If you think
it is a bug, please report it to:
https://bugs.freepascal.org

----------------------------------------------------------------------
ipws · 2017-05-10 03:58

thank you,Handoko, you're very nice and warmth,
gived Attach is a example that is  about getting wrong recordcount
simple project,and lazarus's versiont is 1.64.

----------------------------------------------------------------------
Handoko · 2017-05-10 05:53

I tested your code and I can reproduce your issue on Lazarus 1.6.4 FPC
3.0.2 Linux 64-bit.

I read the TDbf documentation downloaded from:
https://sourceforge.net/projects/tdbf/files/TDbf%20documentation/TDbf%
20documentation%201.1/tdbf.pdf/download?use_mirror=ncu

Here is what it said:Quote7.7 ExactRecordCount

property ExactRecordCount : Integer read GetExactRecordCount ;

Examine ExactRecordCount to determine the exact number of records in
the current dataset.
This takes into account deleted, filtered and indexed records. This in
contrary to Record-
Count, which will always give a rough upper bound estimate. Note that
this property needs
to scan the complete dataset to find the number of records that are
active, while RecordCount
is just a simple calculation.
I checked TBufDataset and I found no ExactRecordCount function. By
examining how TDbf.ExactRecordCount works, then I wrote the code to
imitate TDbf.ExactRecordCount.

Suggestion for solving you problem:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i //...type  
{ TForm1 }   TForm1 = class(TForm)//...    function
GetExactRecordCount: Integer; //... function
TForm1.GetExactRecordCount: Integer;var  prevRecNo: Integer;begin 
Result := 0;  with BufDataset1 do  begin    prevRecNo := RecNo;   
First;    while not(EOF) do     begin      Inc(Result);      Next;   
end;    RecNo := prevRecNo;  end;end; procedure
TForm1.Button1Click(Sender: TObject);begin  if trim(Edit1.Text) = ''
then  begin    Showmessage('Please input filter value');    exit; 
end;  with BufDataset1 do  begin    Filtered := false;    Filter :=
format('M002 = ''*%s*''',[trim(Edit1.Text)]);    Filtered := true;   
Showmessage('Record count is ' + IntToStr(BufDataset1.RecordCount)); 
 Showmessage('Exact record count is ' +
IntToStr(GetExactRecordCount));  end;end;//... 
Please add TForm1.GetExactRecordCount function into your code, also
you have to add it on the declaration area (line code #8).

This issue is not a bug in your code. I consider it is a non-
implemented feature in TBufDataset.