TFDMemTable.CopyDataSet multiplicates some records

[Shavkat PANDA 0]

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 December 26, 2020 by Shavkat PANDA

[Shavkat PANDA 0]

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?

[Guest]

OK! sorry!

Edited December 26, 2020 by Guest

[Guest]

OK! sorry!

Edited December 26, 2020 by Guest

[Shavkat PANDA 0]

your both posts are irrelevant

[Shavkat PANDA 0]

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!

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

[Shavkat PANDA 0]

6 minutes ago, emailx45 said:

to "strings" normally, use "||":  'a' || 'b' ==== 'ab'

'+' is used to aggregate others types, as sum to integer values!

https://stackoverflow.com/questions/23372550/what-does-sql-select-symbol-mean

gosh, did you understand what I wrote?

[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!

[Dmitry Arefiev 101]

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 December 27, 2020 by Dmitry Arefiev

[Shavkat PANDA 0]

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)

[Dmitry Arefiev 101]

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

 

[Shavkat PANDA 0]

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?

[Dmitry Arefiev 101]

Yes, please.

[Dmitry Arefiev 101]

Fixed. Changes will be in 10.4 Update 2.

[Shavkat PANDA 0]

15 minutes ago, Dmitry Arefiev said:

Fixed. Changes will be in 10.4 Update 2.

cool