Как сохранить таблицы с отношениями master-detail в пределах одной транзакции?

Question

Я пытаюсь сохранить две таблицы с отношениями master-detail в MySQL 5.6, используя Delphi XE3 и Zeos 7.0.4. Когда я делаю ApplyUpdates на ведущем устройстве, поле автоинкремента остается со значением 0 в качестве значения. Мне нужно значение автоинкремента, чтобы я мог связать детальную таблицу с полем идентификатора основной таблицы, полученным из ApplyUpdates. Я использую ZConnection с AutoCommit = FALSE и TransactionIsolationLevel = tiReadCommitted, ZQuery с CachedUpdates = TRUE. Что мне не хватает?

ZQPerson.Append;
ZQEmployee.Append;
try
  ZQPersonName.Value := Edit1.Text;
  ZQPerson.ApplyUpdates; //Here I expected to have the auto increment value on the Id field of ZQPerson, but it returns always 0
  ZQEmployeePersonID.Value := ZQPersonId.Value; //Here I'd link Employee to it's Person record
  ZQEmployeeRegNo.Value := StrToInt(Edit2.Text);
  ZQEmployee.ApplyUpdates;
  ZConnection1.Commit; //Here I would persist both tables in a single transaction to avoid master table without details
except
  ZQPerson.CancelUpdates;
  ZQEmployee.CancelUpdates;
  ZConnection1.Rollback; //In case of exceptions rollback everything
  raise;
end;
ZQPerson.CommitUpdates;
ZQEmployee.CommitUpdates;

Мой след ZSQLMonitor это:

2013-08-29 00:01:23 cat: Execute, proto: mysql-5, msg: INSERT INTO person (Id, name) VALUES (NULL, 'Edit1') --> This is just after ZQPerson.ApplyUpdates
2013-08-29 00:01:50 cat: Execute, proto: mysql-5, msg: INSERT INTO employee (Id, RegNo, ProductId) VALUES (NULL, 1000, 0), errcode: 1452, error: Cannot add or update a child row: a foreign key constraint fails (`test`.`employee`, CONSTRAINT `FK_A6085E0491BDF8EE` FOREIGN KEY (`PersonId`) REFERENCES `person` (`Id`) --> This is just after ZQEmployee.ApplyUpdates
2013-08-29 00:02:05 cat: Execute, proto: mysql-5, msg: Native Rollback call --> Rollback after Exception on the ZQEmployee.ApplyUpdates

Answer 1

Обходной путь, который я нашел, был этим. Это не удовлетворяет меня полностью, потому что это не делает прозрачным использование функции автоматического увеличения базы данных, заставляя меня использовать функцию Last_Insert_ID(). Я в контакте с Zeos Develpers, чтобы проверить это.

function LastInsertID(ATableName: string): Integer;
var DBQuery: TZQuery;
begin
  DBQuery := TZQuery.Create(Self);
  with DBQuery do
  begin
    Connection := ZConnection1;
    SQL.Clear;
    SQL.Add('Select Last_Insert_ID() as Last_Insert_ID from ' + ATableName);
    Open;
    Result := FieldByName('Last_Insert_ID').Value;
    Free;
  end;
end;

procedure Persist;
var LastID: Integer;
begin
  ZQPerson.Append;
  ZQEmployee.Append;
  try
    ZQPersonName.Value := Edit1.Text;
    ZQPerson.ApplyUpdates; // Here I expected to have the auto increment value on the Id field of ZQPerson, but it returns always 0
    LastID := LastInsertID('Person'); //Getting the Last_Insert_ID(), even on the uncommitted transction, works
    ZQEmployeePersonId.Value := LastID; //Link the two tables using the Last_Insert_ID() result
    ZQEmployeeRegNo.Value := StrToInt(Edit2.Text);
    ZQEmployee.ApplyUpdates;
    ZConnection1.Commit; // Here I persist both tables in a single transaction to avoid master table without details
  except
    ZQPerson.CancelUpdates;
    ZQEmployee.CancelUpdates;
    ZConnection1.Rollback; // In case of exceptions rollback everything
    raise;
  end;
  ZQPerson.CommitUpdates;
  ZQEmployee.CommitUpdates;

Answer 2

Вы начинаете транзакцию с ZConnection1.StartTransaction? Я также думаю, что вы должны обновить ZQuery1 после вызова ZQuery1.ApplyUpdates, чтобы получить новый идентификатор

Читая ваш комментарий, вы должны делать select * без предложения where? право? Я могу рекомендовать вам использовать этот подход:

1) выбрать и увеличить текущее значение автоинкремента
2) выбрать из основной таблицы, где id=[step1 id] // он будет пуст, конечно
3) добавить детали, используя идентификатор в шаге 1
4) назначить идентификатор в основной набор данных
5) применить оба обновления

Answer 3

Я протестировал его в простой базе данных с двумя основными и подробными таблицами, вложенными в TDataSource и относящимися к where таблицы деталей:

object conMysql: TZConnection
     TransactIsolationLevel = tiReadCommitted
object zqryMaster: TZQuery
     Connection = conMysql
SQL.Strings = (
       'select * from temp.master')
object dsNestedMaster: TDataSource
     DataSet = zqryMaster
object zqryDetail: TZQuery
     Connection = conMysql
     SQL.Strings = (
       'select * from temp.detail'
       'where id_master =: id')

После запуска транзакции все обновления должны ждать подтверждения или отката в случае возникновения ошибки:

  try
    zqryMaster.Connection.StartTransaction;
    zqryMaster.Edit;
    zqryDetail.Edit;
    zqryMaster.FindField('dt_mov').Value := Now;
    while not zqryDetail.Eof do
    begin
      zqryDetail.Edit;
      zqryDetail.FindField('dt_mov').Value := Now;
      zqryDetail.ApplyUpdates;
      zqryDetail.Next;
      //raise Exception.Create('simple error'); //use for tests, check database after perform
    end;
    zqryMaster.ApplyUpdates;
    zqryMaster.Connection.Commit;
  except
    zqryMaster.Connection.Rollback;
    zqryMaster.CancelUpdates;
    zqryDetail.CancelUpdates;
  end;