Newbie: Statement handle inactive after successful execute of SELECT
From: Alan M. Carroll (amc_at_thought-mesh.net)
Date: 11/04/03
- Previous message: Hardy Merrill: "Re: Escaping single quote in DBI::ODBC"
- Next in thread: Michael A Chase: "Re: Newbie: Statement handle inactive after successful execute of SELECT"
- Reply: Michael A Chase: "Re: Newbie: Statement handle inactive after successful execute of SELECT"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
To: dbi-users@perl.org Date: Tue, 4 Nov 2003 14:24:35 -0600
I'm working with a problem in Movable Type (a PERL web application) where
certain operations fail. One place where it fails is in some code to create
an iterator which walks a set of rows in a MySQL 3.23.58 DB using DBI 1.21.
The code looks like this:
my $sth = $dbh->prepare($sql) or return sub { };
$sth->execute(@$bind) or return sub { };
$sth->bind_columns(undef, @bind);
What happens is the "bind_columns" fails with "Statement has no result
columns to bind". I've put in some additional debugging code after the
execute and before the bind_columns. Both "$sth->err" and "$sth->errstr" are
undefined. The statement is *not* active. NUM_OF_FIELDS is zero. The execute
returned "-1". The text of the statement (the contents of $sql) are
select entry_id, entry_blog_id, entry_status, entry_author_id,
entry_allow_comments, entry_title, entry_excerpt, entry_text,
entry_text_more, entry_convert_breaks, entry_to_ping_urls,
entry_pinged_urls, entry_allow_pings, entry_keywords, entry_tangent_cache,
entry_category_id, entry_created_on, entry_created_by, entry_modified_on,
entry_modified_by
from mt_entry
where (entry_status = ?)
order by entry_created_on desc
The entry_status has been bound to "2" (this was presumably done correctly
or the execute would have failed, and I've verified that @$bind is correct
via my debugging code). If I use PHPMyAdmin to do this directly it works
(although PHPMyAdmin puts a LIMIT on the statement which may have an
effect).
This exact same code on the same host works correctly on a different
database instance in the same MySQL process. Sometimes the code works on the
problem database in other situations although I don't have any good criteria
for distinguishing the working from non-working cases. As best I can tell,
there are certain rows which, if they are in the result set, cause this
failure. OTOH it may be size related (in this failure case, there are 16,792
rows, while in the non-problem DB there are about 2800 rows). I've used all
of the MySQL table repair options, none of which indicated any error in the
tables.
My question is, what can I look at to discover more information? I don't
have access to the MySQL logs. I tried "DBI->trace(4)" but it had no visible
effect. As far as I can tell from reading the documentation, this situation
should be impossible, i.e. if the execute succeeds on a SELECT then the
statement should be Active, even if no rows were selected (although in this
case it should select all of the rows). Has anyone else seen this? What
might cause that to happen? The statement handle isn't being re-used, it's
created on the spot from the prepare. Are there any other methods I can call
on the database or statement handle to get additional diagnostic
information? If anyone has a handy DBI info script I can run I'd be happy to
do that. Thanks.
- Previous message: Hardy Merrill: "Re: Escaping single quote in DBI::ODBC"
- Next in thread: Michael A Chase: "Re: Newbie: Statement handle inactive after successful execute of SELECT"
- Reply: Michael A Chase: "Re: Newbie: Statement handle inactive after successful execute of SELECT"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
|
