How to properly start, working and finish a transaction?

Question

I'm using MySQL, and I know that Nested Connection are not allowed - use "save points" for this - but I would like create a more generic code that could also be used with other DBMS.

So, I would like know how to properly start, working and finish a transaction in the code below?

Once ExampleDAO.Save() function could be used inside other function, like OtherExampleDAO.Save(), I need verify a transaction has been started before I try start a new one.

The lines with the verification if Assigned(dbTransaction) then always returns true, so how to properly verify if dbTransaction was instantiated?

function TExampleDAO.Save(const Example: TExample): Boolean;
var
  dbxTransaction: TDBXTransaction;
begin
  if Assigned(Example) then // prevents invalid object, like ExampleDAO.Save(nil);
  begin
    try
      if (_connection.TransactionsSupported) AND
        ((not _connection.InTransaction) OR (_connection.MultipleTransactionsSupported)) then
      begin
        dbxTransaction := _connection.BeginTransaction(TDBXIsolations.ReadCommitted);
      end;

      try
        // example
        _sqlQuery.Close;
        _sqlQuery.SQL.Clear;
        _sqlQuery.SQL.Add('INSERT INTO example(a, b) '
                        + 'VALUES(:a, :b)');
        _sqlQuery.ParamByName('a').AsAnsiString := Example.A;
        _sqlQuery.ParamByName('b').AsDateTime := Example.B;
        _sqlQuery.ExecSQL(False);

        // example info
        _sqlQuery.Close;
        _sqlQuery.SQL.Clear;
        _sqlQuery.SQL.Add('INSERT INTO example_info(c, d) '
                        + 'VALUES(:c, :d)');
        _sqlQuery.ParamByName('c').AsInteger := Example.Info.C;
        _sqlQuery.ParamByName('d').AsFloat := Example.Info.D;
        _sqlQuery.ExecSQL(False);

        if Assigned(dbxTransaction) then
          _connection.CommitFreeAndNil(dbxTransaction);

        Result := True;
      except
        on Exc:Exception do
        begin
          if Assigned(dbxTransaction) then
            _connection.RollBackFreeAndNil(dbxTransaction);

          raise Exc;
          Result := False;
        end;
      end;
    finally
      if Assigned(dbxTransaction) then
        FreeAndNil(dbxTransaction);
    end;    
  end
  else
  begin
    Result := False;
  end;
end;

Answer 1

You need to properly initialize dbxTransaction to nil at the start of your function. Local variables in Delphi (on the Win32 platform, at least) are not initialized until a value is assigned to them, meaning that the content is unknown. Passing any value other than nil to Assigned will result in True. I recommend never testing a local variable's content on any platform until it has had a value assigned in your code.

Here's an example of how to make it work. (I've also removed the unnecessary assignment to Result in the exception block.)

function TExampleDAO.Salve(const Example: TExample): Boolean;
var
  dbxTransaction: TDBXTransaction;
begin
  dbxTransaction := nil;            // Initialize the transaction variable here

  if Assigned(Example) then // prevents invalid object, like ExampleDAO.Save(nil);
  begin
    try
      if (_connection.TransactionsSupported) AND
        ((not _connection.InTransaction) OR (_connection.MultipleTransactionsSupported)) then
      begin
        dbxTransaction := _connection.BeginTransaction(TDBXIsolations.ReadCommitted);
      end;

      try
        // example
        _sqlQuery.Close;
        _sqlQuery.SQL.Clear;
        _sqlQuery.SQL.Add('INSERT INTO example(a, b) '
                        + 'VALUES(:a, :b)');
        _sqlQuery.ParamByName('a').AsAnsiString := Example.A;
        _sqlQuery.ParamByName('b').AsDateTime := Example.B;
        _sqlQuery.ExecSQL(False);

        // example info
        _sqlQuery.Close;
        _sqlQuery.SQL.Clear;
        _sqlQuery.SQL.Add('INSERT INTO example_info(c, d) '
                        + 'VALUES(:c, :d)');
        _sqlQuery.ParamByName('c').AsInteger := Example.Info.C;
        _sqlQuery.ParamByName('d').AsFloat := Example.Info.D;
        _sqlQuery.ExecSQL(False);

        if Assigned(dbxTransaction) then
          _connection.CommitFreeAndNil(dbxTransaction);

        Result := True;
      except
        on Exc:Exception do
        begin
          if Assigned(dbxTransaction) then
            _connection.RollBackFreeAndNil(dbxTransaction);

          raise Exc;
        end;
      end;
    finally
      if Assigned(dbxTransaction) then
        FreeAndNil(dbxTransaction);
    end;    
  end
  else
  begin
    Result := False;
  end;
end;

As was noted by @SirRufo in the comments to your question, failing to pass Example as a parameter should probably raise an exception as well, which would mean that it could become a procedure instead of a function and Result would no longer apply at all.