Display the whole table in an email in Perl

Hi,

I have a table in DB2 like this --

Table Name: Employee

Name  City             Phn
Rob    Yonkers      212-120-4123
Peter   Chicago      646-213-1987
Adam   L.A.            408-134-7656


I want to display this whole table in the body of an email with the word "Employee" as the subject and send it to some pre-selected receipients. How can I do this in Perl? Is there any module?
sunny82Asked:
Who is Participating?
 
wilcoxonConnect With a Mentor Commented:
Here's the code I previously submitted modified to work with MIME::Lite...

Based on your code snippet, I see two likely problems:
1) It looks like you put all the data into a flat single-dimension list.
2) The way you have that loop set up, you will create one MIME message per entry in tab_arr but they will all contain the same data (@tab_arr[0..2]).
#!/usr/local/bin/perl

use strict;
use warnings;
use DBI;
use MIME::Lite;

my @who = ('someone@here.com', 'else@there.com');

my $dbh = DBI->connect('whatever is necessary for DB2') or die "could not connect to DB: $!";

# could also be done as an HTML email but I'm doing it as text
my @tar_arr;

my $sth = $dbh->prepare("select * from Employee");
$sth->execute;
while (my @row = $sth->fetchrow_array) {
    push @tab_arr, [@row];
}
$sth->finish;

my $body = '';
foreach my $row (@tar_arr) {
    $body .= "<tr>\n<td>" . join("</td>\n<td>", @$row) . "</td>\n</tr>\n";
}

my $msg = MIME::Lite->new(
    From => 'myname@myemail.com',
    To => join(", ", @who),
    Subject => 'Employee',
    Type => 'text/html',
    Data => qq{
<table border="1">
<tr>
<th>Name</th>
<th>City</th>
<th>Phone</th>
</tr>
$body
</table>
    }
);
$msg->send;

Open in new window

0
 
sunny82Author Commented:
To display the table we can fire the query $query = "select * from Employee"
0
 
wilcoxonCommented:
Sure.  You can do something like this...
#!/usr/local/bin/perl

use strict;
use warnings;
use DBI;
use Mail::Send;

my @who = ('someone@here.com', 'else@there.com');

my $dbh = DBI->connect('whatever is necessary for DB2') or die "could not connect to DB: $!";

# could also be done as an HTML email but I'm doing it as text
my (@body, @sz);

my $sth = $dbh->prepare("select * from Employee");
$sth->execute;
while (my @row = $sth->fetchrow_array) {
    for my $i (0..@row-1) {
        my $len = defined($row[$i]) ? length($row[$i]) : 0;
        $sz[$i] = $len unless ($sz[$i] and $sz[$i] >= $len);
    }
    push @body, [@row];
}
$sth->finish;

my $msg = Mail::Send->new(Subject => 'Employee');
$msg->to(@who);
my $body = $msg->open;
my $fmt = "%$sz[0]s";
for my $i (1..@sz-1) {
    $fmt .= "  %sz[$i]";
}
$fmt .= "\n";
foreach my $row (@body) {
    printf $fh $fmt, @$row;
}
$body->close; # send

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sunny82Author Commented:
Sure. I will try and let you know.
0
 
sunny82Author Commented:
Since yesterday, I am trying another approach through html. Here I can only see the first row though the array @tab_arr contains all the rows of the Employee table. I am using the Mime::Lite module.

Where am I going wrong?

=================================
for (my $i=0; $i <= $#tab_arr; $i++) {
   $msg = MIME::Lite->new( To => 'myname@myemail.com',
                           From => 'myname@myemail.com',
                           Subject => 'Employee',
                           Type => 'text/html',
                           Data => qq{
                           <table border ="1">
                           <tr>
                           <th>Name</th>
                           <th>City</th>
                           <th>Phone</th>
                           </tr>
                           <tr>
                           <td>$tab_arr[0]</td>
                           <td>$tab_arr[1]</td>
                           <td>$tab_arr[2]</td>
                           </tr>
                           </table>
                           }
                         );

}

$msg->send();
=================================
0
 
sunny82Author Commented:
Excellent that worked great. Thanks so much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.