On 09-Jun-2006 Dan Scott wrote:
> WAG here: will the DBD::DB2 driver implicitly finish the resources if
> you try calling $sth->fetchrow_array() again?
Yes.
> 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.
Yes, but the docs say that if execute is called again then the next execute on
an active statement implicitly calls finish and that is what happens in other
drivers - just not DBD::DB2.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
> 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
>>
>>