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

Sleep

Note that require automatically dies for you if it fails, so I tweaked that a bit so no one gets to copy and paste that into their code. :) – Whitechapel

Answer 1

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";
}

Zinovievsk

Or you could check the return value of $sth->execute(), which tells you in advance whether there's going to be any data at all. – Specialism

@SamKington: the return value of $sth->execute() returns the number of rows affected ONLY for non-SELECT statements. See: search.cpan.org/~timb/DBI-1.631/DBI.pm#execute – Accidental

Answer 2

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;
    ....
 }

Firsthand

if you are going to do that, you might as well just replace the read loop with @results = @{ $sth->fetchall_arrayref( {} ) }; – Alcantar

$#results will be 0 when there's one result. Please don't use $#array unless you are sure you need to. Most of the time, @array in scalar context is what you want instead. – Alcantar

@Alcantar - Well, I did say it was off the top of my head. Would scalar(@array) do what I want? – Firsthand

Another issue is that if there are thousands (or hundreds of thousands) of rows in the result, you may not want to hold them all in memory at the same time. – Zinovievsk

@Graeme: Unless he's working for monster.com, I doubt he has thousands of open positions. – Firsthand

@Paul: scalar(@array) will return the number of items in the array, yes, but, in this case, just testing 'if (@array)...' is sufficient and, IMO, more readable than explicitly scalarizing it. – Honorarium

@Dave - I've never liked that "context" feature of perl. I think being explicit about it is clearer. – Firsthand

@Paul: Yes, in this particular case that's not a problem. I was referring to the general case. – Zinovievsk

@Paul: Fair enough, a lot of people don't like that. My point was really more that, conceptually, what you're looking for here is whether there's anything in the array (if (@array)) rather than the count of items it contains (if (scalar(@array) != 0)). But, as always, TIMTOWTDI. – Honorarium

Answer 3

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

Cantharides

You cannot rely on $sth->rows for SELECT queries (as the one in question). See: search.cpan.org/~timb/DBI-1.631/DBI.pm#rows – Accidental

Answer 4

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";
}

Honorarium

Answer 5

Since your query is a SELECT, you cannot take advantage of rows or of the value returned by the execute itself.

However, you can pre-count how many rows (i.e. vacancies) your query will select by adding another query... something like this:

# Retrieve how many vacancies are currently offered:
my $query = "SELECT COUNT(*) AS rows FROM $table WHERE expiry >= ?";
$sth = $dbh->prepare($query);
$sth->execute($date);
$numVacancies = $numinfo->fetchrow_arrayref()->[0];

# Debug:
print "Number of vacancies: " . $numVacancies . "\n";

if ( $numVacancies == 0 ) { # no vacancy found...
    print "No vacancies found!\n";
}
else { # at least a vacancy has been found...
    print "We currently have the following vacancies:\n";

    # Retrieve the vacancies:
    my $sql = "SELECT * FROM $table where expiry >= '$date' ORDER BY expiry";
    my $sth = $dbh->prepare($sql);
    $sth->execute();

    ...
}

Or, similarly, instead of "prepare" and "execute" the query and then use "fetchrow_array", you can do everything in a single call using selectrow_array:

# Retrieve how many vacancies are currently offered:
my $query = "SELECT COUNT(*) AS rows FROM $table WHERE expiry >= ?"; 
my $numVacancies = $dbh->selectrow_array($query, undef, $date);

# Debug:
print "Number of vacancies: " . $numVacancies . "\n";

And the same is also true for selectall_arrayref:

# Retrieve how many vacancies are currently offered:
my $query = "SELECT COUNT(*) AS rows FROM $table WHERE expiry >= ?";
my $numVacancies = $dbh->selectall_arrayref($query, {Slice => {}}, $date);

# Debug:
print "Number of vacancies: " . @$numVacancies[0]->{rows} . "\n";

However, if you use selectrow_array or selectall_arrayref, you can also retrieve the number of vacancies directly from the result of the original query:

# Retrieve the vacancies:
my $sql = "SELECT * FROM $table where expiry >= ? ORDER BY expiry";
my $vacancies = $dbh->selectall_arrayref($sql, {Slice => {}}, $date);

# Debug:
print "Number of vacancies: " . scalar @{$vacancies} . "\n";

Accidental

Answer 6

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;
}

Skewness

Unless they're Google, they probably don't have more than a few or a few dozen vacancies at any time, so the cost of a conditional is miniscule compared to the cost of rendering the page. I still vote for @Graeme's solution. – Firsthand

For sure, but if the code happens to be reused in another context, it's good to think about. – Skewness

I'm sure the code will re-used in loads of places, but not by us! :) But I appreciate your sentiments - it would make sense as a 'good practice' approach. – Sleep

I'm also not convinced that doing a string concatenation operation is more efficient than a comparison. That's definitely something that would require benchmarking if you really wanted to violate the first rule of Optimization Club. – Firsthand

Agreed. For a large dataset, I would also expect the concatenations (with their associated memory allocation and string copying requirements) to be substantially less efficient than doing the test. – Honorarium

Answer 7

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);

Heartworm

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) {
        ...
    }
}

Alcantar

It differentiates between a single vacancy and multiple vacancies. – Honorarium

And I don't see any other solutions using fetchall_arrayref. – Alcantar

Answer 9

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;
 }

Specialism

perldoc specifically says "For a non-SELECT statement", and the question has a SELECT statement in it. – Zinovievsk