Hi
Using Delphi 12.2 - RODA .1611
Master-Detail situation. Insert a master-record, inserting a detail record.
The detail table adds extra record on serverside through DADelta (BeforeProcessDelta - BusinessProcessorRules).
When I trace the database, all SQL statements execute correctly. The transaction is committed.
On client side a Reconcile Error appears.
SQL statements show the insert into Chemicals (with Identity column), an insert into Incompatibilities with the new ID of Chemicals in column ChemicalID and an insert into Incompatibilities with ChemicalID and IncompatibilityID the other way around.
declare @p1 int
set @p1=14
exec sp_prepare @p1 output,N'@P1 varchar(8000),@P2 text,@P3 text,@P4 varchar(8000),@P5 datetime,@P6 varchar(8000),@P7 datetime,@P8 varchar(8000),@P9 varchar(8000),@P10 int,@P11 int,@P12 int,@P13 bit,@P14 bit,@P15 bit,@P16 float,@P17 int,@P18 int,@P19 bit,@P20 bit,@P21 bit,@P22 bit,@P23 bit,@P24 float,@P25 float,@P26 varchar(8000)',N'INSERT INTO dbo.Chemicals (
[CNK], [NotesDutch], [NotesFrench], [CreatedUser], [CreatedDate], [LastModifiedUser], [LastModifiedDate], [NameDutch], [NameFrench], [OverdosePercent], [Danger], [MaxDeviation], [Cooled], [Narcotic], [PoisonCabinet], [DisplacementRatio], [Precaution], [DefaultLocation], [Anesthetic], [AutoUpdate], [SemiFinishedProduct], [Poisonous], [Overdose], [MaxDose], [PoisonDose], [ExternalNumber])
VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15, @P16, @P17, @P18, @P19, @P20, @P21, @P22, @P23, @P24, @P25, @P26)',1
select @p1
go
exec sp_execute 14,'456',NULL,NULL,'1','2025-05-08 08:41:21.710','1','2025-05-08 08:41:21.710','Nog eentje','',0,0,NULL,0,0,0,0,0,NULL,0,NULL,0,0,0,0,0,''
go
declare @p1 int
set @p1=15
exec sp_prepexec @p1 output,NULL,N'SELECT IsNull(@@Identity, 0) as LastInc'
select @p1
go
exec sp_unprepare 15
go
declare @p1 int
set @p1=16
exec sp_prepare @p1 output,N'@P1 int',N'SELECT
[ID]
FROM dbo.Chemicals
WHERE
[ID] = @P1
',1
select @p1
go
exec sp_execute 16,56985
go
exec sp_unprepare 16
go
exec sp_unprepare 14
go
declare @p1 int
set @p1=17
exec sp_prepare @p1 output,N'@P1 int,@P2 int',N'INSERT INTO dbo.Incompatibilities (
[ChemicalID], [IncompatibilityID])
VALUES (@P1, @P2)',1
select @p1
go
exec sp_execute 17,56985,131
go
declare @p1 int
set @p1=18
exec sp_prepexec @p1 output,NULL,N'SELECT IsNull(@@Identity, 0) as LastInc'
select @p1
go
exec sp_unprepare 18
go
declare @p1 int
set @p1=19
exec sp_prepare @p1 output,N'@P1 int',N'SELECT
[ID]
FROM dbo.Incompatibilities
WHERE
[ID] = @P1
',1
select @p1
go
exec sp_execute 19,1109
go
exec sp_execute 17,131,56985
go
declare @p1 int
set @p1=20
exec sp_prepexec @p1 output,NULL,N'SELECT IsNull(@@Identity, 0) as LastInc'
select @p1
go
exec sp_unprepare 20
go
exec sp_execute 19,1110
go
exec sp_unprepare 19
go
exec sp_unprepare 17
go
Records are added as they should be. But a reconcile error appears on client side.
Clicking Show Details
The BeforeProcessDelta
procedure TCQIncompatibilitiesServerRules.BeforeProcessDelta(
Sender: TDABusinessProcessor; const aDelta: IDADelta);
var
aChange: TDADeltaChange;
lInserts: TList<Integer>;
lUpdates: TList<Integer>;
lDeletes: TList<Integer>;
iCnt: Integer;
iRec: Integer;
iID: Integer;
begin
iRec := 0;
lInserts := TList<Integer>.Create;
lDeletes := TList<Integer>.Create;
lUpdates := TList<Integer>.Create;
try
for iCnt := 0 to aDelta.Count - 1 do
begin
iRec := aDelta.Changes[iCnt].RecID;
if aDelta.Changes[iCnt].ChangeType = ctInsert then
lInserts.Add(iCnt);
if aDelta.Changes[iCnt].ChangeType = ctUpdate then
lUpdates.Add(iCnt);
if aDelta.Changes[iCnt].ChangeType = ctDelete then
lDeletes.Add(iCnt);
end;
//iRec must be unique
for iCnt := 0 to lInserts.Count - 1 do
begin
Inc(iRec);
aChange := aDelta.Add(iRec, ctInsert);
aChange.NewValueByName['ID'] := aDelta.Changes[lInserts[iCnt]].NewValueByName['ID'];
aChange.NewValueByName['ChemicalID'] := aDelta.Changes[lInserts[iCnt]].NewValueByName['IncompatibilityID'];
aChange.NewValueByName['IncompatibilityID'] := aDelta.Changes[lInserts[iCnt]].NewValueByName['ChemicalID'];
end;
for iCnt := 0 to lDeletes.Count - 1 do
begin
Inc(iRec);
aChange := aDelta.Add(iRec, ctDelete);
aChange.OldValueByName['ID'] := DoGetID(aDelta.Changes[lDeletes[iCnt]].OldValueByName['IncompatibilityID'], aDelta.Changes[lDeletes[iCnt]].OldValueByName['ChemicalID']);
aChange.OldValueByName['ChemicalID'] := aDelta.Changes[lDeletes[iCnt]].OldValueByName['IncompatibilityID'];
aChange.OldValueByName['IncompatibilityID'] := aDelta.Changes[lDeletes[iCnt]].OldValueByName['ChemicalID'];
end;
for iCnt := 0 to lUpdates.Count - 1 do
begin
Inc(iRec);
aChange := aDelta.Add(iRec, ctUpdate);
iID := DoGetID(aDelta.Changes[lUpdates[iCnt]].OldValueByName['IncompatibilityID'], aDelta.Changes[lUpdates[iCnt]].NewValueByName['ChemicalID']);
aChange.NewValueByName['ID'] := iID;
aChange.NewValueByName['ChemicalID'] := aDelta.Changes[lUpdates[iCnt]].NewValueByName['IncompatibilityID'];
aChange.NewValueByName['IncompatibilityID'] := aDelta.Changes[lUpdates[iCnt]].NewValueByName['ChemicalID'];
aChange.OldValueByName['ID'] := iID;
aChange.OldValueByName['ChemicalID'] := aDelta.Changes[lUpdates[iCnt]].OldValueByName['ChemicalID'];
aChange.OldValueByName['IncompatibilityID'] := aDelta.Changes[lUpdates[iCnt]].OldValueByName['IncompatibilityID'];
end;
finally
lInserts.Free;
lUpdates.Free;
lDeletes.Free;
end;
end;
The schema has the appropriate Update Rules and Relations.
Please advise.