September 14, 2005 at 10:46 pm
I have a Delphi application that uses ADO to call SQLServer 2000 stored procedures. When errors occurred in the SProc, the ADO does not always get the error and return to Delphi. As a result, the application continues as if the SProc has completed successfully.
The problem occurs when there are multiple "select" queries in the SProc. If the first query succeeds, the ADO will ignore the failures of the subsequent queries. For example is:
create procedure spTestError as begin select * from sysfiles -- this will fail when called 2nd time select * into DummyTable from sysfiles end
If I call the spTestErros 2 or more times in QA, error occurs.
However, when calling multiple times from Delphi via ADO, Delphi never knows the above SProce fails. This happens when I use TADOStoredProc or TADOCommand. For the TADOConnection, I set CursorLocation to clUseClient.
Any suggestions?
James
September 15, 2005 at 9:00 am
Somebody posted this link as an answer to my question on error handling in Stored Procedures a while ago:
September 15, 2005 at 2:40 pm
This is really a programming question. SQL Server is returning the error, but somewhere along the way it is being ignored by the application.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
September 16, 2005 at 4:18 pm
/*create procedure spTestError
as
begin
select * from sysfiles
-- this will fail when called 2nd time
select * into DummyTable from sysfiles
end
/*
You do not have a return value here.
Must be this
create procedure spTestError
as
begin
select * from sysfiles
-- this will fail when called 2nd time
select * into DummyTable from sysfiles
Return (@@Error)
end
in the Delphi you must call.
With spProcedure do begin
DataType := ftInteger;
Name := 'RETURN_VALUE';
Direction := pdReturnValue;
end;
intReturn := spProcedure.Parameters.ParambyName('RETURN_VALUE').Value;
Check the intReturn here.
Tin Le
Tin Le
September 16, 2005 at 8:24 pm
You don't have to use the return code although you can. SQL Server will return any error or information messages to the client without explicitly defining the return code like your example.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply