perl: help with DBI

Shell Programming and Scripting
1

Hi there, I have a bit of code similar to below (which ive actually got from perldoc, but mine is similar enough)

 
  $sth = $dbh->prepare(q{ SELECT region, sales FROM sales_by_region });
  $sth->execute;
  my ($region, $sales);

  # Bind Perl variables to columns:
  $rv = $sth->bind_columns(\$region, \$sales);


  while ($sth->fetch) {
      print "$region: $sales\n";
  }

I wanted to find a way of testing to see if the result set was empty so that within my while loop i could print "sorry, result set is empty\n" instead of just printing nothing (which is what print "$region: $sales\n"; would do as there are no results ...)

how can I add a test for an empty record set into here?

Any help would be greatly appreciated

2

Not sure if this will work for you, but why not try it:

while ($sth->fetch) {

 if ($region ne "" && $sales ne "") {
      print "$region: $sales\n"; 
 }
 else {
      print "Sorry, result set is empty.\n";
 }

}
3

What database are you using? There may be a way to code the SQL to return you some count or indicator when no rows are returned from your SELECT.

There is also a method called 'rows', but it does not work for SELECT statements.

From perldoc DBI:

$rv = $sth->rows;

Returns the number of rows affected by the last row-affecting command, or -1 if the number of rows is not known or not available.

Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement.

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.

One alternative method to get a row count for a SELECT is to execute a "SELECT COUNT(*) FROM ..." SQL statement with the same "..." as your query, and then fetch the row count from that.
4

thank you for your responses, there's plenty there to point me in the right direction

Cheers

5

If the result set was empty, control wouldn't even go inside the while loop; the "fetch" method wouldn't run even once.

Again, "printing nothing" is not possible because the condition for the while loop would fail the first time itself.

Here's an Oracle example:

$
$
$ # check if the table "sales_by_region" has data in it
$ echo "select * from sales_by_region;" | sqlplus -s test/test
 
no rows selected
 
$
$ # so there's no data in it
$ # display the content of the Perl program "fetchsales.pl"
$ cat -n fetchsales.pl
    1  #!perl
    2  use DBI;
    3  use vars qw($dbh $sth $region $sales $rv);
    4
    5  $dbh = DBI->connect('dbi:Oracle:', 'test', 'test');
    6  $sth = $dbh->prepare(q{ SELECT region, sales FROM sales_by_region });
    7  $sth->execute;
    8  my ($region, $sales);
    9  # Bind Perl variables to columns:
   10  $rv = $sth->bind_columns(\$region, \$sales);
   11  while ($sth->fetch) {
   12    print "$region: $sales\n";
   13  }
   14  $sth->finish();
   15  $dbh->disconnect();
$
$ # run the program
$ perl fetchsales.pl
$
$

As you can see, the Perl program printed nothing because control did not go to the while loop at all.

This technique would backfire if there was a record in table with a NULL value for either region column, or sales column or both !

$
$
$ # check the data in the "sales_by_region" table
$ echo "select * from sales_by_region;" | sqlplus -s test/test
       ID REGION          SALES
---------- ---------- ----------
        1 East
        2                599.75
        3
        4 South           273.9
 
4 rows selected.
 
$
$
$ # "sales" is NULL for id=1, "region" is NULL for id=2
$ # and both are NULL for id=3
$
$ # display the content of the Perl program "fetchsales.pl"
$ cat -n fetchsales.pl
    1  #!perl
    2  use DBI;
    3  use vars qw($dbh $sth $region $sales $rv);
    4  $dbh = DBI->connect('dbi:Oracle:', 'test', 'test');
    5  $sth = $dbh->prepare(q{ SELECT region, sales FROM sales_by_region });
    6  $sth->execute;
    7  my ($region, $sales);
    8  # Bind Perl variables to columns:
    9  $rv = $sth->bind_columns(\$region, \$sales);
   10  while ($sth->fetch) {
   11    if ($region ne "" && $sales ne "") {
   12      print "$region: $sales\n";
   13    } else {
   14      print "Sorry, result set is empty.\n";
   15    }
   16  }
   17  $sth->finish();
   18  $dbh->disconnect();
   19
$
$ # run the program
$ perl fetchsales.pl
Sorry, result set is empty.
Sorry, result set is empty.
Sorry, result set is empty.
South: 273.9
$
$

tyler_durden

1 Like