Perl subroutine with mysql not working

There's nothing inherently wrong with your if statements, therefore we probably need to see how $COM and $pass are being set.   I'd suggest printing them out prior to that block... such as

print STDERR "<".$COM."> <".$pass.">\n";

My guess without seeing additional code is you're not chomp'ing the newline characters off the input.

--Todd

Hi Todd - sorry I guess I should've said the statements are working. When I pass for instance $COM as AAC it does print out COM: AAC.  Every statement is run but when I get to the execute it dies for some reason.  However when I take the if statement out it seems to run fine.  It's bloody weird.  I may have to look at it tomorrow with fresh eyes...

I even used the same SQL statement thinking it was something with the statement.

ah.. sorry I misunderstood your question.  It does look odd.   If you add something like

or die "Can't prepare statement:  $dbh->errstr\n"     to the end of the prepare line

and

or die "Can't execute the query: $sth->errstr\n"     to the end of execute line

does it give you more info?

Maybe $sortorder doesn't have what you expect.  You should check for errors for both the prepare and execute statements, and if either has an error, display it.

  my $sth = $dbh->prepare(qq{ select Tag, Dealer, YrMade, Make, Model, ExtCol, Price, FinanceAvail, DateAdded, DRF FROM APSVehicles WHERE AutoActive = 'Y' ORDER BY $sortorder DESC })
    or die "Error: ".$dbh->errstr."\n";
  $sth->execute() or die "Error: ".$sth->errstr."\n";

Select allOpen in new window

cool I will add some error statements to it.  never thought about that.

ok it appears to be dieing actually at a different spot now:
It doesn't run past this retrieval function.

while (my (@Raw) = $sth->fetchrow_array())  # keep fetching until there's nothing left
{
	$Tag[$a] = $Raw[0];
	$Dealer[$a] = $Raw[1];
	$YrMade[$a] = $Raw[2];
	$Make[$a] = $Raw[3];
	$Model[$a] = $Raw[4];
	$ExtCol[$a] = $Raw[5];
	$Price[$a] = $Raw[6];
	$FinanceAvail[$a] = $Raw[7];
	$DateAdded[$a] = $Raw[8];
	$DRF[$a] = $Raw[9];
 
#print "$a / $DRF[$a]<br>\n";
 
	@Raw = "";
	$a = ++$a;
}

Select allOpen in new window

I don't see anything wrong w/ that block.   What do you mean by "doesn't run past"?   Never exits, or doesn't execute, or some other error?

what's the scoping of $sth?   In your first example, the scoping is within each IF block.  Is this what you want?  

Do you have  'use strict;'  at the top of your file to catch those types of errors?

Hi - No I haven't used strict at the top.  Thanks for helping me empty that array.   When the script is executing, it prints RUN, but it stops never gets to the point to print RUN2222.  Is there a way I can die on an error on a while statement?  I tried putting the or die after fetchrow_array())) but the script kept dying...  Thanks for your help all!

runrun:
print "RUN<BR>";
while (my (@Raw) = $sth->fetchrow_array())  # keep fetching until there's nothing left
{
print "RUN2222<BR>";
	$Tag[$a] = $Raw[0];
	$Dealer[$a] = $Raw[1];
	$YrMade[$a] = $Raw[2];
	$Make[$a] = $Raw[3];
	$Model[$a] = $Raw[4];
	$ExtCol[$a] = $Raw[5];
	$Price[$a] = $Raw[6];
	$FinanceAvail[$a] = $Raw[7];
	$DateAdded[$a] = $Raw[8];
	$DRF[$a] = $Raw[9];
 
#print "$a / $DRF[$a]<br>\n";
 
	@Raw = "";
	$a = ++$a;
}
print "RUN3<BR>";

Select allOpen in new window

there have been bugs reported that cause segfaults, which would drop you out of your code... on some platforms it may not be apparent that is happening.

What platform (Windows/Linux distro) and version of perl?  And what DB module are you using?   Can you att your code from the top to where it's dieing?  

sure here is the file.  it's linux webhosting on godaddy.  hope the coding isn't to sloppy.

#!/usr/bin/perl
 
use CGI;
#use CGI::Carp qw/fatalsToBrowser/;
use DBI;
use Env;
use Image::Magick;
 
   print "Content-type: text/html\n\n";
 
#$COLA = "#98ffb1";
#$COLB = "#ff7d83";
#$COLA = "#BDB38C";
#$COLB = "#CDCDB7";
$COLA = "#D2DBFF";
$COLB = "#BBC4FF";
$COLC = "#D2DBFF";
 
### Initialize Connection
#&Init_Connect;
 
 
### Read in Header Information
 
  $forminfo = $ENV{'QUERY_STRING'};
  $UserRaw = $ENV{'REMOTE_USER'};
  $Referer = $ENV{'HTTP_REFERER'};
  $IP = $ENV{'REMOTE_ADDR'};
 
chomp ($forminfo);
 
      @key_value_pairs = split(/&/,$forminfo); 
 
      foreach $pair (@key_value_pairs){
           ($key,$value) = split(/=/,$pair);
           $value =~ s/\+/ /g; 
           $value =~ s/%([0-9a-fA-F][0-9a-fA-F])/pack("C", hex($1))/eg;
           $FORM_DATA{$key} = $value;
      }
 
