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