Problems Trying to Send E-Mail and Insert into Database

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I have a script that was originally just supposed to send an e-mail to a user with the responses from an evaluation form.
I have just been told that we now want not only the e-mail sent, but also for one section of the evaluation to be inserted into a database.
I had a script that did that in another part of our project. I have tried to combine the two scripts but am running into problems.

I think there is a problem with having both processes in this script. At one point, I had the form responses being inserted into the database,
but no e-mail being sent. Now I have the e-mail sent, but no database insertion. It changes depending on where I put the statements.

How can I get both things accomplished? Or is this just a dumb error on my part?

I'll warn you... I am a Perl newbie.

#!/usr/local/bin/perl5.005
use lib '../lib';
use DBI(0.90);
use strict;
use Database;
use CGI qw(:standard);

Database::->db_info_path("../lib/db.cfg");
my $dbh = new Database;

#Grab the evaluation responses from the form
my $cdsid = param('cdsid');
my $title = param('Title');
my $author = param('Author');
my $book_review = param('book_review');
my $post_review = param('post_review');
my $use_name = param('use_name');

my $sendmail = '/usr/lib/sendmail';
my $recipient = 'mbelang3@ford.com';
my $sender = 'e-Books Feedback <plskills@ford.com>';
my $site_name = 'e-Courses';
my $site_url = '../e-course.html';
my $site_name2 = 'e-Books';
my $site_url2 = '../e-books.html';
my $value;
my $field;
my $email;
my $name;

my $query = new CGI;

my $mail_body = '';

foreach $field (sort ($query->param)) {
    foreach $value ($query->param($field)) {
    $mail_body .= "$field: $value\n";
    }
}

if (($email = $query->param('07_email')) and
    ($query->param('07_email') =~ /@/)) {
   if ($name = $query->param('cdsid')) {
    $name =~ s/"//g;
    $sender = "\"$name\" <$email>";
      } else {
    $sender = "$email";
      }
}

open(MAIL, "|$sendmail -oi -t") or die "Can't open pipe to $sendmail: 
+$!\n";
print MAIL "To: $recipient\n";
print MAIL "From: $sender\n";
print MAIL "Subject: e-Books Feedback\n\n";
print MAIL "$mail_body";
close(MAIL) or die "Can't close pipe to $sendmail: $!\n";

#Declare variables for insertion constants
my $plselect = "u"; 
my $updt = "L2e Init";

#Insert the e-Book review and book information into il2e009_bk_review
if ($book_review)
{
my %review = ('il2e009_book_title_x',$title,'il2e009_book_author_x',$a
+uthor,
'il2e009_eval_cds_id_c',$cdsid,'il2e009_pl_select_f',$plselect,'il2e00
+9_post_rev_f',
$post_review,'il2e009_post_name_f', $use_name,'il2e009_review_x',$book
+_review,'il2e009_last_updt_c',$updt);
foreach my $key (keys %review)
{
   print "$key = $review{$key}\n";
}
print "Attempting insert....\n";
$dbh->insert_row('il2e009_bk_review',\%review);
}

$dbh->disconnect;
[download]

Comment on Problems Trying to Send E-Mail and Insert into Database Download Code

