[Pdx-pm] DBD::Oracle error

Christopher M. Hiller chiller at eldorado.elsewhere.org
Mon Mar 17 17:35:53 CST 2003


_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...


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