DBI prepare and function recursion

simon.proctor has asked for the wisdom of the Perl Monks concerning the following question:

I'm working on a hierachal table where I have:

TABLE_STUFF
~~~~~~~~~~~~~~~~~~~~
ID (pk)
name
parent_ID
[download]

As part of the project, I'm implementing a nested set update methodology so the table looks like:

TABLE_STUFF
~~~~~~~~~~~~~~~~~~~~
ID (pk)
name
parent_ID
lft
rght
depth
lineage
[download]

I won't go into the details of nested set over adjacency model except one is good for updates and one for queries.

My code to update the table when an entry is added or removed follows:

sub rebuild_tree
{
    my $dbh = shift;
    my $dir_ID = shift;
    my $left = shift;

    my $right = $left + 1;

    my $sql = "select ID, name from DIRECTORIES where parent_ID = ?";
    my $sth = $dbh->prepare($sql);

    $sth->execute($dir_ID) || return $right + 1;

    my $row;
    while($row = $sth->fetchrow_arrayref)
    {
        #print STDERR "Processing: ", $row->[0], " with name: ", $row-
+>[1],"\n";
        $right = rebuild_tree($dbh, $row->[0], $right);
    }

    my $sql2 = "update DIRECTORIES set lft = ?, rght = ? where ID = ?"
+;
    my $sth2 = $dbh->prepare($sql2);
    $sth2->execute($left, $right, $dir_ID);
    return $right + 1;
}
[download]

This code does work but I want to save my prepares across all calls to the function. I have tried using vars, ie:

use vars qw($sth);
....

# And then in the function
$sth || = $dbh->prepare($sql);
[download]

But the function only goes so deep before I get the following error:

DBD::mysql::st fetchrow_arrayref failed: fetch() without execute()
[download]

I assume this is because I am iterating over a statement handler and not creating a new one in each new function? If so, how can I take advantage of my prepare?

Sorry if this is a lame question but recursion always stumps me ;).

Ta, SP

Comment on DBI prepare and function recursion Select or Download Code

Replies are listed 'Best First'.
Re: DBI prepare and function recursion by gmax (Abbot) on Oct 07, 2003 at 10:34 UTC
Move the statement handlers outside the recursive function. Then, instead of passing a dbh, you pass the properly initialized statement handlers; In addition, you should change the fetch method, so you are sure that the recursed sth is not executing a nested operation while still handling the outer one. This is untested but it should get you on track. sub rebuild_tree { my $sth1 = shift; my $sth2 = shift; my $dir_ID = shift; my $left = shift; my $right = $left + 1; $sth1->execute($dir_ID) \|\| return $right + 1; my $recs = $sth1->fetchall_arrayref(); for my $row (@$recs) { $right = rebuild_tree($sth1,$sth2, $row->[0], $right); } $sth2->execute($left, $right, $dir_ID); return $right + 1; } my $sql1 = "select ID, name from DIRECTORIES where parent_ID = ?"; my $sth1 = $dbh->prepare($sql1); my $sql2 = "update DIRECTORIES set lft = ?, rght = ? where ID = ?"; my $sth2 = $dbh->prepare($sql2); my ($dir_ID, $left) = (); # whatever you need my $right = rebuild_tree($sth1, $sth2, $dir_ID, $left ); [download] Update I remember now that have seen this approach before in one of Joe Celko's tricks in this article, which uses stored procedures to update the tree. _ _ _ _ (_\|\| \| \|(_\|>< _\|	[reply] [d/l]
Re: DBI prepare and function recursion by Abigail-II (Bishop) on Oct 07, 2003 at 10:36 UTC
The problem is in your recursion. What happens is: You enter the function rebuild_tree. You create a statement handler, using prepare. You call execute on it. You fetch once. You go into recursion. You call execute on it. You fetch once. You go into recursion, etc. If there's nothing more to fetch, you return from recursion. And then you fetch again, due to the while. And it's this fetch that causes the problem. The corresponding execute has been cancelled because of the execute you did in recursion. Abigail	[reply]
Re: DBI prepare and function recursion by dragonchild (Archbishop) on Oct 07, 2003 at 14:13 UTC
You don't specify which database you're using. Some database (like Oracle) have the CONNECT BY feature, which handles flattened parent-child relationships in the same table. If you can, you might want to look at that. ------ We are the carpenters and bricklayers of the Information Age. The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6 Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.	[reply]
Re: Re: DBI prepare and function recursion by simon.proctor (Vicar) on Oct 07, 2003 at 15:50 UTC
I'm using MySQL. This is for Net::FTPServer so that when a directory is created/deleted I can recreate the nested set listings as well as the lineage. For serving the files, I'm using the lineage field so that I can do fast lookups to determine if a file exists on the server. In other words the path ends with a file, remove that and I have a lineage which I can do a lookup on. If I find that lineage then I get the directory ID and find all files in that folder that match my filename. If I don't find it - 404, if I do I continue processing. A little convoluted but its WIP atm. The concept is that dirs won't be created often but files will. If it moves to anything, it will be SQL Server.	[reply]
Re: DBI prepare and function recursion by adrianh (Chancellor) on Oct 07, 2003 at 12:47 UTC
Is there any reason why you can't use the `prepare_cached` provided by DBI?	[reply] [d/l]