Re: recursive function with cursor

On Thu, 18 Dec 1997, Michal Hajek wrote: > I want to use cursor in recursive function. But program > stops with error -400 "Fetch attempted on unopen cursor". > The same function without recursive call works good. > This is tracking: (FREC is the recursive funcion) > FREC (1) START OK > FREC (2) START OK (recursive call) > FREC (2) END OK > --the error: the FREC (1) cursor was closed by FREC (2) ??? > > I tried WITH HOLD, and also not to free cursor - no change. > > How to do it ? It isn't clear which language or version you are using, so I'm going to assume ESQL/C >= 5.00; NewEra and I4GL have different answers, though some of the reasons for the problem are the same. Cursor names are equivalent to global variables. That means that there is just one instance of the cursor across the entire program. That, in turn, means that when FREC (1) calls FREC (2) and FREC (2) opens the cursor, it implicitly closes the cursor in FREC (1), because they are both using the same cursor! And when FREC (2) returns, FREC (1) finds that FREC (2) closed the cursor, so it gets the error. (In I4GL, the cursor name is equivalent to a file static variable, but the same logic applies; the second invocation of FREC closes the cursor opened by the FREC (1)). How to fix the problem? Obviously, you have to use a different cursor name on each invocation of the function. Fortunately, with ESQL/C 5.00 and above, you can do just that: the cursor name can come from a string variable, so you simply do something like: $char c_name[20]; sprintf(c_name, "c_frec_%d", recursion_level); $declare $c_name cursor for ...; $open $c_name; while (...) { $fetch $c_name ...; } $close $c_name; The second level of function creates a different cursor, leaving the first level cursor open and in the same position. Just beware of transactions -- COMMIT or ROLLBACK closes all open cursors by default. I4GL doesn't handle string named cursors; you have to simulate them with functions which know about N different cursor names, taking care to manipulate the correct cursor name at all times -- painful. NewEra doesn't appear to understand that cursors can be given string names either (when translating inline SQL code), so presumably the same applies to it, or you can use the SQL statement classes to get around the problem. Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>