Re: Proc*C Cursor Question

On Thu, 20 Jun 1996 12:57:43 -0400, Dave Bornstein <davidb_at_dma.isg.mot.com> 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);
>

Try

select mid, pid, level
  from T
  start with mid = :X
  connect by prior mid = pid

It'll do the whole thing for you :)

>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 problem here is that you are (indenting to show the level of recursion)

1. open curs mess-cur with 0
2. fetch (0)100 from said cursor
3. call find children
4. REOPEN curs mess-cur with 100 (this is important, you CLOSED and REOPENED the cursor, you didn't allocate a 'new' cursor on the stack. EXEC SQL statements are positional and have NO SCOPE. Once you declared mess_cur in this function, it was available to all functions positioned below this one in the code. All references to mess_curs would be to the same, global, static object (look at the top of the generated code, there is really only one mess_curs)
5. Fetched the first ROW from this (1) 101
6. REOPEN curs mess-cur with 101
7. The FETCH failed
8. Tried to fetch the second row (but the fetch already failed with 1401 before). This DOES NOT RETURN 1401 but rather a FETCH OUT OF SEQUENCE error, you are fetching past the end of a cursor. Since the fetch did not return 1401 but some other error message, you do not break out of loop.
9. You print out whatever was last in your variables, the fetch didn't touch them.
10. REOPEN curs mess-cur with 101
11. The Fetch failed
12. goto 8 (forever loop.....)

>
>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

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Jun 21 1996 - 00:00:00 CEST