Bug #68720 for DBD-ODBC: Bug from SVN r14651->r14665

Thu Jun 09 12:25:52 2011 pedrinho [...] gmail.com - Ticket created

Subject:	Bug from SVN r14651->r14665
Date:	Thu, 9 Jun 2011 12:25:39 -0400
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	"Peter C. Norton" <pedrinho [...] gmail.com>

The change between r14651 and r14665 breaks the usually allowed case of doing a fetch on a handle that's got no data to return (e.g. doing the following: $dbh->selectall_arrayref("create table #foo (fooint int)");). The error looks like this: $ perl ~/tmp/fetch_with_no_info.pl ODBCError: $VAR1 = [ 'DBD::ODBC::db selectall_arrayref failed: Unable to fetch information about the error', bless( {}, 'DBI::db' ), [] ]; -Peter

Thu Jun 09 14:15:04 2011 bohica [...] ntlworld.com - Correspondence added

On Thu Jun 09 12:25:52 2011, pedrinho@gmail.com wrote: Show quoted text

> The change between r14651 and r14665 breaks the usually allowed case of > doing a fetch on a handle that's got no data to return (e.g. doing the > following: $dbh->selectall_arrayref("create table #foo (fooint int)");). > > The error looks like this: > > $ perl ~/tmp/fetch_with_no_info.pl > ODBCError: $VAR1 = [ > 'DBD::ODBC::db selectall_arrayref failed: Unable to fetch > information about the error', > bless( {}, 'DBI::db' ), > [] > ]; > > > -Peter

Thanks Peter. I'm assuming you mean r14651 works and r14665 fails. I'll look in to it. Thanks for narrowing it down so tightly. I must admit though that I was surprised anyone was doing a create table with selectall as the DBI says do is for non-result-set generating statements. Martin -- Martin J. Evans Wetherby, UK

Thu Jun 09 14:15:07 2011 The RT System itself - Status changed from 'new' to 'open'

Thu Jun 09 14:20:52 2011 bohica [...] ntlworld.com - Correspondence added

On Thu Jun 09 14:15:04 2011, MJEVANS wrote: Show quoted text

> On Thu Jun 09 12:25:52 2011, pedrinho@gmail.com wrote:

> > The change between r14651 and r14665 breaks the usually allowed case of > > doing a fetch on a handle that's got no data to return (e.g. doing the > > following: $dbh->selectall_arrayref("create table #foo (fooint int)");). > > > > The error looks like this: > > > > $ perl ~/tmp/fetch_with_no_info.pl > > ODBCError: $VAR1 = [ > > 'DBD::ODBC::db selectall_arrayref failed: Unable to fetch > > information about the error', > > bless( {}, 'DBI::db' ), > > [] > > ]; > > > > > > -Peter

> > Thanks Peter. I'm assuming you mean r14651 works and r14665 fails. I'll > look in to it. Thanks for narrowing it down so tightly. > > I must admit though that I was surprised anyone was doing a create table > with selectall as the DBI says do is for non-result-set generating > statements. > > Martin

ok, found it. Thanks again. I'll try and fix it for 1.30_7. Martin -- Martin J. Evans Wetherby, UK

Thu Jun 09 14:39:52 2011 pedrinho [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #68720] Bug from SVN r14651->r14665
Date:	Thu, 9 Jun 2011 14:39:43 -0400
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	"Peter C. Norton" <pedrinho [...] gmail.com>

I was as surprised as you are, however it appears that DBD::Sybase is lenient with what it will allow in do(), and with the fetch() under the covers of select*array*() and I'd venture a guess that most other established drivers are the same way. So I guess that's a de-fecto standard. -Peter On Thu, Jun 9, 2011 at 2:20 PM, Martin J Evans via RT < bug-DBD-ODBC@rt.cpan.org> wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=68720 > > > On Thu Jun 09 14:15:04 2011, MJEVANS wrote:

> > On Thu Jun 09 12:25:52 2011, pedrinho@gmail.com wrote:

> > > The change between r14651 and r14665 breaks the usually allowed case of > > > doing a fetch on a handle that's got no data to return (e.g. doing the > > > following: $dbh->selectall_arrayref("create table #foo (fooint

> int)");).

