Re: recursive functions and cursors

>From: cpg5484@swuts.sbc.com (Sivagurn Ramanathan) >Date: Tue, 13 Jun 95 9:58:41 CDT >X-Informix-List-Id: <list.6556> > >bruce@tkg.com wrote: >> >> I have a table that contains a hierarchy of items where where each record >> references another record as its parent. >> >> [...] >> >> I'm trying to use a cursor in a recursive function but keep running into >> an error. The program compiles ok but returns the following error when ran. >> >> $ fglgo prog.4go>> A >> B >> Program stopped at "prog.4gl", line number 16. >> SQL statement error number -400. >> Fetch attempted on unopen cursor. >> >> # prog.4gl >> 1 database mydata >> 2 >> 3 main >> 4 call showitem(A) >> 5 end main >> 6 >> 7 function showitem(itemid) >> 8 define itemid like tableA.itemid, >> 9 childid like tableA.itemid >> 10 >> 11 display itemid >> 12 >> 13 declare child_cur cursor for >> 14 select itemid from tableA where parent = itemid >> 15 >> 16 foreach child_cur into childid >> 17 call showitem(childid) >> 18 end foreach >> 19 >> 20 end function >> >> Does anyone have a suggestion? > > Folks correct me if I am wrong. > > I think 4GL as such does not support recursion. I4GL does support recursion. What doesn't support recursion is ESQL/C. When you are not using cursors, I4GL supports recursion in ordinary functions. Reports cannot be used recursively reliably, though there is nothing in the language or compilers to stop you doing: REPORT x(...) ... OUTPUT TO REPORT x(...) ... END REPORT However, in the code above, the function showitem() is called for a first time. It opens the cursor with the FOREACH loop, fetches a row of data and calls itself. This re-opens the cursor (doing a close and then an open), and, for sake of argument, reads 2 rows and then closes the cursor, and returns to the first invocation. The cursor to which the first invocation has been closed twice, now. So the next fetch returns the error, quite correctly. Remember, cursors are like global variables -- there is only one of them, even when the function is recursive. In fact, if you look at the generated C code, you'll see that the cursors are indeed represented by global (or file static) variables. > But there is a work around > for your problem which I think is about one of the ways to get around it. > > DO NOT use the foreach .... end foreach approach. Instead open the cursor > out side of the function showitem() and inside the function have a while > loop to fetch the rows. > [sample deleted] This would not work, I'm sorry to report. To get the child data, you have to re-open the cursor. And the code given doesn't re-open the cursor. The original code did a depth-first traversal of the tree. It is quite difficult to simulate this directly in SQL. You can simulate breadth-first traversal and stuff the data into a temporary table, and then retrieve the data from the temporary table. I attach a shell archive containing an SQL-based set of scripts which do various sorts of hierarchical data analysis -- but you should be aware that some of the techniques are not pretty but are effective. Also the file "alternatives" itself contains two shell archives where alternatives are discussed. Also, you can use stored procedures recursively to achieve the required effect, so it may be easiest to adapt the code above into a stored procedure, and then use that from I4GL. Note that if you have an upper bound on the depth of the hierarchy, you can write code to simulate an array of cursors and do the recursion manually. It is rather ghastly (British understatement for perfectly revolting) but will work until the upper bound is exceeded. Finally, the data in the example program will be returned in a indeterminate order because no sort criteria are specified on the SELECT statement. If the data happens to be inserted in order, it will work -- the test data probably was inserted in order -- but after a few random updates, deletes and insertions, it will produce the data in any old order. I hope this makes it through the mailers... Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> : "@(#)shar.sh 1.9" #! /bin/sh # # This is a shell archive. # Remove everything above this line and run sh on the resulting file. # If this archive is complete, you will see this message at the end: # "All files extracted" # # Created: Tue Jun 13 08:57:34 PDT 1995 by johnl at Informix Software Ltd. # Files archived in this archive: # README # Makefile # alternatives # bom.sql # hier.sql # hier1.ace # hier1.sh # hier2.ace # hier2.sh # partexp1.sh # partexp2.sh # partexp3.sh # partexp4.sh # pp.1.sh # pp.sql # #-------------------- if [ -f README -a "$1" != "-c" ] then echo shar: README already exists else echo 'x - README (2039 characters)' sed -e 's/^X//' >README <<'SHAR-EOF' XExamples in Hierarchical Data Structures X======================================== X XThe accompanying shell scripts, ACE report and SQL files can be used to Xdemonstrate how to handle hierarchical data structures (eg Bill of XMaterials or Organisation Chart) in SQL. X XThe Bill of Materials (BoM) solutions (partexp1..partexp4) illustrate Xthe use of Breadth-First-Search. The Organisation Chart (OC) solution X(hier2) illustrates Depth-First-Search. Note that neither solution is Xneat and tidy, though the BoM is simply iterative whereas the OC Xsolution requires a recursively defined table structure. X XThe solutions use my program SQLCMD as an SQL command interpreter. The Xequivalent effect can more or less be achieved using either ISQL or XDBACCESS, but it is not as easy, and some cases would require the output Xfrom ISQL/DBACCESS to be reformatted to retain the data one row per Xline. (Where the command is of the form sqlcmd -d dbase -e "SQL stmt", Xuse echo "SQL stmt" | isql dbase -; where the command is of the form Xsqlcmd -d dbase -f file.sql, use isql dbase file; where the command is Xof the form sqlcmd -d dbase file1.sql file2.sql, use cat file1.sql Xfile2.sql | isql dbase -; when -D is used, set DBDELIMITER to the Xargument value. When -F unload is used, change the last SELECT Xstatement into an unload statement.) Contact me for source to SQLCMD. X XThe Makefile has the targets all, dbase and runit. All makes the Xrequisite files, dbase creates and loads the database, and runit runs Xthe various programs. A sample output is included called runit.log. X XNote that both sets of data have random number sequences. The OC code Xwas initially developed with a neat and tidy (non-random) sequence for Xthe identifiers, and a neat and tidy solution was the result. When the Xnumbers were randomised, that solution fell to bits, so the current Xhier2 solution was developed. Beware when adapting the code of this Xpotential problem; simple sample data may easily mislead you. X XJonathan Leffer XInformix Software X@(#)README 1.1 93/03/03 S