RE: Recursive Cursor

> Article: 6006 of comp.databases.informix > Path: > cix.compulink.co.uk!uknet!pipex!howland.reston.ans.net!cs.utexas.edu!swr > inde!emory!emory!not-for-mail > From: yang@dssmktg.com (Yang Tony) > Newsgroups: comp.databases.informix > Subject: Recursive Cursor > Date: 28 Feb 1994 22:38:08 -0500 > Organization: Mailing List Gateway > Lines: 39 > Sender: walt@mathcs.emory.edu > Distribution: world > Message-ID: <2kudb0INN1gg@emory.mathcs.emory.edu> > Reply-To: yang@dssmktg.com (Yang Tony) > NNTP-Posting-Host: emory.mathcs.emory.edu > X-Informix-List-ID: <list.3597> > > Hi, > > Does anyone know that there is a work around for recursive function > call? > 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 close 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"). If anyone has > workaround, > would you please let me share? > > If Informix reads this mail and can not give us a solution, please add > it > in later version. > > My environment is 4GL version 4.10.UC3 > SQL version 4.10.UD2 > on SCO UNIX 3.2.2 > > > Tony Yang (714)771-0454 > Data Systems Support > yang@dssmktg.com; bangpath - uunet.uu.net!dssmktg!yang 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