Skip to Content
0

Debugger terminates on exception handling in HANA studio

Mar 28, 2017 at 05:14 PM

35

avatar image

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

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Lucas Oliveira
Mar 29, 2017 at 01:01 AM
1

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

I was completely wrong for obvious reason. Thanks Lucas !

0