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.
> 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
>
>