Replies are listed 'Best First'.
(jeffa) Re: Problems Trying to Send E-Mail and Insert into Database by jeffa (Bishop) on Aug 18, 2000 at 21:17 UTC
Yes, nice code - I would suggest using a CPAN module to handle email - makes your code more portable. My favorite (so far) is Mail::Sendmail - `use Mail::Sendmail; %mail = ( To => $recipient, From => $sender, Subject => "stuff", Message => "blah", ); sendmail(%mail) or print STDERR "$Mail::Sendmail::error";` [download] Happy coding, Jeff	[reply] [d/l]
Re: Problems Trying to Send E-Mail and Insert into Database by turnstep (Parson) on Aug 18, 2000 at 20:58 UTC
That's very nice code for a "perl newbie". Congratulations. I would suggest adding a error check to the database insert statement, like this: `$dbh->insert_row('il2e009_bk_review',\%review) or die "Insert failed: +$DBI::errstr\n";` [download]	[reply] [d/l]
Re: Problems Trying to Send E-Mail and Insert into Database by isotope (Deacon) on Aug 18, 2000 at 21:04 UTC
My first instinct is that you may be having some timeout issues with the client. You might try sprinkling some print statements throughout your code to see how far it's going. It could be timing out while (or right after) piping to sendmail.	[reply]
RE (tilly) 2: Problems Trying to Send E-Mail and Insert into Database by tilly (Archbishop) on Aug 18, 2000 at 21:16 UTC
My gut says take out the direct call to sendmail and use Mail::Send instead. They may use sendmail today, but a little future-proofing and portability never hurts. Plus it may just solve the problem at hand!	[reply]
Re: Problems Trying to Send E-Mail and Insert into Database by chip (Curate) on Aug 19, 2000 at 04:31 UTC
In fact, I'd really appreciate it if the original author would reformat the question node. It seems to break the wrapping logic of the Monastery, and I can't read my front page any more. :-( -- Chip Salzenberg, Free-Floating Agent of Chaos	[reply]
RE: Re: Problems Trying to Send E-Mail and Insert into Database by Anonymous Monk on Aug 19, 2000 at 21:28 UTC
I apologize. When I originally posted, the text didn't seem to wrap at all (though it has in the past), and I didn't know what to do. How can I edit this to make it easier for people to read?	[reply]
Re: Problems Trying to Send E-Mail and Insert into Database by Jouke (Curate) on Aug 18, 2000 at 23:47 UTC
I cannot find a real error. Maybe indeed it's just a time-out problem. One suggestion for readability of your code would be to rewrite the definition of your hash like this: `my %review = ( 'il2e009_book_title_x' => $title, 'il2e009_book_author_x'=> $author, 'il2e009_eval_cds_id_c'=> $cdsid, 'il2e009_pl_select_f' => $plselect, 'il2e009_post_rev_f' => $post_review, 'il2e009_post_name_f' => $use_name, 'il2e009_review_x' => $book_review, 'il2e009_last_updt_c' => $updt );` [download] I know, it's just a matter of formatting, but maybe (you said you were a newbie) you didn't know you could write it like this...Jouke Visser, Perl 'Adept'	[reply] [d/l] [select]
Re: Problems Trying to Send E-Mail and Insert into Database by Anonymous Monk on Aug 21, 2000 at 07:43 UTC
HI there, here's a little fix. I had that problem once... here's the code: # add the recipient -t <recipient> # I think it's required. open(MAIL, "\|$sendmail -oi -t $recipient") or die "Can't open pipe to $sendmail: +$!\n"; # Cya AgentKelly - Too Bored To Log In =)	[reply]
RE: Re: Problems Trying to Send E-Mail and Insert into Database by merlyn (Sage) on Aug 21, 2000 at 07:52 UTC
That code is dangerous unless you have precise control over `$recipient`. What's worse is that you already have `-t`, which means the message itself will be scanned for the recipients, so you don't (and shouldn't) specify them on the shell command line. -- Randal L. Schwartz, Perl hacker	[reply]
RE: RE: Re: Problems Trying to Send E-Mail and Insert into Database by Anonymous Monk on Aug 22, 2000 at 08:26 UTC
Thanks =) i'm honored cuz u'r the one who replied =) That's a great help for me too =) Cya and a real big thanks AgentKelly	[reply]
Re: Problems Trying to Send E-Mail and Insert into Database by Anonymous Monk on Aug 17, 2001 at 21:12 UTC
I am a perl newbie as well :) When I insert into a database, I prefer to use the sql statement, so I can see my errors more clearly. I would do the following: $insert="insert into my_table values ('foo', 'bar', 'baz')"; $dbh->do($insert); This way you will get an sql error on the command line. Your problem might be as simple as having input that contains an apostrophe or quotation mark which will cause problems in your insert of a string. So when you get your form input, parse through it, to replace a " ' " with a " \' ". Maya Phansalker	[reply]
Re: Problems Trying to Send E-Mail and Insert into Database by Anonymous Monk on Aug 21, 2000 at 07:40 UTC
HI there, here's a little fix. I had that problem once... here's the code: # add the recipient -t <recipient> # I think it's required. open(MAIL, "\|$sendmail -oi -t $recipient") or die "Can't open pipe to $sendmail: +$!\n"; # Cya AgentKelly - Too Bored To Log In =)	[reply]