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

In reply to DBI prepare and function recursion by simon.proctor

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.