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.

Solution 1

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.

Solution 2

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

Solution 3

This line should be a loop:

my $row = $sth->fetchrow_hashref;

It should be:

my @rows;
while ( my $row = $sth->fetchrow_hashref ) {
    push @rows, $row;
}
return @rows;

If you'd rather have DBI do the loop for you, check out selectall_arrayref or selectall_hashref

Solution 4

The fetchrow_ methods literally just fetch one row at a time.

If you want all the rows of some columns, you can inefficiently push around data structures, or use a call meant for your situation.

It seems to me you want to use selectcol_arrayref as in:

my $ary_ref = $dbh->selectcol_arrayref(
    "select id, name from table",
    { Columns=>[1,2] }
);

The column indexes refer to the position of the columns in the result set, not the original table.

The way you use the returned results will also need to be changed to handle all rows being returned.

Also, you have:

sub get_data
{
  undef $/;

because you slurp the file containing the SQL. However, $/ is a global variable. You should use local $/ in the smallest possible scope. So:

my $sql = do { local $/; <$fh> };

View more solutions

Comments

[cjd143SD]

where do I put $sth->finish? would that be after I return @$row?

[gpojd]

@cjd143SD, when you finish the loop, $sth->fetchrow_hashref will return undef and "finish" itself.

[gpojd]

A note about fetchrow_hashref, the keys will be upper cased unless you change the FetchHashKeyName attribute.

[cjd143SD]

yes, I agree in those cases I want everything from the query it seems selectcol_arrayref would be the choice. Your suggestion for using local $/, would I include that as a subroutine or leave this outside? thanks.

[cjd143SD]

This is what i was looking for. i was trying to build on using a subroutine. Thanks for providing the use of main example as a way to assemble all the output.

[gregn3]

This answer is not complete without mentioning @gpojd 's answer with the selectall_ functions