Geek asks and answers

Retrieving 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 routine. I print only one entry, where I should have two more (only three entries). How to 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 the current query: (more than 1 rec)

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

3 rows selected.
+3
source share
4 answers

, script. get_data() . , , : (), , main, , .

-, get_data ( , fetchall_hashref 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;
}

main :

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

$message, :

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

:

$dbh_oracle->disconnect;

.

, , SQL, script, (MYTABLE, , , ) (FLAG) script, , .

+2

. , :

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

, fetchrow_array, . , , :

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

, , :

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

, fetchrow_hashref, :

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

:

my $row = $sth->fetchrow_hashref;

:

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

, DBI , selectall_arrayref selectall_hashref

+2

fetchrow_ .

, , .

, selectcol_arrayref, :

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

, .

, , .

, :

sub get_data
{
  undef $/;

, SQL. $/ . local $/ . :

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

More articles:


All Articles