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


Hey,

I have an access database that contains several id's that
needs to be e-mailed to several different people. I wrote a
script to retrieve the id's from the database and e-mail
them to the specific user. Unfortunately, it's not working.
i have a file in which i'm writing the message to and then
e-mailing it. But, the program is trying to open or create
all the files at the same time, and then it can't close them.
what i want to do is create one file and write to it, then
e-mail that file, and so on until all the ids have been e-mailed.

Here is what i have thus far:

#!/perl/bin/perl
use Win32::ODBC;
use win32;
use CGI qw(:standard);

### Connect to the database
$db=new Win32::ODBC("DSN=bisdata;UID=;PWD=");

### Sql statement
my $statement="SELECT web_id, country, mail_alias FROM lexnex_ids";

### report connection status
if (!$db) {
    print "\nunable to connect to database!";
    die();
}
    
$num=0;

### Test to see if the person is in the US, if so send him
### an e-mail with his ID
$db->Sql("$statement");
while ($db->FetchRow()) {
    my (%dbrow) = $db->DataHash();
    my $place=$dbrow{'country'};    
    @tester=keys %dbrow;

    if ($place eq US){

    ### Sql statement
    my $statement="SELECT web_id, country, mail_alias FROM lexnex_ids"
+;

    ### Retrieve the records and place it in a hash array
    $db->Sql("$statement");
    while ($db->FetchRow()) {
        my (%dbrow) = $db->DataHash();
        my $lexid=$dbrow{'web_id'};
        my $lexuser=$dbrow{'mail_alias'};    
        @tester=keys %dbrow;
        
        ### Needed to send e-mail
        $mailprog='d:\WEBSVR\blat\blat.exe';
        $recipient='-t $lexuser@comp.com -f kilo@comp.com';
        $nice="d:/WEBSVR/test/Completed/ProgCom/CdromDB_Files/lexnex_i
+d$num.txt";
        $subject="-s Your Nexis Universe ID";
        
        ### Opens the file to place content to be e-mailed
        open (F, ">$nice");

        print F "Your User ID is: $lexid.  Your password is your last 
+name.\n\n";

        close(F) || print p("can't close $nice");

        $commandline="$mailprog $nice $recipient $subject -q";
        system("$commandline");
        
        $num = $num + 1;
        
       }
     }
    }



      ### If there is an error, print it
     print "\n";
     print $db->Error();

     ### Close ODBC connection
     $db->Close();


Thanks,
Kiko

[download]

Replies are listed 'Best First'.
(chromatic) Re: Mass Mail
by chromatic (Archbishop) on Jul 12, 2000 at 21:19 UTC
    Nothing immediately jumps out at me as being wrong, but there are a few things that could use some error checking. First, you really ought to be using -w and strict. That'll catch typos and dangerous constructs and will save you plenty of debugging time.

    Even if you don't use them here, you absolutely must check to see if your open call succeeded: 

    ### Opens the file to place content to be e-mailed
open (F, ">$nice") || die "Can't open $nice: $!";

    [download]
    Since we can't tell right now if that worked, your program could be correct all over except for an open path or something... this is the first place I'd look. Same goes for the system call, as a matter of fact. (And on there, it's more secure to pass it a list of options rather than a string. You can avoid shell interpolation that way -- a good thing to do. However, as you appear to be on a Windows machine, the amount of damage someone can do with the default shell is lower than on Unix.)

    As a side note, and having not worked with Access very much, I wonder why you don't move the 'country eq US' comparison into your SQL statement. A good relational database will be able to perform this faster than you can in Perl: 

    ### Sql statement
 my $statement="SELECT web_id, mail_alias FROM lexnex_ids WHERE countr
+y = 'US'";

    [download]
    As a nice side effect, you don't have to do two DB queries, which may solve some other things (I'm not sure that issuing a new query to the Perl ODBC object while fetching from an older query is anything but trouble).

    I hope this helps.

[reply]
[d/l]
[select]
Re: Mass Mail
by ZZamboni (Curate) on Jul 12, 2000 at 21:59 UTC
    Apart from what chromatic and splinky have said, one thing that strikes me as possibly wrong (but not related to your question) is the lack of quotes around the subject. I'm not familiar with quoting rules in DOS/Windows, but it may be better if you assign like this: 
    $subject='-s "Your Nexis Universe ID"';

    [download]
    so that the whole string is considered as the subject and not only "Your".

    --ZZamboni

[reply]
[d/l]
Re: Mass Mail
by splinky (Hermit) on Jul 12, 2000 at 21:10 UTC
    You never check the return from open. You also never check the result of the system() call. Finally, you never delete the file after mailing it. I don't know if that's on purpose or not, but if not, you should unlink it.

    Other than that, the program looks like it should work.

    *Woof*

[reply]
[d/l]
[select]
Re: Mass Mail
by Kiko (Scribe) on Jul 13, 2000 at 01:49 UTC 
    
I'm i setting the path right? This is what i have:

$mailprog='d:\WEBSVR\blat\blat.exe';

but i get an error that says "the system cannot find the
path specified"

Thanks for your help,
Kiko

    [download]
[reply]
[d/l]

      Ooops - how could that one have been missed... It's one of my favourite mistakes as well - Perl treats the "\" character as a special character for creating stuff like \n (newline) and \t (tab). To create an actual "\" in a string, you have to use "\\". So the correct statement would be :

        $mailprog='d:\WEBSVR\blat\blat.exe';
  warn "Couldnīt locate the mail program at $mailprog\n" unless -x $ma
+ilprog;

      [download]

      Update : chromatic told me that single quotes don't interpret the backslash - and he is right. My shell (bash played the nasty trick of interpretation on me, which is even more a symptom of my difficulties with backslash interpretation ;)

[reply]
[d/l]
        It's in single quotes, thus, there's no interpolation: 
        $ perl
print '\d\thi\n';
\d\thi\n[chromatic@firewheel ]$

        [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom