SQL While loop problem.

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

I have a script that gets vehicle info from our database to print onto a request screen based on a stock no entered. it also gets the names of Images stored against each vehicle. i want to display each image on the page under the vehicle details. I have used a WHILE loop to achieve this

The problem i have is that when it prints to the screen it always misses one of the images. The SQL call is ok, i.e. if i run it in SQL it will return say 5 image names. but if i run the same statement through the perl script it only returns 4. it always misses the first row that is returned.

There are some other SQL calls that are working it is just the image calls and the while loop i am having issues with. here is the complete code...


#!/usr/bin/perl

use CGI::Carp qw(fatalsToBrowser);
use DBI;


&parsedata; 
@ARGV = split(/\\*\&/, $ENV{'QUERY_STRING'});
$ARGV[0] =~ s/\W//g;

######### when a button is pressed ####################
if ($form{'action'} eq 'imglookup') { &imglookup; }

######### when theres a command in the url ###########
elsif ($ARGV[0] eq 'imglookup') { &imglookup; }

####################
sub imglookup {

print "Content-type: text/html\n\n";

$external = 0;

if (lc($ENV{'SERVER_NAME'}) =~ ".co.uk"){$external = 1};

if ($external eq 1){

$imgloc = "http://$ENV{'SERVER_NAME'}:1589/dhimages";}
else {$imgloc = "http://$ENV{'SERVER_NAME'}/dhimages";}

 

if ( $ARGV[1] ne ""){$stockno = $ARGV[1];}

else {$stockno = "$form{'STOCKNO'}";}

#$stockno = $form{'STOCKNO'};

my $dbs = "DBI:ODBC:DRIVER={SQL Server};SERVER={dms-svr-1};DATABASE={f
+rontiersql}"; 
my ($username, $password) = ('xxx', ''); 
 
 ######################Vehicle details#################
my $dbh = DBI->connect($dbs, $username, $password) or die "$DBI::errst
+r\n"; 


