Fetch all records using Perl DBI

Question

I have a Perl script that uses a DBI connection. I open and read the SQL script file using a sub routine. I am printing only one record, where I should have two more (three records total). How do I get all the records?

Result:

Alert:OUTBOUND_DATA:0

Script:

my $dbh_oracle = DBI->connect(
          $CFG{oracle_dbi_connect},
          $CFG{db_user},
          $CFG{db_cred},
          {AutoCommit => 0,
           RaiseError => 0,
           PrintError => 0}) or die ("Cannot connect to the database: ".$DBI::errstr."\n");

my ($val1, $val2) = get_data();
print "Alert:$val1:$val2\n";

send_email("Alert:$val1:$val2");

sub get_data
{
  undef $/;
  open (my $QFH, "< /sql/summary.sql") or die "error can't open this file $!";
  my $sth= $dbh_oracle->prepare(<$QFH>) or
      die ("Cannot connect to the database: ".$DBI::errstr."\n");
  $sth->execute;
  close $QFH;
  my $row = $sth->fetchrow_hashref;
  $sth->finish;
  return @$row{'MYTABLE','FLAG'};
}

sub send_email {
    my $message = shift;
    open (MAIL, "|/usr/sbin/sendmail -t") or die "Can't open sendmail: $!";
    print MAIL "To: me\@test.com\n";
    print MAIL "From: Data\n";
    print MAIL "\n";
    print MAIL $message;
    close MAIL;
}
exit;

RESULTS from running query: (more than 1 rec)

MYTABLE                  FLAG
----------------------- ----------
OUTBOUND_DATA         0
MSGS_BY_DIM                  0
INBOUND_DATA         0

3 rows selected.

Answer 1

There are many different ways you can retrieve data from a statement handle. The most common are quite simple and their use is shown below:

my @row_array = $sth->fetchrow_array;
my $array_ref = $sth->fetchrow_arrayref;
my $hash_ref  = $sth->fetchrow_hashref;

The first, fetchrow_array, will return each row in turn as an array. An example using data returned from the select above could be:

while (my @row_array = $sth->fetchrow_array) {
    print $row_array[0], " is ", $row_array[1], " years old, and has a " , 
          $row_array[2], "\n";
}

The second example is similar but returns an array reference rather than an array:

while (my $array_ref = $sth->fetchrow_arrayref) {
    print $array_ref->[0], " is ", $array_ref->[1], 
          " years old, and has a " , $array_ref->[2], "\n";
}

The third example, fetchrow_hashref, is often the most readable:

while (my $hash_ref = $sth->fetchrow_hashref) {
    print $hash_ref->{name}, " is ", $hash_ref->{age}, 
          " years old, and has a " , $hash_ref->{pet}, "\n";
}

Answer 2

It also depends on how you are structuring your overall script. Your get_data() call only allows a single pair of values to be returned. I see at least a couple options: either return a hash (reference) containing all the data and let the main assemble it, or use the loop constructs mentioned previously and fabricate the message body inside the subroutine, returning only a single scalar string.

To return all the data as a hash reference, the get_data subroutine might look like this (note I'm using fetchall_hashref instead of fetchrow_hashref:

sub get_data
{
  undef $/;
  open (my $QFH, "< /sql/summary.sql") or die "error can't open this file $!";
  my $sth= $dbh_oracle->prepare(<$QFH>) or
      die ("Cannot connect to the database: ".$DBI::errstr."\n");
  $sth->execute;
  close $QFH;
  my $hash_ref = $sth->fetchall_hashref('MYTABLE');
  $sth->finish;
  return $hash_ref;
}

And you call it from main and use the output as follows:

my $hash_ref = get_data();
my $message = "";
foreach my $table (sort keys %$hash_ref) {
    $message .= join(":", "Alert", $table, $$hash_ref{$table}{'FLAG'}) . "\n";
}

This will result in $message containing:

Alert:INBOUND_DATA:0
Alert:MSGS_BY_DIM:0
Alert:OUTBOUND_DATA:0

And you may want to politely:

$dbh_oracle->disconnect;

before you exit.

This has some problems, for example you've got the SQL stashed in an external script, but I have resorted to hard-coding the key (MYTABLE, which I am presuming is unique in your query) and the value (FLAG) in the script, which will be limiting later when you want to expand on this.