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
