DBD::DB2 execute and finish problem

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

Re: DBD::DB2 execute and finish problem

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

Re: DBD::DB2 execute and finish problem

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

request for clarification, was (DBD::DB2 execute and finish problem)

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

Re: request for clarification, was (DBD::DB2 execute and finish problem)

On Mon, 12 Jun 2006 15:24:55 +0100 (BST), Martin J. Evans wrote:

Hi Martin

>> This seems to fall into the category of the first quote from the

I saw the original post, and expected someone else to answer.

I don't agree that it falls in the first category. I strongly suspect you'll=

have to amend all (sic) your code, to call 'finish' /unless the 'fetch'=
exhausts
the returning data/. I think you're reading the docs based on wishful=
thinking
and not on what they really mean.
--
Cheers
Ron Savage, ron@savage.net.au on 13/06/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: request for clarification, was (DBD::DB2 execute and finish problem)

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

Re: request for clarification, was (DBD::DB2 execute and finish problem)

On 13-Jun-2006 Ron Savage wrote:
> On Mon, 12 Jun 2006 15:24:55 +0100 (BST), Martin J. Evans wrote:
>
> Hi Martin
>
>>> This seems to fall into the category of the first quote from the
>
> I saw the original post, and expected someone else to answer.
>
> I don't agree that it falls in the first category. I strongly suspect you'll
> have to amend all (sic) your code, to call 'finish' /unless the 'fetch'
> exhausts
> the returning data/. I think you're reading the docs based on wishful
> thinking
> and not on what they really mean.

Ok, so we disagree and my reasons are fairly straight forward.

1. The DBI docs clearly 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."

2. The code I have works with DBD::ODBC, DBD::mysql and DBD::oracle

3. When I look at DBD::ODBC the first thing dbd_st_execute() does is call
SQLFreeStmt(stmt, SQL_CLOSE) if the statement is active.

4. When I look at DBD::mysql the first thing it does is call
mysql_stmt_free_result if the statement is active.

As DB2 is CLI is ODBC:

I think DBD::DB2 could be fixed with something like:

if (DBIc_ACTIVE(imp_sth)) {

ret = SQLFreeStmt(imp_sth->phstmt, SQL_CLOSE);
ret = check_error( sth, ret, "SQLFreeStmt failed" );
DBIc_ACTIVE_off(imp_sth);
}

at the start of dbd_st_execute which is effectively just calling dbd_st_finish
at the start of execute and this is probably the best solution.

When I add the dbd_st_finish call it works and I cannot as yet see any side
effects other than making DBD::DB2 do what the DBI docs say.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com

Re: request for clarification, was (DBD::DB2 execute and finish problem)

On Tue, 13 Jun 2006 10:28:00 +0100 (BST), Martin J. Evans wrote:

Hi MArtin

> When I add the dbd_st_finish call it works and I cannot as yet see
> any side effects other than making DBD::DB2 do what the DBI docs
> say.

OK. Fair enough.

I was really trying to provoke you to reassess your assumptions (he, he,=
he),
and you reply makes clear you've looked into this thoroughly.

Looks like my interpretation was wrong and yours was right.
--
Cheers
Ron Savage, ron@savage.net.au on 13/06/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: request for clarification, was (DBD::DB2 execute and finish problem)

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