EDatabaseError on form close(Operation can't be performed on active transaction)

[Max3073]

Hi lazarusians

I tried to use the TMySQL50Connection, TSQLTransaction, TSQLQuery, TDatasource, TDBGrid to make a simple application:
Pressing Button1, it connects to MySQL locally and shows the result of a simple select sql statement.
Pressing Button2, the connection is closed (conn.connected:=False)

Everything works fine but:

1. if I press button1 and then i close the app i get "EdatabaseError: Operation cannot be performed on an active transaction"
2. if I press button1 and then button2 and then i close the app i get "EdatabaseError: Operation cannot be performed on an active transaction"

The only way to never get that exception is to never open the connection.
Everybody can tell me how to avoid this problem?
Thanks

[Arbee]

Not sure if this applies to you, but I had these when forgetting to commit (or roll back) the transaction.

[geno]

in your Form OnClose event, make sure you free your MySQL objects:

Code: [Select]

MySQL50Connection1.Free;
DataSource1.Free;
SQLQuery1.Free;

If you don't you would get something like this when run from the command line, or view in the debug output window View->Debug Windows->Debug Output.  (ensure you
enable HeapTrc unit (-gh) in Project ->Project Options->Linking)

EXAMPLE OUTPUT:
Heap dump by heaptrc unit
16505 memory blocks allocated : 3951656/4016416
16495 memory blocks freed     : 3950678/4015432
10 unfreed memory blocks : 978            <-----------------------------this should be 0 unfreed
True heap size : 1572864
True free heap : 1570272
Should be : 1570600

This should take care of the problem.

  geno.

[Max3073]

To Arbee:

I do not have anything to commit: I make no change to data and I get that error

To geno:

This seems quite strange: when I drop components on a form I don't have to free them programatically in the OnClose event.

[geno]

You are right - I never have been concerned about freeing other visual components, but for some reason these in particular (especially so with TMySQL50Connection) will leave unfreed memory blocks, which can cause problems.  I have tested both ways in different applications with the same results.  DataSource1 and SQLQuery1 don't seem to need to be explicitly freed, however, if I do for one, I do for the other just for aesthetics.

Now, it's possible that this is not your problem, however it is worth a shot.

geno.

[erkka]

Same kind of problem here.

My project used to work fine. Then I got a new computer, installed lazarus and all the necessary files etc. It is all OK, only that I also get "operation can't be performed on an active transaction" when closing the application.

I've got two SQLTransactions, and strangely enough the first one seems to work but the second one doesn't.

I tried:

Code: [Select]

  SQLTransaction1.Active:=false;
  SQLTransaction2.Active:=false;

  SQLTransaction1.Free;
  SQLTransaction2.Free;

And it crashes on SQLTransaction2.Free;

Any ideas what wen't wrong?

I'm using Lazarus version 0.9.30, FPC 2.4.2, SVN Revision 29749

EDIT:

SUCCESS!!

I changed it to

Code: [Select]

  SQLTransaction1.EndTransaction;
  SQLTransaction2.EndTransaction;

and it works again. No need to .Free anything

[geno]

I just checked my example using .EndTransaction, and it still leaves 10 unfreed memory blocks; that is why I have been using the .Free method - this works for me;



geno.

[erkka]

So might there be a bug somewhere?

For a while my code worked fine, HeapTrc reported no unfreed memory blocks.

But now it again crashes, complaining "EdatabaseError: Operation cannot be performed on an active transaction".

I have written my own procedure, which closes the transactions. And it works fine. It only raises exception after that, returning the debugger inside destructor TControl.Destroy in control.inc

This is beyond my understading. First it manages to close the transaction with no problem, and in the next instant it complains that it can't destroy an active transaction ???

help, please, anyone 

EDIT,
for testing, I tried this;

Code: [Select]

procedure TMyForm2.Closedatabases
begin
  SQLQuery1.Free;
  SQLQuery2.Free;
  SQLQuery3.Free;

  SQLTransaction1.Rollback;
  SQLTransaction1.Active:=false;
  SQLTransaction1.Free;

  ODBCMyConn.Free;         
end;

and - it crashes at "SQLTransaction1.Free", complaining that operation cannot be performed on active transaction.

Those component's aren't created manually in my code, I just dragged and dropped them on MyForm2.

---

EDIT2;
I even tried

Code: [Select]

  SQLTransaction1.Active:=false;
  if SQLTransaction1.Active = true
   then showmessage('SQLTransaction1.Active=true')
   else showmessage('SQLTransaction1.Active=false');

and it shows the message with true. Uh oh.
I also tried with SQLTransaction1.EndTransaction, but with same result - in the next instant it still seem that SQLTransaction1.Active = true.

I find all of this hard to understand. I know this kind of issues might be caused by memory leaks elsewhere in the code. But a moment ago it was all working fine and HeapTrc reporting no unfreed memory blocks. Any ideas? I have none.

[Buckwheat]

I had very similar problem: TSQLTransaction.Free worked most of the time fine, but sometimes it generated EDatabaseError exception with "Operation cannot be performed on an active transaction" message.

Lazarus version: 0.9.30.4
Lazarus svn revision: 35940
Lazarus build date: 2012/03/14
Lazarus was compiled for i386-win32
Lazarus was compiled with fpc 2.6.0

This is how I fixed this problem:

Add a new class:

Code: [Select]

TCustSQLTransaction = class(TSQLTransaction)
public
  procedure CloseTrans;
end;

procedure TCustSQLTransaction.CloseTrans;
begin
  inherited;
end;

Change

Code: [Select]

FTransaction: TSQLTransaction;

to

Code: [Select]

FTransaction: TCustSQLTransaction;

Change

Code: [Select]

FTransaction := TSQLTransaction.Create(nil);
to

Code: [Select]

FTransaction := TCustSQLTransaction.Create(nil);
Add

Code: [Select]

FTransaction.CloseTrans;
before

Code: [Select]

FTransaction.Free;
"Operation cannot be performed on an active transaction" problem happens because TSQLTransaction.Commit calls TSQLConnection.Commit. If TSQLConnection.Commit returns True, then TSQLTransaction.Commit calls TDBTransaction.CloseTrans which makes transaction inactive. TSQLConnection.Commit is abstract. So, TODBCConnection.Commit is called. But TODBCConnection.Commit is not implemented, it always returns false, so TSQLTransaction.Commit never calls TDBTransaction.CloseTrans !

Code: [Select]

procedure TSQLTransaction.Commit;
begin
  if active then
    begin
    closedatasets;
    If LogEvent(detCommit) then
      Log(detCommit,SCommitting);
    if TSQLConnection(Database).commit(FTrans) then
      begin
      closeTrans;
      FreeAndNil(FTrans);
      end;
    end;
end;

TSQLConnection = class (TDatabase)
  ...
  function Commit(trans : TSQLHandle) : boolean; virtual; abstract;
  ...
end;


function TODBCConnection.Commit(trans: TSQLHandle): boolean;
begin
  // Tranactions not implemented yet
end;

Procedure TDBTransaction.CloseTrans;
begin
  FActive := false;
end;