Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    16

    Unanswered: stored procedure error handling incorrect output

    I am trying to check error handling , but here if I run the below procedure and if the sqlcode out for the select within insert is 0 , it workd fine , there is not a problem, but when the select sql code is <>0 i.e for unsuccesful execution , it gives me error message out but I am getting the sqlcode_ out output parameter as 0 it should give 100 or anything other than 0.

    DECLARE P_VAR INTEGER;
    DECLARE SQLERROR_VAR VARCHAR(50) DEFAULT 'SUCCESSFUL EXECUTION';
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000' ;
    DECLARE SQLCODE INT DEFAULT 0;


    -- Declare handler
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT FROM SYSIBM.SYSDUMMY1;

    -- INITIALIZE OUTPUT SQL PAAMAETERS
    SET P_VAR = P_PLAN;

    INSERT INTO SESN.TE_DEL
    SELECT A.DOC_SY, A.BAN_SY,B.MNO, B.FORM_ID,P_VAR FROM SESN.SET_DOC AS A, SESN.SET2_DOC AS B
    WHERE A.DOC_SY = B.DOC_SY
    AND A.UP_DTTM is NOT NULL
    AND A.START = 'UPED';
    IF SQLCODE <> 0 THEN
    -- SET SQLCODE_OUT = SQLCODE;
    SET SQLERROR_VAR = 'NO STATS FOUND UPED FOR P_VAR';
    SET MESSAGE_OUT = SQLERROR_VAR;
    -- SET SQLSTATE_OUT = SQLSTATE;
    -- SET SQLCODE_OUT = SQLCODE;
    LEAVE P1;
    ELSE
    COMMIT;
    END IF;
    .
    .
    .
    .
    .
    END P1

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    NEE, your Exit Handler doesn't look right to me. I have never seen a Select statement in one before.

    According to the SQL Reference manual Vol 2 for V9.5, the syntax is:

    DECLARE EXIT HANDLER FOR ...

    Specific-condition value:

    SQLSTATE string-constant
    or
    condition-name

    general-condition:

    SQLEXCEPTION
    SQLWARNING
    NOT FOUND
    (or any combination)

    Try something like

    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET sqlcode_out = SQLCODE, sqlstate_out = SQLSTATE;
    Last edited by Stealth_DBA; 06-03-09 at 20:20.

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    deleting my post....
    Last edited by rahul_s80; 06-04-09 at 01:32.
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  4. #4
    Join Date
    May 2009
    Posts
    16
    Thanks Stealth_DBA

    I have tried this way even , but it makes no difference, if you use Select from Sysibm tables or use this way of declaration.

    But basically what I see here is in Declaration part I had Set the SqlCOde_out as default to be zero, then after unsuccesful exceution of Insert , my sqlcode is <> 0 so the same should get stored into SqlCode_out in the next satement where I am using SET SQLCODE_OUT = SQLCODE; as mentioned below.


    INSERT INTO SESN.TE_DEL
    SELECT A.DOC_SY, A.BAN_SY,B.MNO, B.FORM_ID,P_VAR FROM SESN.SET_DOC AS A, SESN.SET2_DOC AS B
    WHERE A.DOC_SY = B.DOC_SY
    AND A.UP_DTTM is NOT NULL
    AND A.START = 'UPED';
    IF SQLCODE <> 0 THEN
    -- SET SQLCODE_OUT = SQLCODE;
    SET SQLERROR_VAR = 'NO STATS FOUND UPED FOR P_VAR';
    SET MESSAGE_OUT = SQLERROR_VAR;
    SET SQLSTATE_OUT = SQLSTATE;
    SET SQLCODE_OUT = SQLCODE;
    LEAVE P1;
    ELSE
    COMMIT;
    END IF;

    But strangely when i use instead statement as
    INSERT INTO SESN.TE_DEL
    SELECT A.DOC_SY, A.BAN_SY,B.MNO, B.FORM_ID,P_VAR FROM SESN.SET_DOC AS A, SESN.SET2_DOC AS B
    WHERE A.DOC_SY = B.DOC_SY
    AND A.UP_DTTM is NOT NULL
    AND A.START = 'UPED';
    IF SQLCODE <> 0 THEN
    -- SET SQLCODE_OUT = SQLCODE;
    SET SQLERROR_VAR = 'NO STATS FOUND UPED FOR P_VAR';
    SET MESSAGE_OUT = SQLERROR_VAR;
    SET SQLSTATE_OUT = SQLSTATE;
    SET SQLCODE_OUT = 100;
    LEAVE P1;
    ELSE
    COMMIT;
    END IF;

    ; it gives me output as 100. so why it is not reading sqlcode for execution of the insert statement

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by NEE
    so why it is not reading sqlcode for execution of the insert statement
    Because it is reset by subsequent statements. What you are returning is SQLCODE after "SET SQLSTATE_OUT = SQLSTATE", which is 0, since that statement completed successfully.

    In other words, saving the value of SQLCODE in SQLCODE_OUT must be the very first statement in the exception handler. If you need both SQLCODE and SQLSTATE you must set both OUT variables in a single statement.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •