On 13-Jun-2006 Tim Bunce wrote:
> On Mon, Jun 12, 2006 at 03:24:55PM +0100, Martin J. Evans wrote:
>> Hi,
>>
>> DBI documentation says execute on a an active statement should imply a
>> finish
>> call but DBD::DB2 does no appear to do this - see example below.
>>
>> This issue is now causing me a severe amount of grief as we have no finish
>> calls anywhere in our code and we are finding more and more cases where
>> finish
>> would have to be called when using DBD::DB2 but not for any other DBD we
>> use.
>> This is a significant incompatibility between DBD::DB2 and other DBDs (like
>> O
>> DBC and mysql and Oracle). Can someone please clarify if this is a DBD::DB2
>> bug
>> or a DBI bug in the documentation or something else.
>
> Assuming your description of the problem is accurate then it sure seems
> like a DBD::DB2 bug. The sequence execute, fetch one row, execute,
> shouldn't cause a problem.
>
> Assuming that execute not calling finish is the underlying issue, then
> you might be able to work around it with something along these lines:
>
> *DBD::DB2::st::execute_orig = \&DBD::DB2::st::execute;
> *DBD::DB2::st::execute = sub {
> my ($sth, @args) = @_;
> $sth->finish if $sth->{Active};
> return DBD::DB2::st::execute_orig(@_);
> };
>
> Tim.
Thanks, I didn't think about doing that. See other email, I added a
dbd_st_finish to dbd_st_execute in DBD::DB2 and that effectively did the same
as above.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
>
>> Thank you.
>>
>> Martin
>> --
>> Martin J. Evans
>> Easysoft Ltd, UK
>> http://www.easysoft.com
>>
>>
>> On 09-Jun-2006 Martin J. Evans 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
>>
>>
