PERL Beginners - MySQL in a for loop

Author

MySQL in a for loop

David Foley

2005-07-27, 5:02 pm

Hi Guys,
Can you please look at the below script. The SQL query
works on it's own in separate script. But not when it is put into the
"for" loop in this script
.. Any ideas??

Thanks,
David
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
#!/usr/bin/perl -w

#Reduce scripting errors
use strict;

#Call relevant modules
use Net::POP3;
use DBI;

#Connect to POP3 server
my $pop3server = Net::POP3->new('****server****');

#Login to POP3 server
$pop3server->login('****user****', '****pass****');

#How many messages there is
my $lastm = ($pop3server->popstat)[0];

#Connect to MySQL database on localhost
my $MySQL = DBI->connect
("DBI:mysql:NOD32:localhost","****","***************") or die " Could
not connect to MySQL database on localhost\n";

#Get and process mail
for my $messageID (90){

my $MFH = $pop3server->getfh($messageID);

#Extract values
my $name = '';
my $refno = '';
my $userpass = '';
my $wtable = '';

while(<$MFH> ) { if ( $_ =~ m/UserName:Password=/ ) {(undef, $userpass) =
split /=/, $_;}
if ( $_ =~ m/person:/ ) {(undef, $refno) = split /:/, $_;}
if ( $_ =~ m/Clients_name:/ ) {(undef, $name) = split /:/, $_;}

}

if($refno =~ /NRS/) {$wtable = 'RSorders2005';} else{ $wtable =
'SUorders2005';};

chomp($userpass);
$MySQL->quote($userpass);
$refno =~ s/^\s//;

#SQL Query 1
my $SQLQ1a = "UPDATE $wtable SET UsernamePassword = '$userpass' WHERE
RefNo = '$refno'";
print "$SQLQ1a";

#SQL Query 1 HANDLE
my $SQLQ1 = $MySQL->prepare($SQLQ1a);

#Exexute SQL Query 1
$SQLQ1->execute();

}

--------------------------------------------------------------------------------------------------------------------------------------------------------

David Van Ginneken

2005-07-27, 5:02 pm

Few things that I can see. I'm sure others will give you more ideas.

On 7/27/05, David Foley <david@reflex.ie> wrote:
> Hi Guys,
> Can you please look at the below script. The SQL query
> works on it's own in separate script. But not when it is put into the
> "for" loop in this script
> . Any ideas??
>=20
> Thanks,
> David
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-------------
> #!/usr/bin/perl -w
>=20
> #Reduce scripting errors
> use strict;
>=20
> #Call relevant modules
> use Net::POP3;
> use DBI;
>=20
> #Connect to POP3 server
> my $pop3server =3D Net::POP3->new('****server****');
>=20
> #Login to POP3 server
> $pop3server->login('****user****', '****pass****');
>=20
> #How many messages there is
> my $lastm =3D ($pop3server->popstat)[0];
>=20
No error checking up to this point. What happens if the connection to
the server or authentication fails?

> #Connect to MySQL database on localhost
> my $MySQL =3D DBI->connect
> ("DBI:mysql:NOD32:localhost","****","***************") or die " Could
> not connect to MySQL database on localhost\n";
>=20
> #Get and process mail
> for my $messageID (90){
What exactly is this supposed to do? If you just run this
for my $messageID (90){
print $messageID . "\n";
}
It outputs nothing..
>=20
> my $MFH =3D $pop3server->getfh($messageID);
What happens when there is an error getting the message here?
>=20
> #Extract values
> my $name =3D '';
> my $refno =3D '';
> my $userpass =3D '';
> my $wtable =3D '';
>=20
> while(<$MFH> ) { if ( $_ =3D~ m/UserName:Password=3D/ ) {(undef, $userpass=
) =3D
> split /=3D/, $_;}
> if ( $_ =3D~ m/person:/ ) {(undef, $refno) =3D split /:/, $_;}
> if ( $_ =3D~ m/Clients_name:/ ) {(undef, $name) =3D split /:/, $_=
;}
$name is not used anywhere. Do you need the above line?
>=20
> }
>=20
> if($refno =3D~ /NRS/) {$wtable =3D 'RSorders2005';} else{ $wtable =3D
> 'SUorders2005';};
>=20
> chomp($userpass);
> $MySQL->quote($userpass);
The above line does not do what you expect it to do.

> $refno =3D~ s/^\s//;
>=20
> #SQL Query 1
> my $SQLQ1a =3D "UPDATE $wtable SET UsernamePassword =3D '$userpass' WHER=
E
> RefNo =3D '$refno'";
> print "$SQLQ1a";
>=20
>=20
> #SQL Query 1 HANDLE
> my $SQLQ1 =3D $MySQL->prepare($SQLQ1a);
>=20
> #Exexute SQL Query 1
> $SQLQ1->execute();
>=20
> }
>=20
>=20
You only are calling 2 different database statements. Would it make
sense to prepare them on the top then call them in the for loop?=20
Something like:
my $RSOrders =3D $MySQL->prepare("update RSorders2005 set
UsernamePassword =3D ? where RefNo =3D ?");
my $SUOrders =3D $MySQL->prepare("update SUorders2005 set
UsernamePassword =3D ? where RefNo =3D ?");

Then in the for loop..
if($refno =3D~ /NRS/) {
$RSOrders->execute($userpass, $refno) or die "Unable to update
tables: ". $MySQL->errstr."\n";
$RSOrders->finish;
} else{=20
$SUOrders->execute($userpass, $refno) or die "Unable to update
tables: ". $MySQL->errstr."\n";
$RSOrders->finish;
}

HTH

Charles K. Clarkson

2005-07-27, 10:00 pm

David Van Ginneken <mailto:davevg@gmail.com> wrote:

: : #Get and process mail
: : for my $messageID (90){

: What exactly is this supposed to do? If you just run this

: for my $messageID (90){
: print $messageID . "\n";
: }
: It outputs nothing..

Actually, it outputs "90\n". There is no reason why
the list in a 'for' loop cannot iterate over just one item.
In fact, it can make testing easier by allowing us to
substitute a list with just one test item.

foreach my $item ( @very_large_array ) {
print "$item\n";
}

Can be replaced with this for testing:

foreach my $item ( $single_test_item ) {
print "$item\n";
}

HTH,

Charles K. Clarkson
--
Mobile Homes Specialist
254 968-8328

David Van Ginneken

2005-07-28, 9:07 am

On 7/27/05, Charles K. Clarkson <cclarkson@htcomp.net> wrote:
> David Van Ginneken <mailto:davevg@gmail.com> wrote:
>=20
> : : #Get and process mail
> : : for my $messageID (90){
>=20
> : What exactly is this supposed to do? If you just run this
>=20
> : for my $messageID (90){
> : print $messageID . "\n";
> : }
> : It outputs nothing..
>=20
> Actually, it outputs "90\n". There is no reason why
> the list in a 'for' loop cannot iterate over just one item.
> In fact, it can make testing easier by allowing us to
> substitute a list with just one test item.
You are absolutely right. Teaches me to glance really fast at the
output. ;-) I also made a typo in the last ->finish command. But he
can get the idea.
>=20
> foreach my $item ( @very_large_array ) {
> print "$item\n";
> }
>=20
> Can be replaced with this for testing:
>=20
> foreach my $item ( $single_test_item ) {
> print "$item\n";
> }
>=20
>=20
> HTH,
>=20
> Charles K. Clarkson
> --
> Mobile Homes Specialist
> 254 968-8328
>=20
>=20
> --
> To unsubscribe, e-mail: beginners-unsubscribe@perl.org
> For additional commands, e-mail: beginners-help@perl.org
> <http://learn.perl.org/> <http://learn.perl.org/first-response>
>=20
>=20
>

Sponsored Links