Jump to content
Shavkat PANDA

TFDMemTable.CopyDataSet multiplicates some records

Recommended Posts

hi,

did anybody have this issue:

 

1) FDquery1 to some MSSQL table

2) view dataset of FDquery1 thru DBGrid - 3 distinct records (same result in SSMS)

3) FDMemTable1.CopyDataSet(FDquery1,[coStructure, coRestart, coAppend])

4) view dataset of FDMemTable1 thru DBGrid - 9 records (3 original records copied 3 times)

 

what could be a problem?

 

Edited by Shavkat PANDA

Share this post


Link to post

found the source of the problem, it's a LocalSQL.

 

the queries (let :Param_B = '%%')
 

select * from Table_1
where Field_0 like :Param_A

and (Field_1 like :Param_B or Field_2 like :Param_B or Field_3 like :Param_B)

 

and

 

select * from Table_1

where Field_0 like :Param_A
and (Field_1 + Field_2 + Field_3 like :Param_B)

 

return identical datasets if Table_1 is a table from MSSQL DB,

but if Table_1 is an FDMemTable - the first query triplicates dataset records

 

(in LocalSQL query '+' must be replaced by '||')

 

is this an SQLite bug?

Share this post


Link to post
16 minutes ago, emailx45 said:

if your code was showed... but try this... all same resulted on the end 15 records - no repeated

  • there are others way to copy "from" ... "to", of course!

image.thumb.png.1edde6eee96c2a6c6dbe9128f3d3d96a.png


procedure TForm1.Button1Click(Sender: TObject);
begin
  FDQuery1.Open();
  //
  Caption := 'FDQuery1.RecordCount = ' + FDQuery1.RecordCount.ToString;
  //
  begin
    FDMemTable1.Close;
    //
    FDMemTable1.FieldDefs.Assign(FDQuery1.FieldDefs);
    //
    FDMemTable1.CreateDataSet;
    //
    FDMemTable1.Open;
    //
    // see on Help about new options and its use!
    FDMemTable1.CopyDataSet(FDQuery1, [coAppend]); // [coStructure, coRestart, coAppend]);
    //
  end;
  //
  FDQuery1.Close;
  //
  ShowMessage('FDMemTable1.RecordCount = ' + FDMemTable1.RecordCount.ToString);
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  FDQuery1.Open();
  //
  Caption := 'FDQuery1.RecordCount = ' + FDQuery1.RecordCount.ToString;
  //
  begin
    FDMemTable1.Close;
    //
    FDMemTable1.Data := FDQuery1.Data; // all records, too!
  end;
  //
  FDQuery1.Close;
  //
  ShowMessage('FDMemTable1.RecordCount = ' + FDMemTable1.RecordCount.ToString);
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
  FDConnection1.Connected := true;
  //
  FDQuery1.Open();
  //
  Caption := 'FDQuery1.RecordCount = ' + FDQuery1.RecordCount.ToString;
  //
  begin
    FDMemTable1.Close;
    // see more on Help about options and use!
    FDMemTable1.CloneCursor(FDQuery1); // After calling CloneCursor, the internal data storage is physically the same for this dataset and for the ASource dataset.
  end;
  //
  FDQuery1.Close;
  //
  ShowMessage('FDMemTable1.RecordCount = ' + FDMemTable1.RecordCount.ToString);
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  FDConnection1.Connected := false;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  FDConnection1.Connected := true;
end;

 

 

hug

sorry, but your both posts are irrelevant

Share this post


Link to post
5 minutes ago, Shavkat PANDA said:

sorry, but your both posts are irrelevant

sorry! if both are irrelevant! just edit your post above and delete my fault!

Share this post


Link to post

I cannot reproduce the issue using 10.4.1. Could you please post:

1) Your RAD Studio version
2) Simple test project reproducing the issue

Edited by Dmitry Arefiev

Share this post


Link to post
38 minutes ago, Dmitry Arefiev said:

I cannot reproduce the issue using 10.4.1. Could you please post:

1) Your RAD Studio version
2) Simple test project reproducing the issue

1) 10.4.1
2) the problem is not in the FDMemTable.CopyDataSet method

after copying I use LocalSQL to query the memory table

two queries shown below produce different datasets in LocalSQL:


a) this query returns the correct set

select * from ProductsInMemory
where DivCod in (:Stok, :Shop, :Prim)
and LocCod like :SearchCod
and (:SearchSeasons='' or instr(' ' || trim(:SearchSeasons) || ' ',' ' || trim(Season) || ' ')>0 )
and BrandName like :SearchBrand
and Article like :SearchArticle
and (:SearchSizes='' or instr(' ' || :SearchSizes || ' ',' ' || trim(Size) || ' ')>0)
and Description like :SearchDescr
and Color like :SearchColor
and Disc1 like :SearchDiscPerc
and (:Check_M=1 and (MorF in (1,3)) or :Check_F=1 and (MorF in (2,3)) or :Check_B=1 and MorF=4 or :Check_G=1 and MorF=5 or :Check_D=1 and MorF>5)
and (:SearchPriceFrom=0 and :SearchPriceTo=0 or :SearchPriceFrom<=SomPrice and SomPrice<=:SearchPriceTo or :SearchPriceFrom<=SalesPrice and SalesPrice<=:SearchPriceTo)
and BarCode like :SearchBarCode
and (OriginalArticle || OriginalArticle2 || OriginalArticle3 like :SearchOriginalArticle)


b) this one returns multiplied records:

select * from ProductsInMemory
where DivCod in (:Stok, :Shop, :Prim)
and LocCod like :SearchCod
and (:SearchSeasons='' or instr(' ' || trim(:SearchSeasons) || ' ',' ' || trim(Season) || ' ')>0 )
and BrandName like :SearchBrand
and Article like :SearchArticle
and (:SearchSizes='' or instr(' ' || :SearchSizes || ' ',' ' || trim(Size) || ' ')>0)
and Description like :SearchDescr
and Color like :SearchColor
and Disc1 like :SearchDiscPerc
and (:Check_M=1 and (MorF in (1,3)) or :Check_F=1 and (MorF in (2,3)) or :Check_B=1 and MorF=4 or :Check_G=1 and MorF=5 or :Check_D=1 and MorF>5)
and (:SearchPriceFrom=0 and :SearchPriceTo=0 or :SearchPriceFrom<=SomPrice and SomPrice<=:SearchPriceTo or :SearchPriceFrom<=SalesPrice and SalesPrice<=:SearchPriceTo)
and BarCode like :SearchBarCode
and (OriginalArticle like :SearchOriginalArticle or OriginalArticle2 like :SearchOriginalArticle or OriginalArticle3 like :SearchOriginalArticle)

the difference between the two is in the last line:
<and (OriginalArticle || OriginalArticle2 || OriginalArticle3 like :SearchOriginalArticle)>

vs.
<and (OriginalArticle like :SearchOriginalArticle or OriginalArticle2 like :SearchOriginalArticle or OriginalArticle3 like :SearchOriginalArticle)>

 


both queries return identical sets if I SELECT FROM the original MSSQL table (for MSSQL we replace '||' by '+' of course)

Share this post


Link to post

Ok, I reproduced your issue after modifing your failing query in second post to:

select * from FDMemTable1
where Field_1 like :Param_B or Field_2 like :Param_B or Field_3 like :Param_B

 

Share this post


Link to post
19 hours ago, Dmitry Arefiev said:

Ok, I reproduced your issue after modifing your failing query in second post to:


select * from FDMemTable1
where Field_1 like :Param_B or Field_2 like :Param_B or Field_3 like :Param_B

 

good. should I add the issue into the quality portal?

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×