Hi,

I am writing an application that requires a multi-level tree structure.

I'm storing the data in a table of the form

ID Description ParentId

For speed, I would like to write a couple of Stored procedures that can run
up and down the tree structure, but at the moment, I have somthing like
this:

CREATE PROCEDURE GetParentUnit_A(INOUT :Pid VARCHAR(10), IN :lvl INTEGER) AS
begin
print :lvl;
declare :i VARCHAR(10);
declare zz CURSOR FOR SELECT parentId from Units WHERE unitId = :Pid;
OPEN zz;
Fetch next from zz into :Pid;
if :Pid = 'Root' then
set :Pid = :Pid;
else
set :lvl = :lvl + 1;
Call GetParentUnit_B(:Pid,:lvl);
end if;
CLOSE zz;
end;

GetParentUnit_B is idential to the above except it calls GetParentUnit_A
just before the CLOSE zz.

My problem is that when I run this, I get 'Multiple Cursor Definition zz'

My question is, is there a way of making the CURSOR zz local to this
PROCEDURE, so that when GetParentUnit_B tries to declare one, it makes ITS
own local version.

If you were thinking of calling the cursor somthing else in GetParentUnit_B,
this won't help much as objects deeper than 2 levels down the tree will
still cause one or other of these procedures to be called more than once.

Bottom line is: Is there any way of accomplishing recursive Procedure calls
in Pervasive, or am I wasting my time trying to get it to work?

Thanks,

ChrisM.

--
Please remove shoes to reply

But what ... is it good for?
- Engineer at the Advanced Computing Systems Division of IBM, 1968,
commenting on the microchip.

I've tested recursion before in the database, and found that the engine used to
fail after about 120 levels, but that it crashed miserably. (Probably a stack
failure.) I believe they changed it to allow only 32 levels (pretty standard in
the SQL industry, or so I'm told) now.

However, I did not test with cursors in my tests. I would think that this
should be logged as a formal incident with Pervasive to get the best (i.e.
quickest) results. I'll try some of my own tests, though, in the meantime...
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: August, 2004: See our web site for details!


ChrisM wrote:

Hi Bill,

Thanks for your reply. 32 Levels of recursion will be more than enough. The
normal depth of the tree is about 3-4 levels...

Do you have any simple examples of your recursive code that you would be
prepared to share? I'm prettu new to Pervasive and to some extent Stored
Procedure writing and would appreciate any help I can get.

Cheers,

Chris.

--
Please remove shoes to reply

But what ... is it good for?
- Engineer at the Advanced Computing Systems Division of IBM, 1968,
commenting on the microchip.
"Bill Bach" <bbach (AT) cncdsl (DOT) com> wrote

Here's one of my examples that I played with:

CREATE TABLE DepthTest (Depth INTEGER) #
SELECT * FROM "DepthTest" #
delete from DepthTest #

The above first statement was run to build a file to test with. The other two
were used to extract data and clean out the file for the next run. Then, the
Stored proc was created as:

CREATE PROCEDURE TestRecursion(in epth integer) WITH DEFAULT HANDLER
AS BEGIN
DECLARE :v1 CHAR(10);
DECLARE :v2 CHAR(50);
DECLARE :v3 CHAR(20);
DECLARE :Result Integer;
INSERT INTO DepthTest VALUES (epth);
IF epth > 0 THEN
CALL TestRecursion(epth - 1);
END IF;
END;

When run for with the command "CALL TestRecursion(x)", it properly populated the
DepthTest data file with sequential descending numbers. This worked for both 5
and 10 with ease. However, running with 50 iterations (X=50) failed on
PSQL2000i. On PSQLV8.1, it ran to over 700, but failed by 900.

That was as far as I tested, and I believe Pervasive has now capped it at 32
levels. Of course, the number and size of local variables may impact the stack
allocation as well.

HTH...
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: August, 2004: See our web site for details!


ChrisM wrote:

Here is the response from Pervasive that I previously received regarding
nested Stored Procedures with cursors:

"I have some news on this. I've found that the engine currently does not
support cursors in nested stored procedures.

Sr. Developer Support Engineer
Pervasive Software"

Regards,
- Greg

"ChrisM" <chris_mayers_blue (AT) Suede (DOT) Yahoo.com> wrote

Hi Bill and Greg,

Thanks for your help.
However, according to Greg's post, I can't use Cursors in recursive
procedures. Since the work I need to do involves working through a series of
records at each recursive-level - ie needs to run a cursor. Looks like I
can't do this with the DB engine. Will have to load the records up into my
app, and deal with it all in application code.

I have to say, I've been using (a trial version of) Pervasive for a week or
so now, and I'm pretty un-impressed with the Performance, Stability and
Support - present company excepted ;-) of it so far...

Regards,

Chris.

--
Please remove shoes to reply

But what ... is it good for?
- Engineer at the Advanced Computing Systems Division of IBM, 1968,
commenting on the microchip.
"ChrisM" <chris_mayers_blue (AT) Suede (DOT) Yahoo.com> wrote