Sending BeginTransaction to the server

[RajvirSangha]

Hi,
In my application, if my database connection gets lost while I am sending new transaction to the server then it does not show the exception or break until I reach to the TheQry.Open; statement (TheQry : TSQLQuery). It seems that it does not send BeginTransaction command to the server. Is there any mechanism so that I can trap this exception on BeginTransaction statement or BeginTransaction command is to be sent to the server everytime when I use it?
Thanks

[AndreyZ]

Hello,

The point is that SQL Server starts a transaction only when an actual operation is performed. It means that when you call the BeginTransaction method, SQL Server does not start the transaction, it is started when you call the TSQLQuery.Open method. We cannot influence such SQL Server behaviour.
To avoid this problem, you can execute a dummy query before performing an actual operation. Here is a code example:

Code: Select all

var
  tr: TDBXTransaction;
begin
  SQLQuery1.SQL.Text := 'select 1';
  SQLQuery1.Open; // here connection is reestablished (if it is possible) if it was lost
  SQLQuery1.Close;
  tr := SQLQuery1.SQLConnection.BeginTransaction;
  try
    // your code
    SQLQuery1.SQLConnection.CommitFreeAndNil(tr);
  except
    SQLQuery1.SQLConnection.RollbackFreeAndNil(tr);
    raise;
  end;
end;