> > > > > > The error looks like this: > > > > > > $ perl ~/tmp/fetch_with_no_info.pl > > > ODBCError: $VAR1 = [ > > > 'DBD::ODBC::db selectall_arrayref failed: Unable to fetch > > > information about the error', > > > bless( {}, 'DBI::db' ), > > > [] > > > ]; > > > > > > > > > -Peter

> > > > Thanks Peter. I'm assuming you mean r14651 works and r14665 fails. I'll > > look in to it. Thanks for narrowing it down so tightly. > > > > I must admit though that I was surprised anyone was doing a create table > > with selectall as the DBI says do is for non-result-set generating > > statements. > > > > Martin

> > ok, found it. Thanks again. I'll try and fix it for 1.30_7. > > Martin > -- > Martin J. Evans > Wetherby, UK >

Fri Jun 10 13:23:10 2011 bohica [...] ntlworld.com - Correspondence added

On Thu Jun 09 14:39:52 2011, pedrinho@gmail.com wrote: Show quoted text

> I was as surprised as you are, however it appears that DBD::Sybase is > lenient with what it will allow in do(), and with the fetch() under > the > covers of select*array*() and I'd venture a guess that most other > established drivers are the same way. So I guess that's a de-fecto > standard. > > -Peter

See http://www.mail-archive.com/dbi-dev@perl.org/msg06359.html I posted to dbi-dev and so far I'm afraid the answers all say you shouldn't use selectall_* for non-select statements or statements not returning any data. A bug introduced some time ago meant that the error you should have got which is "no select statement currently executing" was not shown. I have just fixed that and will make it available in 1.30_7 soon. Martin -- Martin J. Evans Wetherby, UK

