[Pdx-pm] DBD::Oracle error

[Ovid]

--- "Christopher M. Hiller" <chiller at eldorado.elsewhere.org> wrote:
> Ovid
> 
> _walk() doesn't return anything, it just prints out a bunch of stuff--I
> trimmed a lot of it out, instead it prints a HTML heirarchy of the data
> using <ul>'s.
> 
> I like to use the ':foo' method for placeholders if  I end up using many
> placeholders (referring to them by # can be confusing) or if I want to use
> a placeholder more than once in my query.  But otherwise I would use ?, I
> guess I am just used to it...
> 
> I am trying to use prepare_cached() and get the following errors:
> 
> .prepare_cached( SELECT prodcat_cd_for, prodcat_cd_in FROM
> arestab.prod_cat_cmpnts WHERE prodcat_cd_for = :p_cat ) statement handle
> DBI::st=HASH(0x8260104) was still active at cat_tree.pl line 56
> DBD::Oracle::st fetchrow_array failed: ERROR no statement executing
> (perhaps you need to call execute first) at cat_tree.pl line 62.

Okay, I feel very, very stupid.  You should move the prepare outside of the recursive function.  I
just wasn't thinking.  Cache the results of your select and then iterate over them to get
subsequent results.  Warning:  you'll get a deep recursion error if prodcat_cd_in ever points back
to a prodcat_cd_for value.

my $sql = qq{
  SELECT prodcat_cd_for, prodcat_cd_in
  FROM   prod_cat_cmpnts
  WHERE  prodcat_cd_for = ?
};

my $sth = $dbh->prepare($sql);
_walk($sth, 1);

sub _walk {
  my ($sth,$root) = @_;
  $sth->execute($root);

  my @cd_in_vals;
  while (my @row = $sth->fetchrow_array()) {
    push @cd_in_vals => $row[1];
  }

  foreach my $new_root (@cd_in_vals) {
      print "$new_root\n";
      _walk($sth,$new_root);
  }
}


=====
"Ovid" on http://www.perlmonks.org/
Web Programming with Perl:  http://users.easystreet.com/ovid/cgi_course/
Silence Is Evil: http://users.easystreet.com/ovid/philosophy/decency.txt

__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com