Need help with a SQL0508N error (The cursor specified in the ....)

[gimme_this...@yahoo.com]

I'm new to "where current of cursor" and I'm hoping someone can
eyeball this procedure and figure out why this error occurs:

24504(-508)[IBM][CLI Driver][DB2/SUN64] SQL0508N The cursor
specified in the UPDATE or DELETE statement is not positioned on a
row. SQLSTATE=24504
(2.09 secs)

The procedure works okay if I don't use "WHERE CURRENT of cs_cursor"
and use "WHERE PE.PERSON_ID=l_current_person_id AND
PE.l_current_region_id=l_person_region_id" instead.

I tried: WHERE PE.PERSON_ID=l_current_person_id AND
PE.l_current_region_id=l_person_region_id CURRENT OF cs_cursor;

But that errors out.

Also - do I need to select the columns that are to be updated?

Thanks.


CREATE PROCEDURE MYSCHEMA.AVAIL_CONV()
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA

BEGIN

DECLARE SQLSTATE char(5);
DECLARE l_update_count int default 0;
DECLARE l_counter int default 0;
DECLARE l_current_person_id bigint;
DECLARE l_current_region_id integer;
DECLARE l_temp_active_flag character;
DECLARE l_perm_active_flag character;
declare l_avail_flag character;
declare l_avail_stts_chg_date timestamp;
DECLARE l_commit_freq int default 1000;
declare eof smallint default 0;
declare continue handler for sqlstate '02000' set eof = 1;

BEGIN

DECLARE cs_cursor CURSOR WITH HOLD FOR
SELECT PE.PERSON_ID, PE.PERSON_REGION_ID, PE.TEMP_ACTIVE_FLAG,
PE.PERM_ACTIVE_FLAG,
PE.AVAIL_FLAG,PE.AVAIL_STTS_CHG_DATE
FROM MJXX.PEOPLE PE FETCH FIRST 2000 ROWS ONLY
FOR UPDATE;

OPEN cs_cursor;

WHILE eof = 0
DO
FETCH cs_cursor into l_current_person_id,
l_current_region_id, l_temp_active_flag,l_perm_active_flag,
l_avail_flag,l_avail_stts_chg_date;

IF ('Y' = l_temp_active_flag or 'Y' = l_perm_active_flag) then
UPDATE MJXX.PEOPLE PE SET
PE.AVAIL_FLAG='Y', PE.AVAIL_STTS_CHG_DATE = CURRENT TIMESTAMP
WHERE CURRENT OF cs_cursor;
ELSE
UPDATE MJXX.PEOPLE PE SET PE.AVAIL_FLAG='N',
PE.AVAIL_STTS_CHG_DATE = CURRENT TIMESTAMP
WHERE CURRENT OF cs_cursor;
END IF;

SET l_update_count = l_update_count + 1;
SET l_counter = l_counter + 1;

IF l_counter = l_commit_freq
THEN
COMMIT WORK;
SET l_counter = 0;
END IF;

END WHILE;
CLOSE cs_cursor;

END;
END@

CALL MYSCHEMA.AVAIL_CONV2@

[Bruce]

On Feb 8, 10:17 pm, "gimme_this_gimme_t...@yahoo.com"

I have several thoughts...

First, I would do this:

OPEN ...

Fetch...

while do...until EOF
process from previous fetch...
..more code
fetch
done

close...

Also...I believe that when you COMMIT you will lose your position in
the RESULT SET unless you use WITH HOLD...

-B

[sunfe...@yahoo.com.cn]

Although you have set the continue handle。but when the handle is
triggered by "rows not found"( sqlstate '02000)
the update statment will still execute until the last while loop is
completed done。