How to apply cached FDQuery update using Delphi FireDAC with UNIQUE constraint in database

Question

I am having a problem resolving cache updates when delta includes fields that have a UNIQUE constraint in the database. I have a database with the following DDL schema (SQLite in memory can be used for playback):

create table FOO
(
  ID integer primary key,
  DESC char(2) UNIQUE
);

The source database table contains one record with identifier = 1 and DESC = R1

Recognizing this TFDQuery table (select * from FOO), if the following steps are followed, the generated delta will be correctly applied using ApplyUpdates:

• Update Record ID = 1 to DESC = R2
• Add new record ID = 2 with DESC = R1

Delta includes the following:

• R2
• R1

ApplyUpdates , delta . . 1 R2, , .

, , ​​ ( FDQuery.Delta), UNIQUE.

• ID = 2 DESC = TT
• = 1 DESC = R2
• 2 - TT DESC = R1

Delta :

• R2
• R1

, FireDAC , FDQUery Delta.

:

> VCL Forms; FDConnection FDQuery ; FDConnection SQLite ( ); , , - :

:

procedure TFrmMain.btnOkClick(Sender: TObject);
begin
  // create the default database with a FOO table
  con.Open();
  con.ExecSQL('create table FOO' + '(ID integer primary key, DESC char(2) UNIQUE)');
  // insert a default record
  con.ExecSQL('insert into FOO values (1,''R1'')');
  qry.CachedUpdates := true;
  qry.Open('select * from FOO');
  // update the first record to T2
  qry.First();
  qry.Edit();
  qry.Fields[1].AsString := 'R2';
  qry.Post();
  // append the second record to T1
  qry.Append();
  qry.Fields[0].AsInteger := 2;
  qry.Fields[1].AsString := 'R1';
  qry.Post();
  // apply will not generate a unique constraint violation
  qry.ApplyUpdates();
end;

:

  // create the default database with a FOO table
  con.Open();
  con.ExecSQL('create table FOO' + '(ID integer primary key, DESC char(2) UNIQUE)');
   // insert a default record
  con.ExecSQL('insert into FOO values (1,''R1'')');
  qry.CachedUpdates := true;
  qry.Open('select * from FOO');
  // append a temporary record (TT)
  qry.Append();
  qry.Fields[0].AsInteger := 2;
  qry.Fields[1].AsString := 'TT';
  qry.Post();
  // update R1 to R2
  qry.First();
  qry.Edit();
  qry.Fields[1].AsString := 'R2';
  qry.Post();
  qry.Next();
  // update TT to R1
  qry.Edit();
  qry.Fields[1].AsString := 'R1';
  qry.Post();
  // apply will generate a unique contraint violation
  qry.ApplyUpdates();

Answer 1

. , ApplyUpdates .. FireDAC Sqlite ( , ), FD . FireDAC ( ), , .

ApplyUpdates , , , qry Fields.

, ( ) Foo,

(1, 'R1')

Delphi, Foo ( Sqlite Manager FireFox). , , ApplyUpdates.

  Con.Open();
  Con.StartTransaction;
  qry.Open('select * from FOO');
  qry.InsertRecord([2, 'TT']);
  assert(qry.Locate('ID', 1, []));
  qry.Edit;
  qry.FieldByName('DESC').AsString := 'R2';
  qry.Post;
  assert(qry.Locate('ID', 2, []));
  qry.Edit;
  qry.FieldByName('DESC').AsString := 'R1';
  qry.Post;
  Con.Commit;
  qry.Close;
  Con.Close;

(ID = 2) , Con.Close , . Con.Close , Foo

(1, 'R2')
(2, 'R1')

, ApplyUpdates, , , ApplyUpdates Post.

, , ApplyUpdates , .

FireDAC. , FD qs SO, . , EMBA FireDAC NG, https://forums.embarcadero.com/forum.jspa?forumID=502.