WAG here: will the DBD::DB2 driver implicitly finish the resources if
you try calling $sth->fetchrow_array() again?
I don't know if DBD::DB2 can know whether there are more rows left in
the result set until you try fetching the next row, ergo it keeps the
statement handle active.
Dan
On 09/06/06, Martin J. Evans <[EMAIL PROTECTED]> wrote:
Hi,
I'm using DBD::DB2 0.78 and DBI 1.51. I am finding that code which is working
to DBD::ODBC and DBD::mysql fails with invalid cursor state but inserting a
call to finish makes it work. Up until now, I've never used finish because the
docs say:
"If execute() is called on a statement handle that's still active
($sth->{Active} is true) then it should effectively call finish() to tidy up
the previous execution results before starting this new execution."
and
"The finish method is rarely needed, and frequently overused, but can sometimes
be helpful in a few very specific situations to allow the server to free up
resources (such as sort buffers).
When all the data has been fetched from a SELECT statement, the driver should
automatically call finish for you. So you should not normally need to call it
explicitly except when you know that you've not fetched all the data from a
statement handle. The most common example is when you only want to fetch one
row, but in that case the selectrow_* methods are usually better anyway. Adding
calls to finish after each fetch loop is a common mistake, don't do it, it can
mask genuine problems like uncaught fetch errors."
An example is:
create table fred (a int not null primary key)
insert into fred values (1)
insert into fred values (2)
insert into fred values (3)
perl -w -e 'use DBI; my $dbh = DBI->connect("dbi:DB2:mydsn", "xxx",
"yyy"); my @a = (1,2,3); $sth = $dbh->prepare(q/select * from fred where a
= ?/); foreach my $a (@a) {$sth->execute($a); my @row = $sth->fetchrow_array;}'
which returns:
DBD::DB2::st execute failed: [IBM][CLI Driver] CLI0115E Invalid cursor state.
SQLSTATE=24000 at -e line 1.
This seems to fall into the category of the first quote from the docs which
suggest finish should be called for you. I don't want to add the finish if it
should not be required and this is a huge amount of code to work through
anyway. I know I could possible avoid the issue if I used selectall_* but here
again, I'd have to check a lot of code to make this change.
Is this a bug in DBD::DB2?
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
