Re: Proc*C Cursor Question

Hi Dave,

obviously the cursor is not closed until it is reopened again. How to circumvent the problem here , I don't know.
Though, you might want to take a look at the "CONNECT BY PRIOR" SQL possibility which is perfect when using parent structures. It is a different approach but you will get all the children.

Dave Bornstein wrote:
>
> I am having a problem with Pro*C and cursors. I am trying to use a
> recursive function to retrieve data. The data is in the form of
> messages. Each Message has a Message Id and a Parent Id. A Message may
> be both a parent and a child. I am trying to create a tree of all
> children objects and the childrens children for a particular message.
>
> The data looks as follows:
>
> mid number (Message Id)
> pid number (Parent Id)
>
> mid pid
> --- ---
> 100 0
> 101 100
> 102 100
> 103 102
>
> Using the call: find_children(100,0);
>
> For the above data, the results I am expecting are
> (0)100
> (1)101
> (1)102
> (2)103
>
> The number in parenthesis is the amount of tabs the tree would use. The
> code I am using follows:
>
> int find_children(inId, indent)
> int inId;
>
> {
> int currId;
>
> EXEC SQL DECLARE mess_cur CURSOR FOR
> SELECT mid
> FROM <table>
> WHERE pid = :inId;
>
> EXEC SQL OPEN mess_cur;
> EXEC SQL WHENEVER NOT FOUND DO break;
> for (;;) {
> EXEC SQL FETCH mess_cur into :currId;
> printf("(%i) %i\n",indent, currId);
> find_children(currId, indent+1);
> }
 EXEC SQL CLOSE mess_cur;
> }
>
> The results I am getting are:
>
> (0)100
> (1)101
> (1)101
> (1)101
> ... Forever.
> Obviously, the original cursor mess_cur is not being replaced with the
> new host variable in the subsequent calls of find_children. I need to
> use some kind of recursive function because any Message may have any
> amount of children, and each child can have any amount of children.
Received on Mon Jun 24 1996 - 00:00:00 CEST