Re: recursive functions and cursors
bruce@tkg.com wrote:
>
> I have a table that contains a hiearchy of items where where each record
> references another record as its parent.
>
> tableA
> itemid parent
> A A
> B A
> C A
> D B
> E B
> F C
> G C
>
> I would like to print the relationships out in the form of a tree.
>
> A
> |--B
> | |--D
> | \\--E
> |
> \\--C
> |--F
> \\--G
>
> 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?
>
> Many thanks in advance,
>
> Bruce Bramhall
> bruce@tkg.com
>
>
Folks correct me if I am wrong.
I think 4GL as such does not support recursion. 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.
In effect you would have to use
DATABASE mydata
MAIN
.
.
.
OPEN cursor
CALL showitem(..)
CLOSE cursor
.
.
.
END MAIN
FUNCTION showitem(..)
WHILE true
FETCH cursor INTO something.*
IF status OR SQLCA.SQLCODE = 100
THEN
HANDLE errors/exceptions
EXIT WHILE
END IF
.
.
.
HANDLE recursive logic here...
.
.
.
END WHILE
END FUNCTION
You probably might have to have some flag values to be defined and
monitored to properly exit out of the while loop.
I think the book 4GL By Examples does have a parts explosion logic
which would be quite useful for your specific need.
Hope this helps.
-- Siva.
==============================================================================
Ramanathan Sivaguru | 25-N-5 One Bell Center W: (314)235-0954
Software Consultant | Southwestern Bell Telephone H: (314)394-8633
Arbor Consulting Resources | St.Louis MO 63105.
7777 Bon Homme Ave. Ste #1801|
Clayton MO 63105. | E-mail: cpg5484@swuts.sbc.com
==============================================================================