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

In reply to SQL While loop problem. by dmsparts

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.