Mon Jun 13 11:31:25 2011 pedrinho [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #68720] Bug from SVN r14651->r14665
Date:	Mon, 13 Jun 2011 11:31:14 -0400
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	"Peter C. Norton" <pedrinho [...] gmail.com>

Based on the responses do dbi-dev, I've got a further question: I'm not 100% sure about the assertion about calling fetch on an executed non-select statement handle being defined to reliably return false, as in no-more-data as part of the standard. If C<fetch*> isn't really defined to behave the same with a non-SELECT as with a SELECT that simply doesn't return any rows, like I think it is but not with certainty, the following is my submission as a patch to DBI.pm. --- a/DBI.pm Sat Jun 11 16:19:30 2011 -0500 +++ b/DBI.pm Sat Jun 11 16:38:01 2011 -0500 @@ -4621,6 +4621,16 @@ You can specify a maximum number of rows to fetch by including a 'C<MaxRows>' attribute in \%attr. +=head3 On use of non-C<SELECT> SQL in C<select*_*> methods + +While some drivers support statements other than C<SELECT> in the above-listed +convenience functions, others do not. Requirement of this facility is not defined +by the DBI interface standard. The C<do> method is provided for non-C<SELECT> +statements. When you really don't know if the statement you have in a variable +is going to be a C<SELECT> or not, unrolling the process into C<prepare>,C<execute>, +and some C<fetch> variant will always work, as C<fetch> is defined to return +no data when called on executed non-C<SELECT> statement handles. + While it says that fetch() is defined to return no data, it doesn't appear to define whether an error/warning will be issued in this case. I thought I'd tested calling fetch() 2x and got the same message (which isn't to say that the statement failed, I didn't even check for that). How do you expect prepare();execute();fetch() to work in 1.30_07 vs. fetchall_arrayref()? Thanks, -Peter On Fri, Jun 10, 2011 at 1:23 PM, Martin J Evans via RT < bug-DBD-ODBC@rt.cpan.org> wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=68720 > > > On Thu Jun 09 14:39:52 2011, pedrinho@gmail.com wrote:

> > I was as surprised as you are, however it appears that DBD::Sybase is > > lenient with what it will allow in do(), and with the fetch() under > > the > > covers of select*array*() and I'd venture a guess that most other > > established drivers are the same way. So I guess that's a de-fecto > > standard. > > > > -Peter

> > See http://www.mail-archive.com/dbi-dev@perl.org/msg06359.html > > I posted to dbi-dev and so far I'm afraid the answers all say you > shouldn't use selectall_* for non-select statements or statements not > returning any data. > > A bug introduced some time ago meant that the error you should have got > which is "no select statement currently executing" was not shown. I have > just fixed that and will make it available in 1.30_7 soon. > > Martin > -- > Martin J. Evans > Wetherby, UK >

Mon Jun 13 15:36:13 2011 bohica [...] ntlworld.com - Correspondence added

On Mon Jun 13 11:31:25 2011, pedrinho@gmail.com wrote: Show quoted text

> Based on the responses do dbi-dev, I've got a further question: > > I'm not 100% sure about the assertion about calling fetch on an > executed non-select statement handle being defined to reliably return > false, as in no-more-data as part of the standard. If C<fetch*> isn't > really defined to behave the same with a non-SELECT as with a SELECT > that simply doesn't return any rows, like I think it is but not with > certainty, the following is my submission as a patch to DBI.pm. > > --- a/DBI.pm Sat Jun 11 16:19:30 2011 -0500 > +++ b/DBI.pm Sat Jun 11 16:38:01 2011 -0500 > @@ -4621,6 +4621,16 @@ > You can specify a maximum number of rows to fetch by including a > 'C<MaxRows>' attribute in \%attr. > +=head3 On use of non-C<SELECT> SQL in C<select*_*> methods > + > +While some drivers support statements other than C<SELECT> in the > above-listed > +convenience functions, others do not. Requirement of this facility is > not defined > +by the DBI interface standard. The C<do> method is provided for > non-C<SELECT> > +statements. When you really don't know if the statement you have in a > variable > +is going to be a C<SELECT> or not, unrolling the process into > C<prepare>,C<execute>, > +and some C<fetch> variant will always work, as C<fetch> is defined to > return > +no data when called on executed non-C<SELECT> statement handles. > +

Show quoted text

> While it says that fetch() is defined to return no data, it doesn't appear > to define whether an error/warning will be issued in this case. I thought > I'd tested calling fetch() 2x and got the same message (which isn't to say > that the statement failed, I didn't even check for that). How do you

expect Show quoted text

> prepare();execute();fetch() to work in 1.30_07 vs. fetchall_arrayref()? > > Thanks, > > -Peter

If you run the code you were running through selectall_* or fetchall_* in DBD::ODBC via the latest version from subversion trunk you'll get an error saying no select statement is running. It is my belief that this is what you should get (and you would have got in older DBD::ODBCs until I broke this aspect of error reporting) and that is what other DBDs do. It is also the belief of the commentators on the issue I raised. I cannot move this on - only DBI developers can. I had intended releasing 1.30_7 this weekend but I was away. I'll try to sort it out this week. I cannot unilaterally change DBI and this issue is going beyond DBD::ODBC. I've mentioned it before but you'd get a much wider audience on dbi-users mailing list (see dbi.perl.org) and much more likely to get a definitive answer or resolution. Martin -- Martin J. Evans Wetherby, UK

Mon Jun 13 16:22:02 2011 pedrinho [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #68720] Bug from SVN r14651->r14665
Date:	Mon, 13 Jun 2011 16:21:53 -0400
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	"Peter C. Norton" <pedrinho [...] gmail.com>

I'm OK with the change/restriction, I just don't understand where the line is drawn regarding the fetch(). Will a fetch() succeed in this case, perhaps with an empty list? Thanks -Peter On Mon, Jun 13, 2011 at 3:36 PM, Martin J Evans via RT < bug-DBD-ODBC@rt.cpan.org> wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=68720 > > > On Mon Jun 13 11:31:25 2011, pedrinho@gmail.com wrote:

> > Based on the responses do dbi-dev, I've got a further question: > > > > I'm not 100% sure about the assertion about calling fetch on an > > executed non-select statement handle being defined to reliably return > > false, as in no-more-data as part of the standard. If C<fetch*> isn't > > really defined to behave the same with a non-SELECT as with a SELECT > > that simply doesn't return any rows, like I think it is but not with > > certainty, the following is my submission as a patch to DBI.pm. > > > > --- a/DBI.pm Sat Jun 11 16:19:30 2011 -0500 > > +++ b/DBI.pm Sat Jun 11 16:38:01 2011 -0500 > > @@ -4621,6 +4621,16 @@ > > You can specify a maximum number of rows to fetch by including a > > 'C<MaxRows>' attribute in \%attr. > > +=head3 On use of non-C<SELECT> SQL in C<select*_*> methods > > + > > +While some drivers support statements other than C<SELECT> in the > > above-listed > > +convenience functions, others do not. Requirement of this facility is > > not defined > > +by the DBI interface standard. The C<do> method is provided for > > non-C<SELECT> > > +statements. When you really don't know if the statement you have in a > > variable > > +is going to be a C<SELECT> or not, unrolling the process into > > C<prepare>,C<execute>, > > +and some C<fetch> variant will always work, as C<fetch> is defined to > > return > > +no data when called on executed non-C<SELECT> statement handles. > > +

> > While it says that fetch() is defined to return no data, it doesn't

> appear

> > to define whether an error/warning will be issued in this case. I

> thought

> > I'd tested calling fetch() 2x and got the same message (which isn't to

> say

> > that the statement failed, I didn't even check for that). How do you

> expect

> > prepare();execute();fetch() to work in 1.30_07 vs. fetchall_arrayref()? > > > > Thanks, > > > > -Peter

> > If you run the code you were running through selectall_* or fetchall_* > in DBD::ODBC via the latest version from subversion trunk you'll get an > error saying no select statement is running. It is my belief that this > is what you should get (and you would have got in older DBD::ODBCs until > I broke this aspect of error reporting) and that is what other DBDs do. > It is also the belief of the commentators on the issue I raised. I > cannot move this on - only DBI developers can. > > I had intended releasing 1.30_7 this weekend but I was away. I'll try to > sort it out this week. > > I cannot unilaterally change DBI and this issue is going beyond > DBD::ODBC. I've mentioned it before but you'd get a much wider audience > on dbi-users mailing list (see dbi.perl.org) and much more likely to get > a definitive answer or resolution. > > Martin > -- > Martin J. Evans > Wetherby, UK >

Thu Jun 16 13:50:22 2011 bohica [...] ntlworld.com - Correspondence added

On Mon Jun 13 16:22:02 2011, pedrinho@gmail.com wrote: Show quoted text

> I'm OK with the change/restriction, I just don't understand where the line > is drawn regarding the fetch(). Will a fetch() succeed in this case, > perhaps with an empty list? > > Thanks > > -Peter

I'm losing track again. DBD::ODBC used to specifically error if you attempted to pass a non-select to DBI's selectall* methods - something like "no select statement executing". At some stage the error was broken and it stopped reporting the error so it looked from the outside like nothing was wrong. Then I found some ODBC drivers which returned an error but when you called SQLError they said there was no error so I changed the code to issue an error string that an error occurred but the ODBC driver would not tell us about it - this is the change you hit. I've now fixed the non-reporting error and if you pass a non-select to selectall* it errors. As far as I've been able to canvas other opinions on this on dbi-dev this is the correct behaviour and Tim Bunce (DBI author) has asked for a patch to clarify this in DBI. So, as far as I am concerned this issue is now fixed in 1.30_7 i.e., DBD::ODBC errors if you pass a non-select to selectall*. I will close it and if you have a problem with that you can email me and we can perhaps reopen it. Martin -- Martin J. Evans Wetherby, UK

Thu Jun 16 13:50:35 2011 bohica [...] ntlworld.com - Status changed from 'open' to 'resolved'

Thu Jun 16 13:50:36 2011 bohica [...] ntlworld.com - Fixed in 1.30_7 added