DB SQLSTATE check returns wrong value

[skt]

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/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.sql.doc/doc/c0024361.htm

[sakumar9]

As of now, you can try following code. It will solve your purpose.

I have modified the code to work for my sample database. You can either try it against sample database or change it according to your requirement.

DROP PROCEDURE jairam@
 
CREATE PROCEDURE jairam()
MODIFIES SQL DATA
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL
BEGIN
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE SQLCODE INTEGER DEFAULT 0;
  DECLARE errorLabel CHAR(32) DEFAULT '';
 
DECLARE INT_COUNTER INTEGER DEFAULT 1;
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE VC_P_ID INTEGER;
DECLARE CURSOR_MAST_TABLE CURSOR FOR SELECT ID FROM STAFF;
 
DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET at_end = 1;
 
DECLARE EXIT HANDLER FOR SQLEXCEPTION
  SIGNAL SQLSTATE value SQLSTATE SET MESSAGE_TEXT = errorLabel;
 
OPEN CURSOR_MAST_TABLE;
 
FETCH FROM CURSOR_MAST_TABLE INTO VC_P_ID;
 
WHILE(at_end = 0) DO
UPDATE STAFF SET SALARY=SALARY + 1 WHERE 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@

Regards
-- Sanjay

[sakumar9]

The problem that you are facing is because of SET command that you have after FETCH. SET is also an SQL and will always pass. So it overwrites the SQLSTATE of FETCH. So the condition in WHILE loop is always true.

Just change the order of FETCH and SET, make FETCH as last statement in WHILE loop. This will solve your purpose.

Hope it clears your doubt.

Regards
-- Sanjay