Re: Proc*C Cursor Question

Scott Urman wrote:
>
> 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)
>
> Let me know if you would like more info.
>
> |>
> |> Thanks in Advance.
> |>
> |> Dave Bornstein
> |> Motorola ISG
> |> davidb_at_dma.isg.mot.com
>
> --
> ------------------------------------------------------------------------
> Scott Urman Oracle Corporation surman_at_us.oracle.com

My two cents --

        In ProC, a given cursor is exists only once in the program. Thus you recursive function is opening the same cursor and reseting it to the start. After the call to the recursive function, the original cursor is a some indeterminate state. You have fortunately (unfortuantely) have it set to the top of the cursor.

        Also, cursor/SQL etc is not terribly effiecent used this way.

Suggestions:

        One can use the connect by clause to order all of the elements of the tree, and get them in order.

	If the connect by clause will not do it, either
	(1) gather all the data and write a simple C function to do the work
	(2) isolate the openning and closing of the cursor to each call of the function
		This probably is impossible in this example.

Michael Krolewski Received on Sat Jun 22 1996 - 00:00:00 CEST