homepage Welcome to WebmasterWorld Guest from 54.83.133.189
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Website
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library, Charter, Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

    
DB trouble
parrot




msg:4214288
 1:01 pm on Oct 9, 2010 (gmt 0)

Hi guys, I am having severe trouble, maybe it is because I didn't get much sleep last night but I simply cannot see where I go wrong. Any of you have an idea?

I have a file with a few product numbers. I open the file and look the products up in my database. Sounds simple but I only get 1 recor as output?

Here is what I have, apart from a sour head;-)

070101-010
070102-010
070103-010
070104-010
070105-010
070106-010
070107-010
090118-010

# -----------------------------------------------------------
# Load file $ID.txt and generate error when missing
chdir "$Doc_Dir_01/temp";
if (!open(LIN, "$ID.txt")) {
$Err_Num = 1;
$Err_Mess = "Critical system error!<br>missing template '$ID.txt'";
}
else {
foreach $Line (<LIN>){
$Dbh = DBI->connect($DbConnectionInfo,$DbUserid,$DbPasswd);
$Sth = $Dbh->prepare("SELECT * FROM $DbTable WHERE Product_Num = '$Line'");
$Sth->execute();
$Result = $Sth->fetchrow_hashref();
$Product_Num = "$Result->{Product_Num}";
$Group_Num = "$Result->{Group_Num}";
$Product_Name_01 = "$Result->{Product_Name_01}";
$Product_Name_31 = "$Result->{Product_Name_31}";
$Size_X = "$Result->{Size_X}";
$Size_Y = "$Result->{Size_Y}";
$Size_Z = "$Result->{Size_Z}";
$Price_In = "$Result->{Price_In}";
$Price_Out = "$Result->{Price_Out}";
$Desc_01 = "$Result->{Desc_01}";
$Desc_31 = "$Result->{Desc_31}";
$Specs_01 = "$Result->{Specs_01}";
$Specs_31 = "$Result->{Specs_31}";
$Weight = "$Result->{Weight}";
$Company = "$Result->{Company}";
$PictRot = "$Result->{PictRot}";
$Rate = "$Result->{Rate}";
$Sth->finish();
$Dbh->disconnect;

print "$Line - $Product_Num - $Product_Name_31 - $Price_Out<br>";
}
close(LIN);
}

Cheers,

Ton

 

janharders




msg:4214291
 1:19 pm on Oct 9, 2010 (gmt 0)

Maybe it's because you don't remove the line endings from $Line. If the last line of the file does contain a product number (and is not an empty line), that'd explain it.

Also: you reconnect to the database for every line. It's more efficient to only do that once.

give this a try (I hope I didn't make any typos)

chdir "$Doc_Dir_01/temp";
if (!open(LIN, "$ID.txt")) {
$Err_Num = 1;
$Err_Mess = "Critical system error!<br>missing template '$ID.txt'";
}
else {
$Dbh = DBI->connect($DbConnectionInfo,$DbUserid,$DbPasswd);
$Sth = $Dbh->prepare("SELECT * FROM $DbTable WHERE Product_Num = ?");
while(my $Line = <LIN>) {
$Line =~ s/\s+$//gis;
$Sth->execute($Line);
if(my $Result = $Sth->fetchrow_hashref()) {
$Product_Num = "$Result->{Product_Num}";
$Group_Num = "$Result->{Group_Num}";
$Product_Name_01 = "$Result->{Product_Name_01}";
$Product_Name_31 = "$Result->{Product_Name_31}";
$Size_X = "$Result->{Size_X}";
$Size_Y = "$Result->{Size_Y}";
$Size_Z = "$Result->{Size_Z}";
$Price_In = "$Result->{Price_In}";
$Price_Out = "$Result->{Price_Out}";
$Desc_01 = "$Result->{Desc_01}";
$Desc_31 = "$Result->{Desc_31}";
$Specs_01 = "$Result->{Specs_01}";
$Specs_31 = "$Result->{Specs_31}";
$Weight = "$Result->{Weight}";
$Company = "$Result->{Company}";
$PictRot = "$Result->{PictRot}";
$Rate = "$Result->{Rate}";
print "$Line - $Product_Num - $Product_Name_31 - $Price_Out<br>";
}

}
$Sth->finish();
$Dbh->disconnect;
close(LIN);
}


$Line =~ s/\s+$//gis;
removes all whitespaces from the end of the line (including spaces ... but since product numbers generally don't have trailing spaces, that's probably good to handle typos).

also note
$Sth = $Dbh->prepare("SELECT * FROM $DbTable WHERE Product_Num = ?");
$Sth->execute($Line);


I've used ?, a placeholder. When executing, the parameter that is passed, will replace the ? but will automatically be quoted and escaped.

Your SQL would fail (and put your DB at risk) if the file contained a line that contains a '

parrot




msg:4214296
 1:51 pm on Oct 9, 2010 (gmt 0)

Jan, works like a dream, Thank you so much;-) Nederlands? Hier wel.

Ciao en fijn weekend.

Ton

janharders




msg:4214319
 2:38 pm on Oct 9, 2010 (gmt 0)

Glad I could help.

> Nederlands?

Nope, but in the neighborhood: Hamburg, Germany. Have a great weekend yourself.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved