On Mon, 2005-07-18 at 11:53, Wiggins d'Anconia wrote:
> Charles Farinella wrote:
> > I'm sure this is very simple and I am overlooking something. I want to
> > read a list of bad email addresses from a file and remove them from my
> > database.
> >
> > If I print $_, the email addresses are correct, if I try to remove them
> > from the db I get errors on just the characters before the @.
> >
>
> Are you sure those errors aren't coming from the DB?
I'm getting this:
==
DBD::Pg::st execute failed: ERROR: column "mmartins" does not exist at
delBadEmail.pl line 23, <INFILE> line 378.
==
If I put the address, '[EMAIL PROTECTED]' in the script, that works
fine.
If I insert the '\' in front of @ in $_ it throws this:
==
DBD::Pg::st execute failed: ERROR: syntax error at or near "\" at
character
127 at delBadEmail.pl line 23, <INFILE> line 378 (#7)
==
I'm confused as to what it wants. :-(
The solution John Moon sent me didn't work, it send me a bound variable
error.
> > Here is what I have:
> >
> > ==
> > #!/usr/bin/perl -w
> >
> > use strict;
> > use DBI;
> > use DBD::Pg;
> >
> > my $infile = $ARGV[0];
> >
> >
> > open( INFILE, "<$infile" );
> > while( <INFILE> ) {
> > my $dbh = DBI->connect("dbi:Pg:dbname=*, "*", "*") || die;
> >
> > #s/\@/\\@/;
> > #print $_;
> >
> > my $sth = $dbh->prepare("UPDATE
> > table
> > SET
> > email = ''
> > WHERE
> > email = $_ ") || die;
>
> The above value needs to be quoted for the DB not Perl. But it would be
> much more efficient to use placeholders and move your statement
> preparation outside of the loop and then provide the e-mail address to
> the execute. This will take care of your quoting problems and be more
> efficient.
>
> >
> > $sth->execute;
> >
> > finish $sth;
> > $dbh->disconnect;
> > }
> >
> > close( INFILE );
> > ==
> >
>
> Only do things in the loop that must be done in the loop. Reconnecting,
> repreparing, and disconnecting from the DB is a lot of unnecessary work.
>
> perldoc DBI
>
>
> --Untested--
> #!/usr/bin/perl -w
>
> use strict;
> use DBI;
> use DBD::Pg;
>
> my $infile = $ARGV[0];
>
> # no reason to reconnect for every line
> my $dbh = DBI->connect("dbi:Pg:dbname=*, "*", "*") || die;
>
> # statement should only be prepared once
> # note use of placeholder ? character
> my $sth = $dbh->prepare("UPDATE table SET email = '' WHERE email = ? ")
> || die;
>
> open INFILE, "<$infile" or die "Can't open file for reading: $!";
> while ( <INFILE> ) {
> $sth->execute( $_ );
> }
> close INFILE;
>
> $sth->finish;
> $dbh->disconnect;
--
Charles Farinella
Appropriate Solutions, Inc. (www.AppropriateSolutions.com)
[EMAIL PROTECTED]
603.924.6079
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
