Debugger terminates on exception handling in HANA studio

[Former Member]

Hi,

i have this small piece of code with exception handling.

PROCEDURE "XYZ"."xyz::SP_STUDENT" ( IN IP_ID NVARCHAR(5),
IN IP_NAME NVARCHAR(5),
OUT O_MSG NVARCHAR(70)) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER 
	DEFAULT SCHEMA XYZ
	-- READS SQL DATA AS
AS BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
SELECT 'TestString' INTO O_MSG FROM DUMMY;
SELECT 'ERROR NUMBER OCCURRED' || ::SQL_ERROR_CODE || ::SQL_ERROR_MESSAGE INTO O_MSG FROM DUMMY;
INSERT INTO "XYZ"."STUDENT" VALUES(:IP_ID, :IP_NAME);
SELECT 'INSERT SUCCESS' || 'ID IS:' || :IP_ID || ' AND NAME IS ' || :IP_NAME INTO O_MSG FROM DUMMY;
END;
END;

When im trying to debug this my debugger is getting terminated. however if im commenting exception handling related code then it is fine. Is there any thing wrong in code. please advise

Thanks

[lucas_oliveira · Accepted solution]

Hi,

Not sure why the debug is failing but your code sure looks weird as the business logic (adding a new student) is inside the exit handler. You're probably looking for this instead:

CREATE PROCEDURE "xyz::SP_STUDENT" ( IN IP_ID NVARCHAR(5), IN IP_NAME NVARCHAR(5), OUT O_MSG NVARCHAR(70)) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER 
AS BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN  -- if I can't proceed with logic, then I'll process an error:
	SELECT 'TestString' INTO O_MSG FROM DUMMY;
	SELECT 'ERROR NUMBER OCCURRED' || ::SQL_ERROR_CODE || ::SQL_ERROR_MESSAGE INTO O_MSG FROM DUMMY;
	END;
	
	-- try to insert
	INSERT INTO "STUDENT" VALUES(:IP_ID, :IP_NAME);
	
	-- if its all good, I got success
	SELECT 'INSERT SUCCESS' || 'ID IS:' || :IP_ID || ' AND NAME IS ' || :IP_NAME INTO O_MSG FROM DUMMY;
END;

Maybe debugging now would be ok. Can you give that a try?

Regards,

Lucas de Oliveira

[Former Member · Accepted solution]

I was completely wrong for obvious reason. Thanks Lucas !