my $sth = $dbh->prepare('SELECT SalvageVehicles.RegNo, SalvageVehicles
+.RegYear, Manufacturers.Name, Models.Name, SalvageVehicles.CC, Salvag
+eVehicles.YardNo, SalvageClears.ID, BodyType.ID
FROM ((BodyType RIGHT JOIN SalvageVehicles ON BodyType.ID = SalvageVeh
+icles.BodyTypeID) LEFT JOIN SalvageClears ON SalvageVehicles.ID = Sal
+vageClears.SalvageID) LEFT JOIN (Models LEFT JOIN Manufacturers ON Mo
+dels.ManuID = Manufacturers.ID) ON SalvageVehicles.ModelID = Models.I
+D
WHERE (((SalvageClears.ID)='.$stockno.'));') 

or die "Couldn't prepare statement: " . $dbh->errstr;


$sth->execute() or die "execute failed: " . $sth->errstr();

@row = $sth->fetchrow_array();

($reg, $year, $make, $model, $cc, $nsgno, $stocknox, $bodytypeid) = @r
+ow;

######################################


##########Body type#################

my $dbh2 = DBI->connect($dbs, $username, $password) or die "$DBI::errs
+tr\n"; 
my $sth2 = $dbh2->prepare('Select [bodytype].[desc] from bodytype wher
+e id ='.$bodytypeid.' ;')

or die "Couldn't prepare statement: " . $dbh2->errstr;

$sth2->execute() or die "execute failed: " . $sth2->errstr();

@row2 = $sth2->fetchrow_array();
($bodytype) = @row2;
#############################################

########Sub location################
my $dbh3 = DBI->connect($dbs, $username, $password) or die "$DBI::errs
+tr\n"; 

my $sth3 = $dbh3->prepare('SELECT SubLocations.Name
FROM (SalvageRecoveries RIGHT JOIN (SalvageVehicles LEFT JOIN SalvageC
+lears ON SalvageVehicles.ID = SalvageClears.SalvageID) ON SalvageReco
+veries.SalvageID = SalvageVehicles.ID) LEFT JOIN SubLocations ON Salv
+ageRecoveries.SubLocation = SubLocations.ID
WHERE (((SalvageClears.ID)='.$stockno.'));')

or die "Couldn't prepare statement: " . $dbh3->errstr;

$sth3->execute() or die "execute failed: " . $sth3->errstr();

@row3 = $sth3->fetchrow_array();
($sublocation) = @row3;
################################

#######images##################
my $dbh4 = DBI->connect($dbs, $username, $password) or die "$DBI::errs
+tr\n"; 

my $sth4 = $dbh4->prepare('SELECT SalvageVehicles.YardNo, Images.FileT
+ype, Images.ID, SalvageClears.ID, Images.DateEntered
FROM Images RIGHT JOIN (SalvageVehicles LEFT JOIN SalvageClears ON Sal
+vageVehicles.ID = SalvageClears.SalvageID) ON Images.SalvageID = Salv
+ageVehicles.ID
WHERE (((Images.FileType)=\'jpg\') AND ((SalvageClears.ID)='.$stockno.
+'));')



or die "Couldn't prepare statement: " . $dbh4->errstr;

$sth4->execute() or die "execute failed: " . $sth4->errstr();

@row4 = $sth4->fetchrow_array();

($nsgno, $type, $id, $stocknox, $date) = @row4;

#######################################

#########Output###########################

print "<strong>Stock No - $stocknox : $make $model $bodytype $reg NSG 
+Ref - $nsgno - Salvage Rack Location $sublocation  </strong><br>";


while (@row4 = $sth4->fetchrow_array()) {

($nsgno, $type, $id, $stocknox, $date) = @row4;

print "@row4<br>";

@date = split(/-/, $date);

$year = substr($date, 0,4);
$mth = substr($date, 5,2);
$day = substr($date, 8,2);

if ($mth eq "01") {$monnm = "January"};
if ($mth eq "02") {$monnm = "February"};
if ($mth eq "03") {$monnm = "March"};
if ($mth eq "04") {$monnm = "April"};
if ($mth eq "05") {$monnm = "May"};
if ($mth eq "06") {$monnm = "June"};
if ($mth eq "07") {$monnm = "July"};
if ($mth eq "08") {$monnm = "August"};
if ($mth eq "09") {$monnm = "September"};
if ($mth eq "10") {$monnm = "October"};
if ($mth eq "11") {$monnm = "November"};
if ($mth eq "12") {$monnm = "December"};

$sep = '/';
$jpg = "$id.$type";


$imgfile = $imgloc.$sep.$year.$sep.$monnm.$sep.$jpg;

print "<img src=$imgfile border=0 width=400 height=300>";

print "<br>"


}

print "</html>";

$sth->finish(); 
$dbh->disconnect();

$sth2->finish();
$dbh2->disconnect();

$sth3->finish();
$dbh3->disconnect();

$sth4->finish();
$dbh4->disconnect();
}

## Parse data 

sub parsedata {
    $buffer = "";
    read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
    @pairs=split(/&/,$buffer);
    foreach $pair (@pairs)
    {
        @a = split(/=/,$pair);
        $name=$a[0];
        $value=$a[1];
        $value =~ s/\+/ /g;
        $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
        $value =~ s/~!/ ~!/g;
        $value =~ s/[\n\r]/ /sg;
        $value =~ s/\[\]//g;
        push (@data,$name);
        push (@data, $value);
    }
    %form=@data;
    %form;
}
################
[download]

One thing i have found while trying to resolve this problem is that if i do a


print @row4;
[download]

outside the while loop it will print the missing row of data. which is why i think it might be the while loop that is wrong.

I am not 100% with PERL so please excuse any mistake you might think are silly.
Thanks
Michael

Comment on SQL While loop problem. Select or Download Code

Replies are listed 'Best First'.
Re: SQL While loop problem. by DStaal (Chaplain) on Jan 28, 2011 at 13:53 UTC
Relevant lines from your code: `@row4 = $sth4->fetchrow_array(); ($nsgno, $type, $id, $stocknox, $date) = @row4; ####################################### #########Output########################### print "<strong>Stock No - $stocknox : $make $model $bodytype $reg NSG +Ref - $nsgno - Salvage Rack Location $sublocation </strong><br>"; while (@row4 = $sth4->fetchrow_array()) {` [download] You fetch the first row before you enter the while loop, and then enter the while loop by fetching the next row. This will cause your problem. ;) You will either need to remove the `@row4 = $sth4->fetchrow_array();` that is outside the loop (and therefore have to deal with that `print` statement inside the loop somehow) or print the first image outside the loop while you have the data. Another option would be to change the while loop to: `while (@row4 != 0) { ... # Your code here. @row4 = $sth4->fetchrow_array(); }` [download] Thereby only doing the 'fetch' at the end of the loop, and just checking for success.	[reply] [d/l] [select]
Re^2: SQL While loop problem. by dmsparts (Sexton) on Jan 28, 2011 at 14:06 UTC
Thanks for the help, got rid of the initial fetch and hey presto. Regards. Michael	[reply]
Re: SQL While loop problem. by andreas1234567 (Vicar) on Jan 28, 2011 at 13:44 UTC
You have `@row4 = $sth4->fetchrow_array();` both before and as part of the `while` loop conditional. What happens if you take the former away? -- No matter how great and destructive your problems may seem now, remember, you've probably only seen the tip of them. [1]	[reply] [d/l] [select]
Re: SQL While loop problem. by moritz (Cardinal) on Jan 28, 2011 at 13:56 UTC
Unrelated to your actual questions, there are two easy things you can improve in your code: If you're doing an error check after each database operation anyway, you might just as well set the `RaiseError => 1` option during connect. That way you save a whole lot of redundant error check code. `if ($mth eq "01") {$monnm = "January"}; if ($mth eq "02") {$monnm = "February"}; if ($mth eq "03") {$monnm = "March"}; if ($mth eq "04") {$monnm = "April"}; if ($mth eq "05") {$monnm = "May"}; if ($mth eq "06") {$monnm = "June"}; if ($mth eq "07") {$monnm = "July"}; if ($mth eq "08") {$monnm = "August"}; if ($mth eq "09") {$monnm = "September"}; if ($mth eq "10") {$monnm = "October"}; if ($mth eq "11") {$monnm = "November"}; if ($mth eq "12") {$monnm = "December"};` [download] I'd write that as `my @months = qw/January February March April May June July August Sept +ember October November Decemeber/; # arrays are zero-indexed, so subtract one to # get from the month number to the array index: $monnm = $months[$mnth - 1];` [download] Perl 6 - second systems done right	[reply] [d/l] [select]
Re: SQL While loop problem. by marto (Cardinal) on Jan 28, 2011 at 14:18 UTC
Hi there, in addition to the advice given by others, I suggest you take a look at Placeholders from the DBI documentation. Also you could use HTML::Template to seperate your HTML code from your Perl code.	[reply]
Re: SQL While loop problem. by mje (Curate) on Jan 28, 2011 at 13:42 UTC
On line 104 you read a row and do nothing with it (outside the while loop).	[reply]
Re^2: SQL While loop problem. by DStaal (Chaplain) on Jan 28, 2011 at 13:54 UTC
Actually, he does do something with it. Just not with all of it...	[reply]
Re: SQL While loop problem. by Anonymous Monk on Jan 28, 2011 at 13:46 UTC
any mistake you might think are sily mummy loves me, she said so	[reply]
Re^2: SQL While loop problem. by sundialsvc4 (Abbot) on Jan 28, 2011 at 14:10 UTC
ROTFL ... Thanks. I needed that ... Mental images of a skeleton on the beach, sipping a pi�a colada in the rain ...	[reply]


laziness, impatience, and hubris
	PerlMonks