Re: Proc*C Cursor Question

In article <31C98307.3007_at_dma.isg.mot.com>, Dave Bornstein <davidb_at_dma.isg.mot.com> writes:
|> 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?

You are correct as to the problem here. Unfortunately, regular cursors like these won't work for you, since they are not true C variables, and are global to the entire program. You have two options:

1. OCI
2. Cursor variables (with Oracle 7.2 and Pro*C 2.1 or higher)

|>
|> Thanks in Advance.
|>
|> Dave Bornstein
|> Motorola ISG
|> davidb_at_dma.isg.mot.com
 

-- 
------------------------------------------------------------------------
Scott Urman            Oracle Corporation           surman_at_us.oracle.com
------------------------------------------------------------------------
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2
Published by Oracle Press - http://www.osborne.com/oracle/index.htm
------------------------------------------------------------------------
"The opinions expressed here are my own, and are not necessarily that of
 Oracle Corporation"
------------------------------------------------------------------------