[Pdx-pm] DBD::Oracle error
Ovid
poec at yahoo.com
Mon Mar 17 17:21:24 CST 2003
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
More information about the Pdx-pm-list
mailing list