Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

Re: Sqlite DBI $sth->rows

by lachoy (Parson)
on Oct 22, 2003 at 13:09 UTC ( #301210=note: print w/replies, xml ) Need Help??

in reply to Sqlite DBI $sth->rows

In addition to jeffa's good advice, you cannot rely on rows() returning anything reasonable for a SELECT statement. Don't use it.

M-x auto-bs-mode

Replies are listed 'Best First'.
Re: Re: Sqlite DBI $sth->rows
by The_Jesus (Novice) on Oct 22, 2003 at 14:11 UTC
    What do you suggest I use in its place?
      Before I give some alternative methods, I thought you might like to get the official explanation on the "rows" method - this is 'perldoc DBI' section on "rows":
      "rows" $rv = $sth->rows; Returns the number of rows affected by the last row affecti +ng com- mand, or -1 if the number of rows is not known or not avail +able. Generally, you can only rely on a row count after a non-"SE +LECT" "execute" (for some specific operations like "UPDATE" and "DELETE"), or after fetching all the rows of a "SELECT" sta +tement. For "SELECT" statements, it is generally not possible to kn +ow how many rows will be returned except by fetching them all. So +me drivers will return the number of rows the application has +fetched so far, but others may return -1 until all rows have been f +etched. So use of the "rows" method or $DBI::rows with "SELECT" sta +tements is not recommended. One alternative method to get a row count for a "SELECT" is + to exe- cute a "SELECT COUNT(*) FROM ..." SQL statement with the sa +me "..." as your query and then fetch the row count from that.
      As the perldocs suggest, here's an example of doing a SELECT COUNT to get the number of rows found:
      my $sql = { SELECT count(*) FROM patient_data WHERE name = ? }; my $sth = $dbh->prepare($sql); $sth->execute($name); my ($count_rows) = $sth->fetchrow_array();
      or, you could fetch all the rows, one-by-one, and count them as you go, like:
      my $sql = { SELECT name FROM patient_data WHERE name = ? }; my $sth = $dbh->prepare($sql); $sth->execute($name); my $count_rows = 0; while (my ($name) = $sth->fetchrow_array()) { ### do something with $name here ### $count_rows += 1; }
      Of course, I haven't done anything with error trapping the DBI statements here, but you definitely should.

        my $sql = { SELECT count(*) FROM patient_data WHERE name = ? }; my $sth = $dbh->prepare($sql); $sth->execute($name); my ($count_rows) = $sth->fetchrow_array();
        Note that this is not complete without a finish() call:
        You need to use finish if you don't repeat calling fetchrow_*, or equivalent, until it returns undef. So you need it in your second example, too.

        If you omit it, you'll get a warning from DBI, or you should. (Maybe this depends on the database driver?)

      Super Search to the rescue - again.

      I had trouble with this issue, and found a decent thread here SELECT COUNT and DBI: rows that hashed out the issue pretty well with many experienced monks chiming in.

      It looks like the most portable way of doing this is to SELECT COUNT.... before doing the actual SELECT.
      You can also wrap your $sth->fetchrows in a while statement, which is ok if you just want to do nothing when no results are returned. It won't allow you to proactively deal with a situation where 0 records are returned, ie let a user know that their query returned no results.

        I'm not quite sure what you mean by "proactively", but this deals with the case of no rows found ...
        my $dbh = DBI->connect($conn_string, $user, $password, { RaiseError => 1, }) || die $DBI::errstr; my $sth = $dbh->prepare(<<__END_SQL__); SELECT stuff FROM some_place WHERE some_condition = 1 __END_SQL__ $sth->execute; my $num_rows = 0; while (my @x = $sth->fetchrow) { $num_rows++; do_something_with(@x); } die "No rows found\n" unless $num_rows; print "Done\n";

        We are the carpenters and bricklayers of the Information Age.

        The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

        ... strings and arrays will suffice. As they are easily available as native data types in any sane language, ... - blokhead, speaking on evolutionary algorithms

        Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

        I fully agree. I had the same problems and scanned the web quite extensively. The SELECT COUNT method seems to work best (well, at least for me :) I'd prefer it over fetching all rows and counting them in a while loop, since that seems like a waste of resources to me (especially on really large data sets), and also over the $sth->rows, 'cause that doesn't seem to be reliable on SELECT queries.

        Yes, that is one long line ;)

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://301210]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (5)
As of 2022-05-25 20:37 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (90 votes). Check out past polls.