Empty Hash?

perleager has asked for the wisdom of the Perl Monks concerning the following question:

Hello,

Basically, below is a example on how to use bind_columns to improve your fetch effieciency/speed. Its working fine for me, but lets say the SQL statement doesn't return any rows, leaving it to print nothing. How can I determine that? For example, if it doesn't find anything , I want it to print "Sorry, No Matches." I have commented the example code below on where It would return nothing and where I want it to print the no match message.

use strict;
use DBI;

my $dbh = DBI->connect( 'dbi:mysql:golharam:serine',
                        '',
                        '',
                        {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                      ) || die "Database connection not made: $DBI::er
+rstr";

my $sql = qq{ SELECT id, name, title, phone FROM employees };
my $sth = $dbh->prepare( $sql );
$sth->execute();

my( $id, $name, $title, $phone );
$sth->bind_columns( undef, \$id, \$name, \$title, \$phone );

while( $sth->fetch() ) {    #lets say sth->fetch() doesn't return anyt
+hing...this is where i want to print the no match message.
  print "$name, $title, $phone\n";
}

$sth->finish();
$dbh->disconnect();
[download]

Thank you,
perleager

Comment on Empty Hash? Download Code

Replies are listed 'Best First'.
Re: Empty Hash? by jZed (Prior) on Dec 31, 2004 at 01:23 UTC
`$sth->bind_columns( undef, \$id, \$name, \$title, \$phone ); my $found; while( $sth->fetch() ) { $found++; print "$name, $title, $phone\n"; } print "No matches found!\n" unless $found; # $sth->finish(); finish() IS NOT NEEDED HERE` [download]	[reply] [d/l]
Re: Empty Hash? by strat (Canon) on Dec 31, 2004 at 10:12 UTC
For many databases, $sth->rows() returns how many lines are returned. If the DB can't do that, -1 is returned instead; then check the documentation of the DBD (e.g. perldoc DBD::mysql) if there is a special attribute or method which either gives you the ammount of rows or tells you that no more rows are available. If there is a method that tells you that no more results are available (e.g. with sybase), just check it before you fetch the data (e.g. before the while-loop) to find out if there is no data returned Best regards, perl -e "s>>F>e=>y)\martinF)stronat)=>print,print v8.8.8.32.11.32"	[reply]
Re^2: Empty Hash? by jZed (Prior) on Dec 31, 2004 at 16:54 UTC
From the DBI docs: "use of the rows method or $DBI::rows with SELECT statements is not recommended"	[reply]
Re^3: Empty Hash? by superfrink (Curate) on Jan 01, 2005 at 01:16 UTC
As soon as I read that I wanted to know why. So I looked it up. Here is a bit more context: For select statements it is generally not possible to know how many rows will be returned except by fetching them all. Some drivers will return the number of rows the application has fetched so far but others may return -1 until all rows have been fetched. So, use of the rows method, or $DBI::rows, with select statements is not recommended.	[reply]
Re^2: Empty Hash? by mpeppler (Vicar) on Jan 03, 2005 at 19:11 UTC
If there is a method that tells you that no more results are available (e.g. with sybase), just check it before you fetch the data (e.g. before the while-loop) to find out if there is no data returned Actually that's not quite right. DBD::Sybase's syb_more_results attribute will be TRUE until the first fetch been called, even if no rows are returned by a query - so you can't use that attribute (or DBI's upcoming mre_results() method) to check for zero length result sets. Michael	[reply]
Re: Empty Hash? by jbrugger (Parson) on Dec 31, 2004 at 05:41 UTC
as described here : http://search.cpan.org/~timb/DBI-1.46/DBI.pm, you can either set RaiseError, or check the returned set. ps. you can also use scalar(@array) for the size of an array, and scalar(keys %hash) for the size of a hash. little example: #!/usr/bin/perl -w use strict; my %none; my %more=(one=>"1", two=>"2"); print "none: ". scalar(keys %none) . "\n"; print "more: ". scalar(keys %more) . "\n";	[reply]
Re^2: Empty Hash? by jZed (Prior) on Dec 31, 2004 at 16:53 UTC
> you can either set RaiseError, or check the returned set Setting RaiseError will not tell whether rows have been selected - it is not an error to make a SELECT statement that finds 0 rows.	[reply]