User transaction is alredy in progress

[stians]

Hello

I have a problem, ther i get this error msg: User transaction is alredy in progress

I have a Stringlist with Sql, Inserts and updates and ig run this code:

Function Tzdb.SendSQLExec(SQL:String):boolean;
var
SQLList:TstringList;
fnr:integer;
begin
if(GlobalDebug)Then SQLLOG(SQL);
if(Trim(SQL) = '')then Exit;
Try
SQLList:=TstringList.Create;
Try
CheckConnectionAndFixit;// Connect again if timeout
SQLList.Text:=Trim(SQL);
Result:=True;
MyConnection1.StartTransaction;

For fnr:=0 to SQLList.Count-1 do
begin
MyQuery1.Close;
MyQuery1.SQL.Text:=Trim(SQLList.Strings[fnr]);
MyQuery1.Execute;
end;

MyConnection1.Commit;
Except
Result:=False;
end;
finally
SQLList.Free;
end;
end;

Is this wrong? Is it any way to do this bether? I want to rollback if any sql fails.

Stians.

[Antaeus]

If any error happens between starting and committing transaction, the transaction stays active. To prevent this try to change following piece of code:

Code: Select all

  Except 
  Result:=False; 
  end; 

to this one:

Code: Select all

  Except 
    Result := False; 
    MyConnection1.Rollback;
  end; 

Now, if error happens, transaction becomes closed and all changes are discarded.

[stians]

Great,
Is this related to the: User transaction is alredy in progress error message?

Stians

[Antaeus]

This error is raised if you try to start transaction (MyConnection.StartTransaction) and it is already started (MyConnection.InTransaction=True). To finish transaction you should either commit it (MyConnection.Commit) or rollback (MyConnection.Rollback). In your code if error happens on MyQuery1.Execute, neither COMMIT nor ROLLBACK of the transaction will be called. So second call of Tzdb.SendSQLExec will lead to error raising.