Re^2: Recursive dbh executions

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

   ...
   $sth->execute($objid);
   my @rows = @{$sth->fetchall_arrayref()};
   $sth->finish();
   foreach $row (@rows) {
   ...
[download]

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".

Comment on Re^2: Recursive dbh executions Select or Download Code

Replies are listed 'Best First'.
Re^3: Recursive dbh executions by VSarkiss (Monsignor) on Mar 23, 2005 at 16:19 UTC
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.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^3: Recursive dbh executions
by VSarkiss (Monsignor) on Mar 23, 2005 at 16:19 UTC

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

for

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]

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.

[reply]
[d/l]
[select]