CODEWITHSUNDEEP
perldbi
cjd143SD
cjd143SD

Reputation: 869

Fetch all records using Perl DBI

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.

Upvotes: 5

Views: 41931

Answers (4)

Sinan &#220;n&#252;r
Sinan &#220;n&#252;r

Reputation: 118138

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

Upvotes: 2

gmk
gmk

Reputation: 179

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.

Upvotes: 3

gpojd
gpojd

Reputation: 23065

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

Upvotes: 3

Ωmega
Ωmega

Reputation: 43673

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

Upvotes: 9

Related Questions