Re: Building a tree recursively

In article <69od8u$2oj@cssun.mathcs.emory.edu>, "Ing. Melvin Perez Cedano" <cam@codetel.net.do> writes >This is a multi-part message in MIME format. >--------------CB15BB1AD6D7FF0F18F5BD13 >Content-Type: text/plain; charset=us-ascii >Content-Transfer-Encoding: 7bit > >Scott: > >You could use a prepared cursor, I mean >LET sqlstmt= "select child_no from unit_link where parent_no = ?" >PREPARE ex_sql FROM sqlstmt >DECLARE child CURSOR FOR ex_sql > >then you just use OPEN USING, without need to close-reopen the cursor. > >Anyway, this won't produce what you want, since the cursor are not >recursive. So, I have done this using an array in INFORMIX-4GL for store the >children records. > > Create a "cursor mangement" library:- DEFINE Cursors ARRAY[20] of RECORD used char(1) END RECORD FUNCTION init_cursors - set array to all "N" - not unused FUNCTION prepare_cursor(query_string) find an available cursor (used = "N") and set cursor_id to its position in the array set Cursors[cursor_id] to it to used = "Y" CASE WHEN cursor_id = 1 PREPARE Cursor01 FROM query_string WHEN cursor_id = 2 PREPARE Cursor02 FROM query_string ... END CASE RETURN cursor_id END FUNCTION FUNCTION open_cursor(cursor_id) CASE WHEN cursor_id = 1 OPEN Cursor01 WHEN cursor_id = 2 OPEN Cursor02 ... END CASE END FUNCTION same for fetch and close functions Then use DEFINE cursor_id integer CALL prepare_cursor("Select...") RETURNING cursor_id CALL open_cursor(cursor_id) ... CALL fetch_cursor(cursor_id) ... CALL close_cursor(cursor_id) A pain but you only have to write the library once.. > > >Scott Black wrote: > >> I know someone out there must have done this before... >> >> I have a table: >> unit_link( >> parent_no integer, >> child_no integer... >> >> Each child can have children of it's own, for as many levels as >> necessary. I tried to build a recursive function call to build the tree >> as follows: >> >> function get_children(unit_no) >> >> declare child cursor for >> select child_no >> from unit_link >> where parent_no = unit_no>> >> foreach child into this_unit >> call get_children(this_unit)... >> >> By now you see the problem. On the second time through I get error -400 >> (Fetch attempted on unopen cursor). Tech support says that this is the >> way it is designed, you cannot re-use a cursor without first closing it. >> It seems to me that each recursive call should be within it's own stack >> and that re-using variables, cursors and the like should not be a >> problem, but... >> >> Is there another way to go about this w/o recursion? What methods have >> any of you used to solve similar problems? >> >> TIA >> BTW this is HP-UX 10.20 OnLine 7.22 4GL 6.04 > > > >--------------CB15BB1AD6D7FF0F18F5BD13 >Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf" >Content-Transfer-Encoding: 7bit >Content-Description: Card for Melvin Perez >Content-Disposition: attachment; filename="vcard.vcf" > >begin: vcard >fn: Melvin Perez >n: Perez;Melvin >org: CAM Informatica. S. A. >email;internet: cam@codetel.net.do >title: Technical Vicepresident >tel;work: (809) 686-5574 >tel;fax: (809) 686-5467 >tel;home: (809) 227-2893 >x-mozilla-cpt: ;-960 >x-mozilla-html: FALSE >version: 2.1 >end: vcard > > >--------------CB15BB1AD6D7FF0F18F5BD13-- > -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care