I'm using Pervasive Database v8.7 and I have the following stored
procedure that It doesn't work and goes into a endless loop.

Is it that this database doesn't support nested cursors?

thank you.

CREATE PROCEDURE populate_test() AS
BEGIN

DECLARE :v_parent CHAR(20);
DECLARE :v_part1 CHAR(20);

declare x cursor for
select distinct parent from BOM_MSTR;

open x;

delete from BOM_TEST3;

INSLOOP:
LOOP

IF SQLSTATE = '02000' THEN
LEAVE INSLOOP;
END IF;

FETCH NEXT FROM x INTO
:v_parent;

declare y cursor for
select distinct part from BOM_MSTR
where parent=:v_parent;

open y;
INSLOOP2:
LOOP
IF SQLSTATE = '02000' THEN
LEAVE INSLOOP2;
END IF;


FETCH NEXT FROM y INTO
:v_part1;

START TRANSACTION;

INSERT INTO BOM_TEST3 (Parent,Part,lvl)
values (:v_parent,:v_part1,1);
COMMIT WORK;

END LOOP;
CLOSE y;

END LOOP;
CLOSE x;
END

Posted a reply on the Pervasive forum.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive Training - March 2009 in Chicago ***


tulio salinas wrote: