FAQ
According to the DBI docs:

==========
Perform whatever processing is necessary to execute the prepared
statement. An undef is returned if an error occurs. A successful execute
always returns true regardless of the number of rows affected, even if
it's zero (see below). It is always important to check the return status
of execute (and most other DBI methods) for errors if you're not using
"RaiseError".

For a non-SELECT statement, execute returns the number of rows affected,
if known. If no rows were affected, then execute returns "0E0", which
Perl will treat as 0 but will regard as true. Note that it is not an
error for no rows to be affected by a statement. If the number of rows
affected is not known, then execute returns -1.

For SELECT statements, execute simply "starts" the query within the
database engine. Use one of the fetch methods to retrieve the data after
calling execute. The execute method does not return the number of rows
that will be returned by the query (because most databases can't tell in
advance), it simply returns a true value.

If any arguments are given, then execute will effectively call
"bind_param" for each value before executing the statement. Values bound
in this way are usually treated as SQL_VARCHAR types unless the driver
can determine the correct type (which is rare), or unless bind_param (or
bind_param_inout) has already been used to specify the type.

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

but I'm doing a select * from table and getting back a true value
which is 1. I thought for a successful select, execute would return
0E0 (which is true) but not 1. I was hoping to use true but not
0E0 to identify a non-select statement that affected rows.

The following code using dbd::mysql illustrates this:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Data::Dumper;

my $dbh = DBI->connect(
'DBI:mysql:mjetest', 'xxx', 'yyy',
) or die "DBI::errstr";

$dbh->do(q/drop table if exists mytest/);
$dbh->do(q/create table mytest (a int primary key, b char(20))/);
my $sth = $dbh->prepare(q/insert into mytest values (?,?)/);
$sth->execute(1, 'one');
$sth->execute(2, 'two');
$sth->execute(3, 'three');
$sth = $dbh->prepare("select * from mytest where b = ?");
my $sts = $sth->execute('one');
print "sts = $sts\n";
$ref = $sth->fetchrow_arrayref;

which produces:

sts = 1

but I expected 0E0.

This was with DBI 1.50.

Any ideas? Am I missing something?

Martin

Search Discussions

  • Tim Bunce at May 19, 2006 at 11:29 pm

    On Fri, May 19, 2006 at 07:24:13PM +0100, Martin J. Evans wrote:

    but I'm doing a select * from table and getting back a true value
    which is 1. I thought for a successful select, execute would return
    0E0 (which is true) but not 1. I was hoping to use true but not
    0E0 to identify a non-select statement that affected rows.
    You can use $sth->{NUM_OF_FIELDS} to tell if it's a SELECT statement.

    Tim.
  • Martin J. Evans at May 22, 2006 at 8:13 am

    On 19-May-2006 Tim Bunce wrote:
    On Fri, May 19, 2006 at 07:24:13PM +0100, Martin J. Evans wrote:

    but I'm doing a select * from table and getting back a true value
    which is 1. I thought for a successful select, execute would return
    0E0 (which is true) but not 1. I was hoping to use true but not
    0E0 to identify a non-select statement that affected rows.
    You can use $sth->{NUM_OF_FIELDS} to tell if it's a SELECT statement.

    Tim.
    Thanks and sorry - what comes from working too late.
    I've made that change but I still don't think execute is returning what the
    documentation suggests it should.

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedMay 19, '06 at 6:27p
activeMay 22, '06 at 8:13a
posts3
users2
websitedbi.perl.org

2 users in discussion

Martin J. Evans: 2 posts Tim Bunce: 1 post

People

Translate

site design / logo © 2022 Grokbase