[prev in list] [next in list] [prev in thread] [next in thread] 

List:       dbi-users
Subject:    Re: Statement handle still Active: prepare_cached
From:       Mark Lawrence <nomad () null ! net>
Date:       2008-03-25 23:12:09
Message-ID: 20080325231209.GA12541 () lifebook ! rekudos ! net
[Download RAW message or body]

On Tue Mar 25, 2008 at 10:35:39AM +0000, Tim Bunce wrote:
> On Tue, Mar 25, 2008 at 09:31:30AM +0100, Mark Lawrence wrote:
> > I'm having a problem with one of my modules (SQL::DB) failing on
> > calls to prepare_cached. I'm asking here for help because this is
> > failing on every CPAN Tester's build, but I can't repeat the issue
> > locally (x86_64 Debian). The tests use SQLite for the database.
> > 
> > The error is occuring on INSERT and UPDATE statements:
> > 
> >   t/09-db...........prepare_cached(INSERT INTO
> >       sqldb (name, val)
> >   VALUES
> >       (?, ?)
> >   ) statement handle DBI::st=HASH(0xdf1cd8) still Active at ...
> > 
> > As far as I know I am not nesting calls to the same statement. But even
> > if I was, what I don't understand is how an INSERT or UPDATE statement
> > handle remains active after the database call. According to the pod:
> > 
> >    "Active" (boolean, read-only)
> > 
> >    The "Active" attribute is true if the handle object is "active". This
> >    is rarely used in applications. The exact meaning of active is somewhat
> >    vague at the moment. For a database handle it typically means that the
> >    handle is connected to a database ("$dbh->disconnect" sets "Active"
> >    off).  For a statement handle it typically means that the handle is a
> >    "SELECT" that may have more data to fetch. (Fetching all the data or
> >    calling "$sth->finish" sets "Active" off.)
> > 
> > I know about the $if_active option to prepare_cached, but I would rather
> > get the warnings and I don't want new statement handles added to the
> > cache for what I believe is the same statement.
> 
> You're right that INSERT or UPDATE statements shouldn't have Active set.
> I'd start by trying to understand how/why/where that's happening.

I have found out two bits of information which may be related:

1. I have been using a prepare_cached / bind_param / execute cycle on
INSERT / UPDATE / DELETE methods instead of do(), because I need to set
the parameter bind type - at least on INSERT / UPDATE. I suspect that the
$sth->execute() call is not finish()ing off the statement handle.
Perhaps because it assumes execute() is a SELECT call? I haven't looked
at the code to confirm this...

2. I was also assuming that the fetchrow_array/ref methods would know if
they had retrieved the last row, but that doesn't seem to be the case
until they actually try to fetch that non-existent row. So statements
with "LIMIT 1" followed by a fetchrow_array are not yet finished - you
have to make another fetchrow_array call or finish() the statement
handle manually.

At least I wasn't making recursive queries.

> > I have other question: The call to prepare_cached is happening
> > inside two eval{} blocks, yet the error message above doesn't come from
> > my code but instead from DBI.
> 
> I'm not quite sure what you're saying here, but the warning is generated
> by prepare_cached() in DBI.pm using carp().

Perhaps it is the Test::More infrastructure which is forcing the
warnings somehow.

Anyhow, version 0.12 is now on CPAN. Time to wait and see. Gotta love
those testers.

Mark.
-- 
Mark Lawrence
[prev in list] [next in list] [prev in thread] [next in thread]