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

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]
      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]

      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]

      ROTFL ...

      Thanks.   I needed that ...   Mental images of a skeleton on the beach, sipping a piņa colada in the rain ...

Back to Seekers of Perl Wisdom