[Pdx-pm] DBD::Oracle error
Christopher M. Hiller
chiller at eldorado.elsewhere.org
Mon Mar 17 17:35:53 CST 2003
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.
So I looked at the docs, and tried to make a call to
$sth->prepare_cached($sql, undef, 1)
(and likewise '2' for the third param) which just gives me the original
error I complained about! This is using the code with the $sth definition
inside of my subroutine.
Ugh, I have always tried to keep what I'm doing with DBI very
straightforward and I don't have that great of a grasp of what is *really*
going on behind the scenes...
Thanks,
Chris
On Mon, 17 Mar 2003, Ovid wrote:
> 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]);
> }
> }
More information about the Pdx-pm-list
mailing list