How can I check if a database query will return results?

Question

Our website uses Perl to provide a simple mechanism for our HR people to post vacancies to our website. It was developed by a third party, but they have been long since kicked into touch, and sadly we do not have any Perl skills in-house. This is what happens when Marketing people circumvent their in-house IT team!

I need to make a simple change to this application. Currently, the vacancies page says 'We currently have the following vacancies:', regardless of whether there are any vacancies! So we want to change it so that this line is only displayed at the appropriate times.

I could, obviously, start to learn a bit of Perl, but we are already planning a replacement site, and it certainly won't be using Perl. So since the solution will be trivial for those with these skills, I thought I'd ask for some focused help.

Below is the start of the procedure that lists the vacancies.

sub list {
  require HTTP::Date;
  import HTTP::Date;

  my $date = [split /\s+/, HTTP::Date::time2iso(time())]->[0];

  my $dbh = DBI->connect($dsn, $user, $password)
    || die "cannot connect to $database: $!\n";

  my $sql = <<EOSQL;
SELECT * FROM $table where expiry >= '$date' order by expiry
EOSQL

  my $sth = $dbh->prepare($sql);
  $sth->execute();


  while (my $ref = $sth->fetchrow_hashref()) {
    my $temp  = $template;
    $temp     =~ s#__TITLE__#$ref->{'title'}#;

    my $job_spec = $ref->{'job_spec'};

...etc...

The key line is while (my $ref = $sth->fetchrow_hashref()) {. I'm figuring that this is saying 'while I can pull off another vacancy from the returned recordset...'. If I place my print statement before this line, it will always be shown; after this line and it was be repeated for every vacancy.

How do I determine that there are some vacancies to be displayed, without prematurely moving through the returned recordset?

I could always copy the code within the while loop, and place it within an if() statement (preceding the while loop) which will also include my print statement. But I'd prefer to just have the simpler approach of If any records then print "We currently have.." line. Unfortunately, I haven't a clue to code even this simple line.

See, I told you it was a trivial problem, even considering my fumbled explanation!

TIA

Chris

Answer 1

This isn't so much a Perl question as it's a database question, and there is no good way to know how many results you have until you have them. You've got two choices here:

1. Do a query that does a "select count(*)" to see how many rows there are, and then another query to get the actual rows or
2. Do the query and store the results into a hash, then count how many entries you have in the hash, and then go through the hash and print out the results.

For example, off the top of my head:

my @results = ();
while (my $ref = $sth->fetchrow_hashref()) {
   push @results, $ref;
}

if ($#results == 0) {
  ... no results
} else {
  foreach $ref (@results) {
    my $temp = $template;
    ....
 }

Answer 2

I know no Perl, but perhaps you could have a variable such as header_printed. At the top of the loop, if it is not true, print the header and then set it to false.

Answer 3

A really simple way would be:

$sth->execute();

my $first = 1;
while (my $ref = $sth->fetchrow_hashref()) {
    if( $first ) {
        print "We currently have the following vacancies:\n";
        $first = 0;
    }
    my $temp  = $template;
    ...
}
if( $first ) {
    print "No vacancies found\n";
}

Answer 4

A bit more efficient way (avoiding a conditional inside the loop), if you don't mind it changing the way the page is output a bit (all at once rather than a row at a time) you could make a variable to hold the output just before the loop:

my $output = '';

and then inside the loop, change any print statement to look like this:

$output .= "whatever we would have printed";

then after the loop:

if ($output eq '')
{
  print 'We have no vacancies.';
}
else
{
  print "We currently have the following vacancies:\n" . $output;
}

Answer 5

Just add another query.. something like this:

# count the vacancies    
$numinfo = $dbh->prepare("SELECT COUNT(*) FROM $table WHERE EXPIRY >= ?");
$numinfo->execute($date);
$count = $numinfo->fetchrow_arrayref()->[0];

# print a message
my $msg = '';
if   ($count == 0) $msg = 'We do not have any vacancies right now';
else               $msg = 'We have the following vacancies';
print($msg);

Answer 6

Thanks to all who wasted a few minutes on my behalf.

I was expecting a solution that checked to see if anything was in $sth or similar, but Graeme's approach was quick and simple (and has already been successfully implemented).

@Paul - I know Perl is highly thought of, and I'm always learning, but I've got to prioritise!

Anyway, you guys have helped me dodge a bullet, so Thank You.

Chris

Answer 7

If you are using Mysql, the "rows" method works just fine:

$sth->execute();

if($sth->rows) {
  print "We have data!\n";
}

while(my $ref = $sth->fetchrow_hashref()) {
...
}

The method, and some caveats, are documented in extensive detail in "perldoc DBI". Always start with "perldoc".

Answer 8

use Lingua::EN::Inflect 'PL';

$sth->execute();
my $results = $sth->fetchall_arrayref( {}, $max_rows );

if (@$results) {
    print "We currently have the following ", PL("vacancy",scalar @$results), ":\n";

    for my $ref (@$results) {
        ...
    }
}

Answer 9

Since everyone wants to optimize away the repeated tests for whether the header has been printed in Graeme's solution, I present this minor variation on it:

$sth->execute();

my $ref = $sth->fetchrow_hashref();
if ($ref) {
  print "We currently have the following vacancies:\n";
  while ($ref) {
    my $temp  = $template;
    ...
    $ref = $sth->fetchrow_hashref();
  }
} else {
    print "No vacancies found\n";
}

Answer 10

Says perldoc DBI:

 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.

So the answer is to check the return value of $sth->execute():

 my $returnval = $sth->execute;
 if (defined $returnval && $returnval == 0) {
     carp "Query executed successfully but returned nothing";
     return;
 }

Answer 11

Be careful using ->rows, I believe with some of the drivers, it will not have valid results until at least one row has been returned... and on some, it won't be valid until all rows have been returned.