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;

`Result := False;` after the `raise` is useless. I would raise an `EArgumentNilException` if `Example` is not assigned and turn it from a function into a procedure — Sir Rufo, Oct 03 '14 at 16:13
http://stackoverflow.com/questions/8548843/why-should-i-not-use-if-assigned-before-using-or-freeing-things — David Heffernan, Oct 03 '14 at 16:36

Ken White · Accepted Answer · 2014-10-03T19:14:10.847

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.

Thank you @KenWhite. At the moment, I can not change the function, to be a procedure, because others objects are expecting the True result of the function to continue their execution. So, I'm also starting the Result := False, at the begin of the function. You can check the final code here: https://gist.github.com/anonymous/c1ce27723cf793828c66 — rodrigopandini, Oct 06 '14 at 13:52

How to properly start, working and finish a transaction?

1 Answers1