$query_handle->execute($patient_name);
[download]

use strict;
use warnings;
use DBI;

my $name = shift or die "need patient's name\n";

my $dbh = DBI->connect(
   'dbi:SQLite:dbname=foo.dbm',undef,undef,
   {RaiseError=>1}
);

my $sth = $dbh->prepare('
   SELECT name
   FROM patient_data
   WHERE name = ?
');

$sth->execute($name);

warn "no match for $name\n" unless $sth->rows;
[download]

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

Your suggestion about using RaiseError is good. The only snag is that RaiseError does not apply to the connect call that creates the database handle. The success of the connect needs to be checked explicitly.

my $dbh = DBI->connect('dbi:SQLite:dbname=foo.db',undef,undef,
   { RaiseError=>1 }
) or die $DBI::errstr;
[download]

...RaiseError does not apply to the connect call...

Yes, it does apply. If it doesn't, then it's a bug in the DBD. So you don't need an 'or die ...' on the connect when you use RaiseError

The success of the connect most certainly does not need to be checked explicitly, that is, by both setting RaiseError and calling die yourself - DBI.pm does it for you:

DBI.pm (version 1.38)
358: sub connect {
...
416: unless ($dbh = $drh->$connect_meth($dsn, $user, $pass, $attr)) {
        my $msg = "$class->connect($dsn) failed: ".$drh->errstr;
        if (ref $attr) {
            Carp::croak($msg) if $attr->{RaiseError};
            Carp::carp ($msg) if $attr->{PrintError};
        }
        DBI->trace_msg("       $msg\n");
        $! = 0; # for the daft people who do DBI->connect(...) || die "$!";
        return undef;
    }
...

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

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

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.
[download]

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();
[download]

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;
  }
[download]

Of course, I haven't done anything with error trapping the DBI statements here, but you definitely should.

HTH.

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.

hth,
digger

my $sql = qq( 
use customers
select * from customers
);
[download]