How to handle transactions properly with Zeos?

[Kirill]

TZTransaction added on 7.3-testing.

Thanks for adding TZTransaction component. Can you give an example of the proper use of this component?

[EgonHugeist]

@Krill,
still not had time to write totorials. IMHO it should work like the FPC/FireDac/Unidac transaction components.
Did you test it?

Side notes: If you're using the component, avoid the Conenction [StartTransaction/Commit/Rollback] and use the TZTransaction component instead.
Was this helpfull?

[Kirill]

Hi. I use Lazarus 2.0.10 with FPC 3.2.0. Zeos version is r7078-trunk. DBMS is Firebird 3.0. There are three components on the form: ZConnection1, ZQuery1, ZTransaction1. ZTransaction1.Connection is ZConnection1. ZQuery1.Connection is ZConnection1. ZQuery1.Transaction is ZTransaction1. The source code is given below.

Code: Select all

procedure TForm1.ButtonTransactionClick(Sender: TObject);
var
  i: integer;
begin
  ZConnection1.AutoCommit := False;
  ZTransaction1.AutoCommit := False;

  ZQuery1.SQL.Text := 'insert into TestTable(A) values (1)';
  ZConnection1.Connected := True;

  ZTransaction1.StartTransaction();
  for i := 1 to 10 do
  begin
    ZQuery1.ExecSQL();
  end;

  //  Invalid operation in AutoCommit mode
  if RadioGroup1.ItemIndex = 0 then
  begin
    ZTransaction1.Commit();
  end
  else
  begin
    ZTransaction1.Rollback();
  end;

  ZConnection1.Connected := False;
end;

There is an error «Invalid operation in AutoCommit mode» for both commit and rollback. What am I doing wrong?

[EgonHugeist]

Hello Krill,

i fixed a bug: https://sourceforge.net/p/zeoslib/code-0/7088/

and added a TestCase:

Code: Select all

procedure TZGenericecTransactionTestCase.TestQueryTransaction;
var Query: TZQuery;
    Transaction: TZTransaction;
    Succeeded: Boolean;
begin
  Query := CreateQuery;
  Check(Query <> nil);
  Transaction := CreateTransaction;
  try
    Check(Transaction.AutoCommit, 'the txn should be in autocommit mode');
    Connection.Connect;
    Transaction.TransactIsolationLevel := Connection.DbcConnection.GetMetadata.GetDatabaseInfo.GetDefaultTransactionIsolation;
    Query.Transaction := Transaction;
    //this test should pass if the txn is in autocommit mode
    Query.SQL.Text := 'DELETE FROM equipment where eq_id = ' + SysUtils.IntToStr(TEST_ROW_ID);
    Query.ExecSQL;
    Transaction.StartTransaction;
    Succeeded := False;
    with Query do try
      CheckFalse(Transaction.AutoCommit);
      { Create prepared statement for equipment table }
      Sql.Text := 'INSERT INTO equipment (eq_id, eq_name, eq_type, eq_cost, eq_date, '
          + ' woff_date) VALUES(:q_id, :eq_name, :eq_type, :eq_cost, :eq_date, :woff_date)';
      CheckEquals(6, Params.Count);

      Params[5].DataType := ftDate;

      Params[0].AsInteger := TEST_ROW_ID;
      Params[1].AsString := '\xyz\'+#13;
      Params[2].AsInteger := 7;
      Params[3].AsFloat := 1234.567;
      Params[4].AsDateTime := EncodeDate(1999, 8, 5);
      Params[5].Value := Null;
      ExecSQL;

      CheckEquals(1, RowsAffected);
      Succeeded := True;
    finally
      if Succeeded
      then Transaction.Commit
      else Transaction.Rollback;
    end;
  finally
    FreeAndNil(Transaction);
    FreeAndNil(Query);
    Connection.ExecuteDirect('DELETE FROM equipment where eq_id = ' + SysUtils.IntToStr(TEST_ROW_ID));
  end;
end;

It shows how i do interpret the use of the new component. Any feedback would be welcome, but please open a new thread for it.

[Kirill]

Hi, Michael!

The problem with TZTransaction component has been solved. Thank you very much.

Kirill.