Skip to Content

Debugger terminates on exception handling in HANA studio

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Mar 29, 2017 at 01:01 AM

    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

    Add comment
    10|10000 characters needed characters exceeded