?
Solved

Display the whole table in an email in Perl

Posted on 2011-04-19
6
Medium Priority
?
220 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:sunny82
  • 4
  • 2
6 Comments
 

Author Comment

by:sunny82
ID: 35428054
To display the table we can fire the query $query = "select * from Employee"
0
 
LVL 27

Expert Comment

by:wilcoxon
ID: 35428543
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
 

Author Comment

by:sunny82
ID: 35429634
Sure. I will try and let you know.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:sunny82
ID: 35433839
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
 
LVL 27

Accepted Solution

by:
wilcoxon earned 2000 total points
ID: 35433960
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
 

Author Comment

by:sunny82
ID: 35434064
Excellent that worked great. Thanks so much.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question