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

Hello all, I'm new to perlmonks.org and I have a question regarding MS Access. I have a program that extracts e-mail addresses from web pages and stores them in an Access database as well as a file. When the program ends, the file contains about 7 times more addresses than Access. How do I correct this? Here is the code snippet that I have so far: 
use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';

sub extractEmail
{
    my($hitPage) = @_;

    my $email;
    my $emailPresent = 0;
    my $webTitle = HTML::TokeParser->new($hitPage); 
    my $titlePage = "";
    
    my $Conn = Win32::OLE->new("ADODB.Connection");
    my $RecSet = Win32::OLE->new("ADODB.Recordset");
    my $DBTable = "Addresses";
    my $DBFile  = "g:\\PO system\\emailaddresses.mdb";
    my $DSN = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb
+)};DBQ=$DBFile";
                    
    #Opens the connection to the database
    
    $Conn->Open($DSN);
    $RecSet->Open($DBTable, $Conn,adLockOptimistic, adOpenDynamic);   
+     
    
    #The current page is searched for any <MAILTO:> tags and extracts 
+email addresses and stores them in a database and file 

while($$hitPage =~ /mailto:.*?\.(com|ca|net)/g)
{
    $email = $&;
    $email =~ s/mailto://g;
    $titlePage = $webTitle->get_trimmed_text("/title");
    print OUTFILE $email, "\t\t", $webTitle->get_trimmed_text("/title"
+);
    $RecSet->AddNew(["Email Address", "Website Title"], [$email, $titl
+ePage]);
    $emailPresent = 1;
    }
    
    $RecSet->Close();
    $Conn->Close();

    return $emailPresent;    
}

[download]
This whole code is inside a loop. I don't think the constant opening and closing of the database is the problem (although it slows the program down), but I could be wrong. Thanks for any help!

Replies are listed 'Best First'.
Re: Adding data to an access database
by Jenda (Abbot) on Jan 07, 2003 at 20:56 UTC

    Most probably some of the $RecSet->AddNew(...) failed. Does the method return some kind of success/failure status? And aren't you supposed to call something like $RecSet->Update() or something if you use this insane way of adding data?

    Basicaly you'd better forget about ADO, change the script to use DBI and DBD::ODBC and use the normal way of adding data:

    use DBI;
$db = DBI->connect("dbi:ODBC:$DSN");
$insert = $db->prepare('INSERT INTO Addresses ("Email Address", "Websi
+te Title") VALUES (?, ?)')
 or die "The INSERT statement is wrong: ".$db->errstr()."\n";

while (...) { 
 ...
 $insert->execute($email, $titlePage)
  or die "Failed to insert the email $email : ".$db->errstr."\n";
}

    [download]

    Jenda

[reply]
[d/l]
[select]
      Thanks Jenda, I'll give that a try
[reply]
        Thank you for your help! I got it to work!
[reply]
Re: Adding data to an access database
by MarkM (Curate) on Jan 07, 2003 at 20:42 UTC

    I believe that most programs used to scan email address from web pages, news groups, or any other forum are used to do evil in this world. Therefore, I regret that I cannot help you, as even if you are not a spammer, other people reading this forum may be.

    You would see the same response from me if you asked how to create a virus, or a worm, or ...

[reply]
      Update: As someone in the CB mentioned, what if the original poster had not mentioned this was being used to extract email? His problem is essentially a database problem, not an email extraction problem.

      Quick, delete all nodes on matching an email address! They might be used for spamming! Oh crap, delete all the nodes on regular expressions, they're used for finding email addresses! You see where this goes?

      End Update

      Your logic is similar to:

      "Most guns are used to do evil in this world. Therefore, I can not help you learn about guns."

      or pehaps this is closer to home:

      "Most email viruses are written in Visual Basic*. Therefore I can not help you learn about Visual Basic."

      * I have no idea if this is true. Used for example only.

      Unless I am gravely mistaken, this is a site for sharing a love of coding, perl in particular, and helping others. Not personal ethics.

      ibanix

      $ echo '$0 & $0 &' > foo; chmod a+x foo; foo;
[reply]
[d/l]
        Howdy!

        That the slope becomes slippery does not in and of itself mean we cannot look at what was asked and frown. Raziel lays out a scenario that looks suspiciously like address harvesting a la spammers. So far, Raziel has not offered any further explanation of his/her intentions that might clarify this point.

        In my mind, this question skates right at the edge. Given that useful conversation has occurred, I'm personally inclined toward leavgin this post stand.

        The worst thing about including the "extracting emiail addresses" function is that it obscured the actual issue. Other posters have spoken to the technical issue at hand.

        yours,
        Michael

[reply]

        Actually my logic is that I hate SPAM, and since I can see no sensible reason for the code other than SPAM, my morals do not allow me to contribute to something that may be used for SPAM. The problem with your logic is that guns have several 'reasonable' uses. What use do you see for SPAM?

        As a director on the board of a community-run ISP, I have been forced to recognize that SPAM is one of the key reasons that discourage people from using email.

        If you can come up with a reasonable use to harvest email addresses from webpages, that is not SPAM related, I will agree that your comparison to guns is correct. In any case, I have the right to make a moral stand, whether or not you agree with my stand. Down vote me if you think that my opinion should be held against me.

[reply]
      How do you know this guy is a spammer?

      I can think of a lot of reasons why someone would want to do this
      Try not to be so closed minded.
      There real evil in this post is that they are using MS Access!
      and OLE! yuck!
[reply]

        There's nothing wrong in using Access if you happen to use Windows and do not need to access the Access database from the web (that is if you do not need to make many concurrent connections). It allows you to create nice forms and reports easily, it's something Windozers can feel at home with, ...

        Using ADO from Perl on the other hand IS a little strange. It adds too much overhead. IMHO of course.

        On the other hand I can't think of a decent reason to extract emails from webpages. There are quite a few for extracting URLs, but emails?

        Jenda

[reply]
Re: Adding data to an access database
by Raziel (Initiate) on Jan 08, 2003 at 20:53 UTC
    I feel I owe an explanation to everyone. I didn't want to ask this question as I knew it could result in some "controversy". I would like to thank everyone for keeping an open mind on my question. To be honest, the program I am creating will be used for, well, spam. This, however is very targeted spam. I am a co-op student working at a business that does DNA sequencing and synthesis. The e-mails we will send are to businesses we believe could use some of our DNA services. We do not intend to send it out to millions of people, just selected businesses. If anyone else outside of our target zone is hit, it is an accident and was never intended. Thank you for your time all!
[reply]
      Just remember - spam is all about consent rather than content. If you don't have proof of consent and one of the recipients complains, then you stand to lose your internet connectivity, not to mention the reputation hit your company will take.

      I'd suggest that you lurk in the usenet group news.admin.net-abuse.email to see the effects on both the recipients and originators of unsolicited commercial email.

      Clare

[reply]
      Aw, and I had such high hopes....

      Remember, spam is "unsolicited, commerical email". If your reciepents, even if it's only one, haven't asked for this mail, and you're trying to sell them a product or service, it's SPAM.

      But I still maintain this code could be used for legitmate purposes :-P

      Cheers,
      ibanix

      $ echo '$0 & $0 &' > foo; chmod a+x foo; foo;
[reply]
[d/l]

Back to Seekers of Perl Wisdom