recursive retrieval

[Harry Singh]

hi! all, in one of my program i've certain data in parent child relationship. like parent A has child B and B has children C1, C2, and C3. And in my java program i'm retrieving this data using recursion.

public void child(String str ) throws Exception
{
ResultSet rs=s.executeQuery("select uid from tree where parent='"+str+"' ");
while(rs.next())
{
String abc= rs.getString(1);
System.out.println(abc);
child(abc);
}
}
while calling child("A") for the first time inside main i got the output like...
A
B
C1
Resultset is closed.
I want to print all the C1, C2, and C3 child nodes.
I've MSAccess at backend.
regards
--prateek

[Jamie Robertson]

not gonna work using the jdbc dbc bridge. The maximum number of statements you can have open per connection is 1. So upon entering every child node, you execute a new statement which will automatically close the previous resultset/statement. So once you start moving back up to the top of the recursion, none of the previous existing resultsets are open. Thus, the error "resultset is closed"
Solutions:
- have one connection per level of recursion so that every statement/resultset can use its own connection. This is an time expensive task, so it probably shouldn't be done dynamically.
- pass down an array of all the possible values so that you can close your resultset/statement and not include it the recursion. This could be memory intensive depending on the number of matches and the number of recursive calls made.
any other ideas?
Jamie

[Jamie Robertson]

passing down an arrayList is something like this:

public void child(ArrayList str ) 
for each element in str 
{
execute query using current ArrayList element
while(more results)
{
populate new ArrayList from results
}
close resultset and statement
call child(the new ArrayList);
}
}

I think that will also work...don't have time to double check it though

[This message has been edited by Jamie Robertson (edited November 28, 2001).]