Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008
    Posts
    2

    Unanswered: DB SQLSTATE check returns false value

    CREATE PROCEDURE NYCIKCP.LIB_SEQUNCE_GEN(IN "@ADB_DIVISION" VARCHAR(32))
    SPECIFIC LIB_SEQUNCE_GEN
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    NULL CALL
    LANGUAGE SQL
    BEGIN
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INT DEFAULT 0;
    DECLARE INT_COUNTER INTEGER DEFAULT 1;
    DECLARE VC_P_ID VARCHAR(32) DEFAULT NULL;

    DECLARE CURSOR_MAST_TABLE CURSOR FOR
    --SELECT P_ID FROM LIBRA_PROCESS_MAST MAST WHERE MAST.DIVISION_ID='adv_GCS' AND MAST.ADDENDUM IS NULL ORDER BY MAST.P_NAME;
    SELECT P_ID FROM LIBRA_PROCESS_MAST MAST WHERE MAST.DIVISION_ID=@ADB_DIVISION AND MAST.ADDEND_PARENT IS NULL ORDER BY MAST.P_NAME;
    OPEN CURSOR_MAST_TABLE;

    FETCH FROM CURSOR_MAST_TABLE INTO VC_P_ID;

    WHILE(SQLSTATE = '00000') DO
    UPDATE LIBRA_PROCESS_MAST MAST SET MAST.DIV_SHORT_CODE_INDEX=INT_COUNTER WHERE MAST.P_ID=VC_P_ID;
    FETCH FROM CURSOR_MAST_TABLE INTO VC_P_ID;
    SET INT_COUNTER=INT_COUNTER+1;
    END WHILE;

    CLOSE CURSOR_MAST_TABLE;

    END
    ;

    this proc when run, run for ages and does not terminate. When i debug this proc from Embarcadero DBArtisan i could detect that the while loop is causing to run the proc, as its not getting terminated even after the sqlstate changes from '00000' to '02000' when end of cursor record is reached.

    I have no clue why the while loop returns true for the check SQLSTATE = '00000') when the sqlstate vale is '02000'.

    i have tried replacing the while loop with if loop, i am still facing the issue. i belive the issue is not with the while or for loop but the way the check is performed in the proc.

    could someone suggest the correct way to perform the check and the exit procedure for end of cursor.

    refrence:
    http://publib.boulder.ibm.com/infoce...c/c0024361.htm

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Pretty obvious, however: Each SQL statement sets the SQLCODE and the SQLSTATE - each. If I count the number of SQL statements in the body of the loop, I see 3:
    • UPDATE
    • FETCH
    • SET

    The condition in the WHILE verifies the SQLSTATE of the last statement in the loop, i.e. the SET statement - not the FETCH.

    Also, I would recommend to replace the WHILE loop with a regular FOR loop. That saves you the cursor declaration, open, close, fetch (actually 2 fetch statements), and the test whether the loop has to terminate.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2008
    Posts
    2
    Thanks Stolze. It works great with changing the flow of execution of statements. Thanks a ton.
    Last edited by skt; 04-03-08 at 06:20.

Posting Permissions

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