$form_size = $ENV{'CONTENT_LENGTH'};
read( STDIN, $forminfo, $form_size );
 
      @key_value_pairs = split(/&/,$forminfo);
 
      foreach $pair (@key_value_pairs){
           ($key,$value) = split(/=/,$pair);
           $value =~ s/\+/ /g; 
           $value =~ s/%([0-9a-fA-F][0-9a-fA-F])/pack("C", hex($1))/eg;
           $FORM_DATA{$key} = $value;
      }
 
#foreach $var(sort keys(%ENV)){
#    print"<LI> $var - $ENV{$var}<br>";
#}
 
#### Display Header
 
 
$COM = $FORM_DATA{'COM'};
$Request = $FORM_DATA{'Request'};
$IR = $FORM_DATA{'IR'};
$OR = $FORM_DATA{'OR'};
$PG = $FORM_DATA{'PG'};
$SO = $FORM_DATA{'SO'};
$Sub = $FORM_DATA{'Sub'};
$AAC = $FORM_DATA{'AAC'};
 
chomp ($COM,$IR,$OR,$PG,$SO,$Request,$Sub);
 
### Print Header
&Header;
 
#### VIEW DATABASE LISTING
if ($Request eq "") {
 
	if ($SO eq "") {
		$sortorder = "YrMade";
		$SODesc = "Year Manufactured";
	}
	if ($SO eq "" & ($IP eq "24.72.27.223" || $IP eq "24.72.51.51")) {
		$sortorder = "DateAdded";
		$SODesc = "Date Added";
	}
	if ($SO eq "YrMade") {
		$sortorder = "YrMade";
		$SODesc = "Year Manufactured";
	}
	if ($SO eq "Make") {
		$sortorder = "Make";
		$SODesc = "Manufacturer";
	}
	if ($SO eq "Price") {
		$sortorder ="Price";
		$SODesc = "Price";
	}
	if ($SO eq "FinanceAvail") {
		$sortorder = "FinanceAvail";
		$SODesc = "Financing Available";
	}
 
#print "$sortorder / $SO / $IP / select Tag, Dealer, YrMade, Make, Model, ExtCol, Price, FinanceAvail, DateAdded, DRF FROM APSVehicles WHERE AutoActive = 'Y' ORDER BY $sortorder DESC<br>\n";
 
my $dsn = 'DBI:mysql:xxxxxxxxxxx';
my $db_user_name = 'xxxxxxxxx';
my $db_password = 'xxxxxxxxx';
my ($id, $password);
my $dbh = DBI->connect($dsn, $db_user_name, $db_password);
 
#my $sth = $dbh->prepare(qq{ select * from APSVehicles });
#$sth->execute();
 
#$sth->fetchrow_array();
 
$a = 0;
if ($COM eq "AAC") {
print "COM: AAC";
	my $sth = $dbh->prepare(qq{ select Tag, Dealer, YrMade, Make, Model, ExtCol, Price, FinanceAvail, DateAdded, DRF FROM APSVehicles WHERE AutoActive = 'Y' ORDER BY $sortorder DESC })  or die "Error: ".$dbh->errstr."\n";
	$sth->execute() or die "Error: ".$sth->errstr."\n";
	$pass = "11";
}
if ($COM eq "RWA") {
print "COM: RWA";
	my $sth = $dbh->prepare(qq{ select Tag, Dealer, YrMade, Make, Model, ExtCol, Price, FinanceAvail, DateAdded, DRF FROM APSVehicles WHERE AutoActive = 'Y' ORDER BY $sortorder DESC }) or die "Error: ".$dbh->errstr."\n";
	$sth->execute() or die "Error: ".$sth->errstr."\n";
	$pass = "11";
}
if ($pass ne "11") {
print "COM: NON AAC";
	my $sth = $dbh->prepare(qq{ select Tag, Dealer, YrMade, Make, Model, ExtCol, Price, FinanceAvail, DateAdded, DRF FROM APSVehicles WHERE AutoActive = 'Y' ORDER BY $sortorder DESC }) or die "Error: ".$dbh->errstr."\n";
	$sth->execute() or die "Error: ".$sth->errstr."\n";
}
runrun:
print "RUN<BR>";
while (my (@Raw) = $sth->fetchrow_array())  # keep fetching until there's nothing left
{
print "RUN2222<BR>";
 
	$Tag[$a] = $Raw[0];
	$Dealer[$a] = $Raw[1];
	$YrMade[$a] = $Raw[2];
	$Make[$a] = $Raw[3];
	$Model[$a] = $Raw[4];
	$ExtCol[$a] = $Raw[5];
	$Price[$a] = $Raw[6];
	$FinanceAvail[$a] = $Raw[7];
	$DateAdded[$a] = $Raw[8];
	$DRF[$a] = $Raw[9];
 
#print "$a / $DRF[$a]<br>\n";
 
	@Raw = "";
	$a = ++$a;
}
print "RUN3<BR>";

Select allOpen in new window

thanks everyone for your help!