When doing the same (well, almost) query and again, you do want to reuse the statement handle for several reasons. Take a look at the DBI documentation if you want to know why. Anyhow, the basic idea would look like this:
#this is an exsample and will not run right away!
use strict;
use DBI;
use vars qw#$DBH $STH#;
my $dsn = "foo";
my $user = "bar";
my $passwd = "baz";
$DBH = DBI->connect ($dsn, $user, $passwd) or die DBI->errstr ();
$STH = $DBH->prepare ("SELECT name, parent FROM table WHERE id = ?") o
+r die $DBH->errstr ();
my $id = int (rand (100));
my @list = check_id ($id);
print STDOUT join (', ', @list) . "\n";
sub check_id
{
my $id = shift;
$STH->execute ($id) or die $STH->errstr ();
my ($name, $parent) = $STH->fetchrow_array ();
$STH->finish ();
# !!! local scope !!!
my @list = ($name);
if ($parent)
{
push (@list, check_id ($parent);
}
return (@list);
}
This code should do the magic.. Please make sure that you realize that @list in the subroutine is NOT identical with @list in the main program. Also take a look at the statement handle which is initialized only once (using the placeholder "?").
Regards,
-octo