TADOStoredProc problem in Delphi 10.1 Berlin ?
TADOStoredProc problem in Delphi 10.1 Berlin ?
I think I might have a problem. I am firing a stored procedure on SQL Server 2012 using TADOStoredProc ( proc.Exec ). The original code was written in Delphi 5 and was sat inside a try .. except and when / if the proc failed the exception was handled:
try
..
proc.ExecProc
..
except
on e:Exception do
begin
WriteAndForwardError( format('%s',[e.Message]) );
if adoConnection.InTransaction then adoConnection.RollbackTrans;
exec_failed := true;
end;
end;
but now the same code ( ported over with minimal changes ) does not raise an error and I know it should be throwing back a Foreign Key Exception for FK_23. I have tested it on the old code base and same data in D5 causes this exception, but in D10.1 this exception is not happening.
Has anyone else seen this behaviour or is it just me
I think I might have a problem. I am firing a stored procedure on SQL Server 2012 using TADOStoredProc ( proc.Exec ). The original code was written in Delphi 5 and was sat inside a try .. except and when / if the proc failed the exception was handled:
try
..
proc.ExecProc
..
except
on e:Exception do
begin
WriteAndForwardError( format('%s',[e.Message]) );
if adoConnection.InTransaction then adoConnection.RollbackTrans;
exec_failed := true;
end;
end;
but now the same code ( ported over with minimal changes ) does not raise an error and I know it should be throwing back a Foreign Key Exception for FK_23. I have tested it on the old code base and same data in D5 causes this exception, but in D10.1 this exception is not happening.
Has anyone else seen this behaviour or is it just me
/sub interested
ReplyDeleteFirst Issue: Turns out I am using BEGIN TRY ... BEGIN CATCH in the procedure itself and that requires the ExecuteOptions to be set ( proc.ExecuteOptions := [eoExecuteNoRecords]; ).
ReplyDeleteSecond Issue: Also there is a bit of a bug in SQL Server to do with nested transactions; in that it thinks it has no transactions ( @@TRANCOUNT = 0 ) until "something" happens, so we now stick a select ( SELECT TOP 1 @ErrorString = 'Unspecified error running command by ' + SYSTEM_USER FROM sysobjects WHERE type = 'U' ORDER BY name ) before the SAVE TRANSACTION tran_point statements
If anyone wants the example harness code and stored proc I played around with you are welcome to it, especially if you think you can improve on my solution