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
Guest

OK! sorry!

Edited by Guest

Share this post


Link to post
Guest

OK! sorry!

Edited by Guest

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
Guest
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

×