Nesting prepared DBI handles

MattLG has asked for the wisdom of the Perl Monks concerning the following question:

I'm a little bit confused about the way DBI works when I re-use a prepared statement handle inside a while loop. Here's what I mean (table contains a set of tree structure data where elements refer to a parent_id):

my $prepared_handle = $dbh->prepare('SELECT * FROM table WHERE parent_
+id = ?');

:
:
:
get_children(0);

sub get_children {
  my $parent_id = shift;

  $executed_handle = $prepared_handle->execute($parent_id);
  while (my $pointer = $executed_handle->fetchrow_hashref) {
    $results .= $pointer->{id}."\n";
    get_children($pointer->{parent_id});
  }
}
[download]

This sort of code doesn't seem to work because the nested call to get_children() appears to be overwriting the previous query. I'm getting:

Top level A
Top level B
  Second Level A
  Second Level B
[download]

and then it stops, instead of continuing like so:

Top level A
Top level B
  Second Level A
  Second Level B
Top level C
Top level D
[download]

Now this isn't a major problem, I can just re-prepare the $prepared_handle inside get_children() each time instead of using a globally prepared one, but this is less obviously efficient.

Is there an efficient way around this or is this just a limitation of the DBI interface?

(This is on mysql if it makes a difference)

Cheers
MattLG

Comment on Nesting prepared DBI handles Select or Download Code

Replies are listed 'Best First'.
Re: Nesting prepared DBI handles by WizardOfUz (Friar) on Nov 08, 2009 at 13:12 UTC
"Portable applications should not assume that a new statement can be prepared and/or executed while still fetching results from a previous statement." See the DBI documentation	[reply]
Re: Nesting prepared DBI handles by moritz (Cardinal) on Nov 08, 2009 at 13:20 UTC
This is probably not your problem here, but maybe worth mentioning anyway: DBI re-uses existing hashes and arrays for building its data structures, so when you store a hashref from a previous `fetchrow_hashref` without copying it you might get bad surprises.	[reply] [d/l]
Re^2: Nesting prepared DBI handles by WizardOfUz (Friar) on Nov 08, 2009 at 13:26 UTC
Not yet: "By default a reference to a new hash is returned for each row. It is likely that a future version of the DBI will support an attribute which will enable the same hash to be reused for each row. This will give a significant performance boost, but it won't be enabled by default because of the risk of breaking old code." Please take a look at the DBI documentation	[reply]
Re: Nesting prepared DBI handles by ruzam (Curate) on Nov 08, 2009 at 16:46 UTC
You're not just re-using the prepared statement, you're interrupting it in the middle of each use. The next execute() drops everything that was found and cached previously and loads up a fresh list ready to be fetched. There's only one prepare, so there's only one cache of results that can be fetched. The trick is to fetch all your results first before issuing the next execute. `sub get_children { my $parent_id = shift; $executed_handle = $prepared_handle->execute($parent_id); # first retrieve all the results my @results; while (my $pointer = $executed_handle->fetchrow_hashref) { push @results, $pointer->{parent_id}; } # now deal with the results foreach (@results) { $results .= $_."\n"; get_children($_); } }` [download] I'll also note that since you appear to be fetching only a single column (parent_id), then you could also skip the whole execute/fetch loop and select the results directly into an array ref. But I suspect your example may have been trimmed down for the purpose of the question, so this may not apply. `my $parent_id = shift; # first retrieve all the results my $results_ref = $dbh->selectcol_arrayref( $prepared_handle, undef, $parent_id); # now deal with the results foreach (@$results_ref) { $results .= $_."\n"; get_children($_); }` [download]	[reply] [d/l] [select]
Re: Nesting prepared DBI handles by CountZero (Bishop) on Nov 08, 2009 at 14:34 UTC
Have you tried making `$executed_handle` a lexical ("`my`") variable rather than a global variable? You should get a separate variable for each call into the `get_children` and it should not overwrite the existing variable (but who knows how DBI internally keeps its data?). Nothing is guaranteed but it is worth a try. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply] [d/l] [select]
Re^2: Nesting prepared DBI handles by MattLG (Beadle) on Nov 08, 2009 at 14:59 UTC
Sorry, that was a typo. $executed_handle is scoped to get_children() Cheers MattLG	[reply]
Re: Nesting prepared DBI handles by happy.barney (Friar) on Nov 09, 2009 at 11:29 UTC
it's quite uneffective to run inner select. You can also fall in "deep recursion" problem. Consider to use some tree implementation in SQL (for example, Joe Celko has book about that) this one solves only this problem. it will work only on trees small enough to fit in your RAM. `sub prepare_tree { my $sth = $dbh->prepare('SELECT * FROM table'); $sth->execute; my $retval = {}; while (my $hash = $sth->fetchrow_array) { push @{ $retval->{ $hash->{parent_id} } }, { %$hash }; } $sth->finish; $retval; }` [download]	[reply] [d/l]
Re: Nesting prepared DBI handles by bot403 (Beadle) on Nov 09, 2009 at 13:40 UTC
Do you have flexibility with your DB design? Have you considered using the nested set model for hierarchical data? This makes arbitrary nesting relationships a single query that performs well in SQL. It took me a day to fully understand it and a day to fully implement it in my code but the payoffs were huge in terms for coding headaches and performance.	[reply]