[Pdx-pm] DBD::Oracle error

[Ovid]

What it looks like to me is that the first example is quite slow because you're repreparing the
SQL every time.  The second example doesn't work (this is a guess) because the scope of the
statement handle is such that you're trying to prepare a statement handle that you haven't
finished.  I generally don't prepare statement handles inside of a loop, so I don't get such error
messages often.  To speed up your first example, try prepare_cashed().  If you pass it the same
arguments, it will fetch the statement handle from a cache rather than repreparing it.  That's
much faster.

Also, the ':p_cat' method of specifying a placeholder is less common and not very portable.  If
you can switch to '?' for a placeholder, I think people would find it easier to read:

Also, what are you returning?  This function doesn't appear to return anything (or I could just be
blind).

sub _walk {
  my $root = shift;
  my $sql = qq{
    SELECT prodcat_cd_for, prodcat_cd_in 
    FROM   prod_cat_cmpnts 
    WHERE  prodcat_cd_for = ? 
  };
  my $sth = $dbh->prepare_cached($sql);
  $sth->bind_param(1, $root);
  $sth->execute;

  while (my @row = $sth->fetchrow_array()) {
    print "$row[1]\n";
    _walk($row[1]);
  }
}

Cheers,
Ovid

--- "Christopher M. Hiller" <chiller at eldorado.elsewhere.org> wrote:
> Hi, I was wondering if someone here could give me a hand with this
> problem.  I have searched google, google groups and PerlMonks and have not
> found anything worthwhile.
> 
> Here is some working code, albeit really slow:
> 
> ---snip---
> 
> _walk($root);
> 
> sub _walk {
> 	my $root = shift;
> 	my $sql = qq{ SELECT prodcat_cd_for, prodcat_cd_in FROM
> prod_cat_cmpnts WHERE prodcat_cd_for = :p_cat };
> 	my $sth = $dbh->prepare($sql);
> 
> 	$sth->bind_param(':p_cat', $root);
>  	$sth->execute;
> 
> 	while (my @row = $sth->fetchrow_array()) {
> 		print "$row[1]\n";
> 		_walk($row[1]);
> 	}
> }
> 
> ---snip---
> 
> Here is what I'd like to do since I think it'd be quicker:
> 
> ---snip---
> 
> my $sql = qq{ SELECT prodcat_cd_for, prodcat_cd_in FROM prod_cat_cmpnts
> WHERE prodcat_cd_for = :p_cat };
> my $sth = $dbh->prepare($sql);
> 
> _walk($root);
> 
> sub _walk {
> 	my $root = shift;
> 
> 	$sth->bind_param(':p_cat', $root);
> 	$sth->execute;
> 
> 	while (my @row = $sth->fetchrow_array()) {
> 		print "$row[1]\n";
> 		_walk($row[1]);
> 	}
> }
> 
> ---snip---
> 
> However, Oracle returns the first row of the statement, then freaks:
> 
> DBD::Oracle::st fetchrow_array failed: ERROR no statement executing
> (perhaps you need to call execute first)
> 
> What am I doing wrong?  Do I need to prepare() when binding a new
> parameter every time?  Would this really make things quicker?  The first
> one is really quite slow...
> 
> Thanks for your help,
> Chris
> 
> _______________________________________________
> Pdx-pm-list mailing list
> Pdx-pm-list at mail.pm.org
> http://mail.pm.org/mailman/listinfo/pdx-pm-list


=====
"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