Re: recursion in Pro*C

On Tue, 27 Oct 1998 13:27:33 -0000, "Alan D. Mills" <alanmNOSPAM_at_uk.europe.mcd.mot.com> wrote:
>My dynamic SQL statement is picked up from another table and executed as a
>cursor. For each record retrieved I may then want to build another dynamic
>cursor for it and so on. My question is. What is the scope of a cursor
>(dynamic or otherwise) within the Pro*C program. I thought it was global
>nomatter where the cursor is actually defined. Can I define a C function
>which builds and executes a dynamic cursor and for each record retrieved
>call the same function to open the next level of dynamic cursor, returning
>to the correct record of the previous on completion?
>
>Is this possible as a recursive call. If not, can anyone suggest any
>alternative designs for this sort of thing.

PRO*C "has a tendency" (read: always, to my knowledge, which may be incomplete) to use the same memory each time a given cursor is defined. So that if your code is:
  Define & open cursor
  for each record
    call myself
  end
then when the recursion happens, the first cursor is redefined, and reopened. When the recursive call completes, the original loop is in a mess.

My solution for this is:
  Define & open cursor
  For each record
    store in array
  end
  for each array element
    call myself
  end

So the cursor is never opened both during the calling, and the called routine.

I hope there's a better way...
--
Tommy Wareing
MIS Group
Learning Resources
Oxford Brookes University
01865 483389 Received on Tue Oct 27 1998 - 08:16:08 CST