Your node title is correct, it's "recursive" you want, not "reentrant". That's a whole 'nuther thing.

The easiest way to fix it would be to pull everything from the query into an array, and then process that recursively. Something like this (untested):

$sth->execute($objid);
for my $row (@{$sth->fetchall_arrayref()})
{
    ($objid, $type) = @$row;
    # ..etc..
[download]

The statement handle is still open in your snippet, isn't it? If so, nothing was saved. The handle could be closed like this:

Read more... (344 Bytes)

But then one has to wonder why recursion is needed at all. We could reuse the prepared statement if we got rid of recursion:

Sub GetChildren{
   my ($objid) = @_;

   my $sth = $dbh->prepare(<<'   ;');
      SELECT objid, type
        FROM table
       WHERE parentid=?
   ;

   # Initial fetch.
   $sth->execute($objid);
   @rows = @{$sth->fetchall_arrayref()};

   while (@rows) {
      my ($objid, $type) = @{shift(@rows)};

      if ($type eq "Container") {
         # Add to todo list.
         $sth->execute($objid);
         push(@rows, @{$sth->fetchall_arrayref()});
         next;
      }

      do something else
   }

   $sth->finish();
}
[download]

Note: "=" was used to compare strings. "=" is the assignment operator. "==" is the numerical equivalency operator. "eq" is the string equivalency operator. "eq" is therefore the one needed to check if the type is "Container".

The statement handle is still open in your snippet, isn't it?

If I understand what I think you mean: No, putting it in a for means all the rows have been fetched before the loop body starts execution.

If you're really going to fix this, note that the statement doesn't change across calls, only the bound value. I'd rewrite it like this:

my $sth = $dbh->prepare(<<_EOSQL);
   select obj_id, type
   from   table
   where  parent_id = ?
_EOSQL

sub GetChildren
{
    my ($objid) = @_;
    for my $row (@{$dbh->selectall_arrayref, undef, $objid)})
    {
        if ($row->[1] eq 'Container')
        {
            GetChildren($row->[0]);
        }
        else
        {
            # whatever
        }
    }
}
[download]

But as to why the OP's using recursion: I agree with you that it seems unnecessary. It may even be fixable in the SQL, but not knowing enough about the problem and the database, I can't tell.

Your point about assignment and equality confusion is well-taken. Another reason to use warnings, which will complain about "assignment in conditional".

Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.

Alternately, if you're using a database that supports this (such as Oracle or Postgres), you can use a CONNECT BY clause to have the database do the parent-child relationships for you. Or, you can use other methods that simulate that type of behavior.

I agree. As has been noted, the problem is that inside that while, you are in the middle of reading the results coming back. If you think of it at the database level, if you were working in an SQL tool and typing queries to get results, you type one query, execute, and get all the rows back. There isn't a way in mid-result fetching to run another query.

There are ways to script things with more advanced SQL, but that can get hairy. Unless you have a memory limitation on your system, it's much easier and cleaner to pull in a full data structure, then work on that.

It's not quite that simple. Some DBD's, such as Oracle and MySQL, can have multiple statements executing at the same time. Others, such as Sybase / SQL*Server, cannot. But, most DBD's won't let you do the same statement twice at the same time. That's the issue here.

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

old reply follows...

Your problem isn't with the $sth; since $sth is a lexical variable, each recursive call to GetChildren will have it's own version of it.

I'm not sure if you can nest SQL queries on a single database handle, though.

I don't think your problem is what you think it is. I've never had any issue with multiple Selects running at the same time. Perhaps showing the error or explaining your problem would provide us with better insigh. If you don't truly need the recursion then you should look at the other solutions provided.

As you're creating a new statement handle, I wouldn't think it would be a problem. (that's not to say that there aren't any database that won't have a problem, but in the general sense of it, having multiple selects shouldn't be a problem). (trying to hold open a select cursor too long could be a problem if you're trying to ensure read consistency in your transaction, especially if you're performing a lot of writes in that same transaction)

I would make sure that I explicitly cleaned up after myself, though:

(yes, I know that Perl's garbage collection should take care of it when it goes out of scope, I'm just paranoid)

Also, if you're using Oracle, there's built in heirarchy handling, via CONNECT BY. It's possible that other databases might have ways of handling it, as well.

Yikes, you erased your question. PLEASE put it back, the whole point of a site like this is so people can learn from previous examples and if you erase the question, the responses to it become meaningless.