Proc*C Cursor Question

From: Dave Bornstein <davidb_at_dma.isg.mot.com>
Date: 1996/06/20
Message-ID: <31C98307.3007_at_dma.isg.mot.com>#1/1

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.

Does anyone know of a way to either fix the above problem, or suggest a different implementation alternative?

Thanks in Advance.

Dave Bornstein
Motorola ISG
davidb_at_dma.isg.mot.com Received on Thu Jun 20 1996 - 00:00:00 CEST