Re: Is it ever legal (should be supported) to issue a non select in selectall_arrayref?

On 11/06/11 22:49, David Nicol wrote:
> 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.
> +
>   =head3 C<prepare>
>
>     $sth = $dbh->prepare($statement)          or die $dbh->errstr;
>
> --
>

I'm not sure DBD::ODBC knows the difference between "fetch" being called on an executed non-select statement and selectall*. The person who originally rt'd this has read this thread and thinks the proposed DBI pod change now infers:

use DBI;
use Data::Dumper;
$dbh = DBI->connect("dbi:ODBC:DSN=DBGEARDEVNYC-es;APP=nortonp_$ARGV[0]", "guestuser", "deshaw",
             {
             HandleError=>sub {print "ODBCError: " . Dumper(\@_) . "\n"},
             });
my $ref = $dbh->prepare("create table #foo (fooint int)");
$ref->execute();
my $results = $ref->fetch();

would put false in $results but DBD::ODBC errors with:

'DBD::ODBC::st fetch failed: no select statement currently executing (SQL-HY000)'

The problem is that in moving from DBD::Sybase to DBD::ODBC his code doing the above worked and now doesn't. It accidentally worked in a previous DBD::ODBC because the above error was masked (due to a bug I introduced). Obviously I could make it work but then anyone calling fetch on a non-select by accident or as the result of a bug in their code would not know. Personally, if I was not sure if some SQL was a select or not I'd examine NUM_OF_FIELDS first before I called fetch. DBD::Sybase differs from DBD::ODBC in the fetch as DBD::Sybase does:

	/* Check that execute() was executed sucessfully. This also implies	*/
	/* that describe() executed sucessfuly so the memory buffers	*/
	/* are allocated and bound.						*/
	if (!DBIc_is(imp_sth, DBIcf_ACTIVE) || !imp_sth->exec_done) {
		return Nullav;
	}

and DBD::ODBC does:

     /* Check that execute() was executed sucessfully. This also implies	*/
     /* that dbd_describe() executed sucessfuly so the memory buffers	*/
     /* are allocated and bound.						*/
     if ( !DBIc_ACTIVE(imp_sth) ) {
         dbd_error(sth, DBDODBC_INTERNAL_ERROR, "no select statement currently executing");
         return Nullav;
     }

So the proposed change saying

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

I don't see how DBD::ODBC is supposed to implement that. fetch is just an alias for fetchrow_arrayref. There is fetch and nothing else. If you call selectall* it ends up in dbd_st_fetch, if you call fetch it ends up in dbd_st_fetch.

So the question becomes which is right DBD::Sybase behaviour or DBD::ODBC.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com