Re: Recursive Cursor

From Paul Hatcher: } } > Read following 4GL program } > } > function recursive_call() } > } > declare c_cursor cursor for } > select * from any_table } > where any_field = any_criterion } > foreach c_cursor into pr_any_table.* # pr_any_table record like } > any_table.* } > if something_ok then } > call recursive_call() } > else } > call do_else() } > end if } > end foreach } > free c_cursor } > } > end function } > } > The problem in this function is that when the function is called second } > time } > recursively and finish foreach loop, it closes the cursor c_cursor. At } > same } > time, it closes the cursor opened when the function was called first } > time. } > So, When control returns to first layer and fetchs the cursor again, } > error } > -400 occurs ("Fetch attempted on unopen cursor"). } } A general technique is to turn the recursion into iteration. } } You have a temp table that contains all rows to be processed and a query } that tells you whether there are any rows to be processed. } } The first set of rows are inserted into the temp table and you then } process them deleting as you go. If you find a row that needs subsequent } processing you *do not* recursively call the same function, but execute } an insert statement that selects the rows into the temp table - } processing then continues. You repeat this process until no rows remain } in the temp table. Using your example the code would be something like } this... } } function recursive_call() } } let s = "select * from any_table ", } "where any_field = ? ", } "into temp fred with no log " } prepare cbIns from s } execute cbIns using any_value } } declare c_cursor as } select * from fred } } select count(*) into n from fred } } while n != 0 } foreach c_cursor into pr_any_table.* } if something_ok then } execute cbIns using pr_any_table.fld } else } call do_else() } end if } end foreach } } delete from fred } where current of c_cursor } } select count(*) into n from fred } end while } } free c_cursor } } end function } } The code need some tweaking for optimal performance e.g the count should } be in a prepared statement, but this technique might be suitable for what } you want. } } Paul Hatcher } Graduate Associates Ltd +44 81 840 3110 - Phone } phatcher@cix.compulink.co.uk +44 81 567 1894 - Fax The problem is you can not get what you want, if recursion is re-written into iteration. For example, a recursive call prints something in a format like begin_first_print ## 1st recursion begin_second_print ## 2nd recursion begin_third_print ## 3rd recursion ... end_third_print end_second_print end_first_print If changed to iteration, the result looks like begin_first_print ## 1st iteration end_first_print begin_second_print ## 2nd iteration end_second_print begin_third_print ## 3rd iteration end_third_print ... Furthermore, if function requires return value which is used in the function (most recursive functions do and that is the powerful point of the recursion), that means you can not finish first recursion until you get return value from second level of recursion, how could first iteration be finished and what value does first iteration generate? At leadt we know we can not get what we expect. A suggestion for Informix is to make cursor as a true variable. (But it does not now. It is a symbol now. Even though you define it as a variable, it still works like a symbol. That is what I think.) When cursor is declared, informix checks if it is a variable. If it is not, let it work like it does right now. If it is, use the value of the variable as cursor name to declare cursor. So we can pass cursor name as a parameter to recursive function and different cursors under the same variable in different recursions can be declared. When close the cursor, it only close the cursor declared in current recursion. This is only a suggestion. Theoretically, it should work. But we can not make it work, only Informix can. Tony Yang (714)771-0454 Data Systems Support yang@dssmktg.com; bangpath - uunet.uu.net!dssmktg!yang