Hi,

I have created a stored procedure (see below) which is supposed to update
columns called LeftExtent and RightExtent. The procedure compiles and runs,
but the results are not what I would expect.

After running the procedure the table's LeftExtent fields have the values
that I would expect to find in the RightExtent fields and the RightExtent
fields are unchanged (all rows with value 0).

Is there something wrong with this Stored Procedure, or is this a Pervasive
bug?

WinXP Pro, Pervasive.SQL V8 WGE SP1.

Thanks,
- Greg


CREATE PROCEDURE PlantHierExtent();
BEGIN
-- our counter is the variable used to set the left and right extent
DECLARE :counter INTEGER;
SET :counter = 1;

-- bDown signifies that we are traversing down to the first child
-- or to the next sibling
DECLARE :bDown INTEGER;
SET :bDown = 1;

-- blank GUID used for comparisons
DECLARE :gBLANK BINARY(16);
SET :gBLANK = '00000000000000000000000000000000';

-- variable to store the record that we are dealing with
DECLARE :gPK BINARY(16);
-- what we do is start from the ROOT
SET :gPK = '00000000000000000000000000000001';

-- Variables to store data retrieved from the cursor
DECLARE :gParent BINARY(16);
DECLARE :gFirstChild BINARY(16);
DECLARE :gNextSibling BINARY(16);
DECLARE :LeftExtent UINTEGER;
DECLARE :RightExtent UINTEGER;

-- Loop through the whole hierarchy until we run out of keys to process
UpdateLoop:
WHILE (:gPK <> :gBLANK) DO
-- Find the record with the primary key in :gPK
DECLARE c1 cursor FOR SELECT gFirstChild, gNextSibling, gParent,
LeftExtent, RightExtent
FROM PlantHier
WHERE gPK = :gPK
FOR UPDATE;
OPEN c1;
FETCH NEXT FROM c1 INTO :gFirstChild, :gNextSibling, :gParent,
:LeftExtent, :RightExtent;
IF SQLSTATE = '02000' THEN
-- Record not found (this should not be possible)
PRINT 'Record Not Found';
LEAVE UpdateLoop;
END IF;

IF (:bDown = 1) THEN
-- We are trying to traverse down or accross
IF (:gFirstChild <> :gBLANK) THEN
-- if there is a child, set the LeftExtent, we'll update
-- the Right on the way back up the tree
UPDATE SET LeftExtent = :counter
WHERE CURRENT OF c1;
SET :counter = :counter + 1;
SET :gPK = :gFirstChild;
ELSE
IF (:gNextSibling <> :gBLANK) THEN
-- if there is a sibling, set the LeftExtent &
RightExtent as
-- we are finished with this record
SET :gPK = :gNextSibling;
UPDATE SET LeftExtent = :counter, RightExtent = :counter
+ 1
WHERE CURRENT OF c1;
SET :counter = :counter + 2;
ELSE
-- we are finished with this branch, so set the
LeftExtent
-- & RightExtent and prepare to move back up the tree
SET :gPK = :gParent;
UPDATE SET LeftExtent = :counter, RightExtent = :counter
+ 1
WHERE CURRENT OF c1;
SET :counter = :counter + 2;
SET :bDown = 0;
END IF;
END IF;
ELSE
-- We are trying to traverse across or up
-- We are finished with the current record, so set it's
RightExtent
UPDATE SET RightExtent = :counter
WHERE CURRENT OF c1;
SET :counter = :counter + 1;
IF (:gNextSibling <> :gBLANK) THEN
-- There is a sibling, so notify the loop that we want to
attempt
-- to traverse down again
SET :gPK = :gNextSibling;
SET :bDown = 1;
ELSE
-- This branch is complete, try moving up again
SET :gPK = :gParent;
END IF;
END IF;
CLOSE c1;

END WHILE;
END;

Greg:

I'm not sure that the query as written is a good idea. You're obviously
traversing a tree structure, so it would seem to me that a simple recursion
would be a much better solution. The SRDE supports recusrive procedures, but
both engines (PSQL2000 and PSQLV8) crash if it goes too far (although V8 went
MANY more levels than P2000 in my own tests). Fixes should soon be available to
guarantee 32 levels of recursion, a la Microsoft SQLServer. (I was able to get
V8 to go several hundred levels before it crashed out, though.)

Perhaps if you try changing the logic it will make it a bit easier to
troubleshoot?
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: November 18-20: See our web site for details!
Chicago Cubs: The Tradition Continues!


Greg Doherty wrote:

UPDATE:
Pervasive has confirmed that there is a bug in the database engine causing
this Stored Procedure to work incorrectly.

- Greg

"Greg Doherty" <NoSpam (AT) nospam (DOT) com> wrote