How to use TCRBatchMove for updating records

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ertank
Posts: 172
Joined: Wed 13 Jan 2016 16:00

How to use TCRBatchMove for updating records

Post by ertank » Sat 14 Jul 2018 15:15

Hello,

I am using Delphi 10.2.3, UniDAC 7.1.3. Target is Win32 executable.

I have two datasets. They are from two different database systems with identical structure. Currently I use below code:

Code: Select all

DM.CRBatchMove1.Mode := bmUpdate;
DM.CRBatchMove1.Execute();
Log('Updated records: ' + FormatFloat('#,##0', DM.CRBatchMove1.ChangedCount));
I do not set any of the KeyFields or IndexFieldNames properties in relevant TUniQuery components. I also confirm that target query is not read-only.

I always see 0 (zero) changed records and I also check and confirm that target table records are not updated.
I do know that there is one record that has to be updated in target query because one field value is different in source.

Below is that one record data in source query. Column "MK" is a constant defined in TUniQuery.SQL and column "ID" is a Primary Key in database table definition:

Code: Select all

MK	ID	GRUP_ID	URUNADI	STOKKODU	STOK_TIPI	STKTUR	BIRIMI	KDV	ALISFIYATI	FIYATI	BARKOD	FIYAT1	FIYAT2	RESIM	DOVIZKODU	SIKSATILAN	OTOGELSIN	ACIKLAMA1	ACIKLAMA2	ACIKLAMA3	SONALISFYT	SONSATFYT	MINMIK	MAXMIK	MINAFYT	MAXAFYT	MINSFYT	BASKIADETI	MAXSFYT	YAZICIKODU	TRZGRPKOD	SIRANO	RENK_ID	OAMLYT	OSMLYT	MARKA	ACKGOSTER	OZEL_KOD1	CHNG_TIME	PASIF	STKMARKA_ID	FIYAT3	FIYAT4	KDV_ID	POSTEVENT	CREATED_AT	UPDATED_AT
1	2721	1	ÇÖP ŞİŞ	2.0062		0	ADET	18	0	4	6988055000197	4	0	TL																	3043	36						14.07.2018 01:26:19	0	0				1	31.12.1899	14.07.2018 01:26:19
Below is that one record data in target query. Columns "MK" and "ID" are two column Primary Key together in database table definition:

Code: Select all

MK	ID	GRUP_ID	URUNADI	STOKKODU	STOK_TIPI	STKTUR	BIRIMI	KDV	ALISFIYATI	FIYATI	BARKOD	FIYAT1	FIYAT2	RESIM	DOVIZKODU	SIKSATILAN	OTOGELSIN	ACIKLAMA1	ACIKLAMA2	ACIKLAMA3	SONALISFYT	SONSATFYT	MINMIK	MAXMIK	MINAFYT	MAXAFYT	MINSFYT	BASKIADETI	MAXSFYT	YAZICIKODU	TRZGRPKOD	SIRANO	RENK_ID	OAMLYT	OSMLYT	MARKA	ACKGOSTER	OZEL_KOD1	CHNG_TIME	PASIF	STKMARKA_ID	FIYAT3	FIYAT4	KDV_ID	POSTEVENT	CREATED_AT	UPDATED_AT
1	2721	2	ÇÖP ŞİŞ	2.0062		0	ADET	18	0	4	6988055000197	4	0		TL																	3043	36						2018-07-13 18:04:17.029	0	0				1	1899-12-31 00:00:00	2018-07-13 18:04:17.104
It can be seen that GRUP_ID is different from source and it is not updated in target. There are other different columns such as UPDATED_AT in source record data.

I failed to find a decent usage sample among demo projects and online documentation. It is possible that I overlooked at something. I read in online documentation and help file "bmUpdate: Replaces records in the destination dataset with the matching records from the source dataset." However, I cannot understand how that "matching records" actually works, or if I need some properties set to have it work as expected.

I am not sure if the way I am using TCRBatchMove for updating records is correct.

I appreciate any help on resolving my problem.

Thanks & regards,
Ertan

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: How to use TCRBatchMove for updating records

Post by azyk » Tue 17 Jul 2018 10:46

Please specify the source and target DBMS you are using. Also provide us with a scripts to create source and target tables.

ertank
Posts: 172
Joined: Wed 13 Jan 2016 16:00

Re: How to use TCRBatchMove for updating records

Post by ertank » Thu 19 Jul 2018 08:14

Hello,

When I was trying to prepare a demo project to reproduce the problem, I realize that TCRBatchMove source is reading data from wrong query. After fixing that mistake I see that update is applied to target data set. Sorry for the wrong alarm.

In the mean time, I observe that TCRBatchMove updates 2 records out of 2 records in total. This is always the case even if there is a single record in target that is different from source. Is this expected way of TCRBatchMove?

Thanks & regards,
Ertan

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: How to use TCRBatchMove for updating records

Post by azyk » Fri 20 Jul 2018 10:44

We tried to reproduce the specified behavior according to your recommendations, however it was not reproduced.
In the initial conditions, TCRBatchMove1.Mode=bmUpdate.
The source table:

Code: Select all

ID,GRUP_ID ,URUNADI
2721, 1, 'ÇÖP ŞİŞ'
2722, 2, 'ÇÖP ŞİŞ'
The target table:

Code: Select all

ID,GRUP_ID ,URUNADI
2721, 1, 'ÇÖP ŞİŞ'
2722, 1, 'ÇÖP ŞİŞ'
1. The target table has a primary key. The TUniQuery.KeyFields value is blank.
2. The target table does not have a primary key. TUniQuery.KeyFields='ID'.

In both cases, TCRBatchMove updated one record in a dataset and sent SQL query to the server to change one record:

Code: Select all

exec sp_executesql N'UPDATE BatchMoveDST
SET
  GRUP_ID = @P1
WHERE
  ID = @P2',N'@P1 int,@P2 int',2,2722
go
Please, compose and send us a test sample to reproduce the issue.

ertank
Posts: 172
Joined: Wed 13 Jan 2016 16:00

Re: How to use TCRBatchMove for updating records

Post by ertank » Fri 20 Jul 2018 14:02

Dear azyk,

I did not check statements executed on PostgreSQL server until I read your reply. I simply checked ChangedCount value and it was always 2.

When I look at server side executed statements I see that one column is a "bytea" data type and it is the only column that is always updated on target table by the component.

Let me know if you observe same behavior, please.

Thanks & regards,
Ertan

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: How to use TCRBatchMove for updating records

Post by azyk » Tue 24 Jul 2018 12:16

Thank you for the information. We reproduced the specified behavior and are investigating it now.

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: How to use TCRBatchMove for updating records

Post by azyk » Wed 12 Sep 2018 11:24

TCRBatchMove.Mode = bmUpdate

When calling the TCRBatchMove.Execute method, UniDAC goes to the first record of TCRBatchMove.Source. It get a key field value (in your sample, MK=1 and ID=2721).
It will search for this record in TCRBatchMove.Destination (MK=1 and ID=2721). When the record in Destination is found, UniDAC copies values of all fields (except the key ones) from Source dataset record and increases the ChangedCount value by 1...

Thus, in the described case, TCRBatchMove will always copy 2 records to Dest, despite that they are identical with Source. This behavior is correct.

Post Reply