[SOLVED] Traverse database analogous to preorder traversal of graph

[coolFrenzi]

Hello,

I have a database table with 2 columns, say child and parent, depicting the parent-child relationship in a k-ary graph. Hence a tuple in child is "is-a-child-of" of the corresponding parent tuple.
Initially, I supply a parent tuple "p" to my algorithm which needs to trace out all the child of "p" and the chid of its child and so on in the hierarchy of "p".
I have tried to do it recursively (sth like preorder traversal of binary tree) but it doesn't work.
Any idea would be highly appreciated.

With the following code, I get the exception: "Resultset is closed"
Example:
Child Parent
B A
C E
D A
E A
F C
G A
H E
I B
J Y
K C
L Y

Initially, p=A

Output:
A
B
I
D
E
C
F
H
G

 public void recurseChild(String id, java.sql.Connection conn)
 {

  try 
  {
   ResultSet rs;
   Statement stmt = conn.createStatement();
   String cmd="select conceptid1 from Relationship where conceptid2=" + id;
   
    if(stmt.execute(cmd)) 
    {
     rs=stmt.getResultSet();
     
     while(rs.next()) 
     {
        String ss=rs.getString(1);
        System.out.println(ss);
        recurseChild(ss, conn);
       
     }
     
    }  //end if
    
  } //end try
  
  catch(SQLException s) 
  {
   System.out.println(s);
  } 
   
 }//end recursiveChild()

[coolFrenzi]

The formatting of child and parent example seems to have been lost so please understand that first column refers to child and second column to parent.

Even in the output, A is the starting point. B, D, E and G are its children. I is a child of B. C and H are children of E. F is a child of C. They are traced in the order given.

[OrangeDog]

OK, I'll try and explain why this is happening.

The SQL connection will only return one result set at a time, so every time you query it, the previous set is closed. As you are doing a pre-order traversal, only the first record is read before you recurse and make another query. When the inner method returns, the original result set has been closed. To avoid this, you need to copy the entire result set (and close it for clarity) before you make any recursive calls, probably into a FIFO queue.

I think that is the problem, but even if it isn't that solution should solve it.

[coolFrenzi]

Thanks. Using LIFO queue works (and not FIFO).

[OrangeDog]

Ah, I always get them the wrong way round until I notice I'm traversing the wrong way.