Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    kualaumpur
    Posts
    33

    Unanswered: Problem in HANDLING the SQLCODE in SP

    Hi gurus ,

    I have written the following code , but not able to trap the SQLCODE
    , EVEN though the input is not proper i am not getting the proper
    values of SQLCODE ...CAN ANYBODY TELL me how to trap the SQLCODE value in SP ?

    trinmoy




    CREATE PROCEDURE DB2ADMIN.Proc2 ( IN ll_P_id char(12),
    OUT SQLCODE_OUT int )
    RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare variables
    DECLARE SQLCODE INT DEFAULT 0;

    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT A.PCATEGORY AS P_CATEGORY,
    A.STATUS AS P_STATUS,
    FROM PROJECT A
    WHERE A.PRIMARYKEY = ll_P_ID ;

    -- Declare handler
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SET SQLCODE_OUT = 0;

    -- Cursor left open for client application
    OPEN cursor1;

    END P1

  2. #2
    Join Date
    Jun 2003
    Location
    Shanghai, China
    Posts
    8

    The Answer!

    Because the SET statement is a standard SQL , so it will return the SQLCODE = 0 , after finishing the SET statement the program exit!So you can not catch the SQLCODE of Exception!

    You can try the following:
    DECLARE EXIT HANDLER FOR SQLEXCEPTION;
    VALUES (0) INTO SQLCODE_OUT;

  3. #3
    Join Date
    Nov 2003
    Location
    kualaumpur
    Posts
    33

    Re: still the problem exists ..i need to trap the SQLCODE

    Originally posted by Jark
    Because the SET statement is a standard SQL , so it will return the SQLCODE = 0 , after finishing the SET statement the program exit!So you can not catch the SQLCODE of Exception!

    You can try the following:
    DECLARE EXIT HANDLER FOR SQLEXCEPTION;
    VALUES (0) INTO SQLCODE_OUT;
    -----------------------------------------------
    sorry jerk its not working ..still i am not able to trap it ...i want to get the SQLCODE ...FOR EXAMPLE IF SQLCODE = 100 (NOT FOUND..NEED TO DO SOME PROCESSING ....

    how to catch the SQLCODE OF the sql statement i am still unaware ....

  4. #4
    Join Date
    Nov 2003
    Location
    kualaumpur
    Posts
    33

    Re: The Answer!

    Originally posted by Jark
    Because the SET statement is a standard SQL , so it will return the SQLCODE = 0 , after finishing the SET statement the program exit!So you can not catch the SQLCODE of Exception!

    You can try the following:
    DECLARE EXIT HANDLER FOR SQLEXCEPTION;
    VALUES (0) INTO SQLCODE_OUT;



    hello gurus

    i am not able to trap the SQLCODE Still...i might be doig something wrong ...but unware whts is it ...
    i want to catch the SQLCODE FOR EXAMPLE if it is not found or may my for other cases

    REATE PROCEDURE DB2ADMIN.Proc2 ( IN ll_P_id char(12),
    OUT SQLCODE_OUT int )
    RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare variables
    DECLARE SQLCODE INT DEFAULT 0;

    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT A.PCATEGORY AS P_CATEGORY,
    A.STATUS AS P_STATUS,
    FROM PROJECT A
    WHERE A.PRIMARYKEY = ll_P_ID ;

    -- Declare handler
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    set SQLCODE_OUT = SQLCODE;

    -- Cursor left open for client application
    OPEN cursor1;

    IF SQLCODE_OUT !=0 THEN
    --- DO SOME PROSECCING ....;
    ELSE
    INSERT INTO SOME TABLE VALUES('11,'22');
    COMMIT;
    END IF ;

    END P1


    pls tell me how to trap the sqlcode for the above case ..and wht i am doing wrong ....

  5. #5
    Join Date
    Jun 2003
    Location
    Shanghai, China
    Posts
    8

    Try the following procedure!

    REATE PROCEDURE DB2ADMIN.Proc2 ( IN ll_P_id char(12),
    OUT SQLCODE_OUT int )
    RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare variables
    DECLARE SQLCODE INT DEFAULT 0;

    -- Declare cursor
    DECLARE cursor1 CURSOR FOR
    SELECT A.PCATEGORY AS P_CATEGORY,
    A.STATUS AS P_STATUS,
    FROM PROJECT A
    WHERE A.PRIMARYKEY = ll_P_ID ;

    -- Declare handler
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    set SQLCODE_OUT = SQLCODE;

    -- Cursor left open for client application
    OPEN cursor1;

    IF SQLCODE <>0 THEN
    --- DO SOME PROSECCING ....;
    ELSE
    INSERT INTO SOME TABLE VALUES('11,'22');
    COMMIT;
    END IF ;

    END P1

Posting Permissions

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