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
Sending BeginTransaction to the server
Re: Sending BeginTransaction to the server
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:
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;