mORMot Open Source

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Batch Insert How to Get ResultIDs on Exception

Hello!

I save data in a batch mode into a MySQL table (MyISAM without transaction support). After a data batch is saved the result IDs should be assigned to the objects. The problem is if an exception is raised during the execution of BatchSend I didn't have any IDs in the Results array. So, the saved data is inconsistent, but I can not delete the data which was saved before the exception raised because I don't have any IDs in Results.

How could I handle the situation like this? Is there a better approach to do it?

procedure TDataAccessService<T>.SaveList(const AList: IList<T>);
var
  ClientDB: TSQLRest;
  SQLQuery: TSQLRecord;
  Data: IDataAccess;
  Batch: TSQLRestBatch;
  I: Integer;
  ResCode: Integer;
  Results: TIDDynArray;
begin
  Batch := nil;
  SQLQuery := nil;
  try
    ClientDB := GetClientDB;
    Batch := TSQLRestBatch.Create(ClientDB, GetSQLRecordClass);
    SQLQuery := GetSQLRecordClass.Create;
    for Data in AList do
    begin
      Batch.Add(DataToSQLQuery(Data, SQLQuery), True);
    end;

    ResCode := ClientDB.BatchSend(Batch, Results);

    if (ResCode <> HTML_SUCCESS) then
      raise Exception.Create(StatusCodeToErrorMsg(ResCode));

    for I := Low(Results) to High(Results) do
    begin
      Data := AList.Items[i];
      Data.ID := TDBKey(Results[i]);
      UpdateBlob(Data);
    end;
  finally
    FreeAndNil(SQLQuery);
    FreeAndNil(Batch);
  end;
end;

Thank you in advance for your help!