Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Perl subroutine with mysql not working

Posted on 2009-05-13
14
Medium Priority
?
373 Views
Last Modified: 2013-12-25
Im having problems with the perl subroutine not executing the sql statements when I include if statements.  If i take out the IF statements everything works properly.  I hope I'm explaining myself properly.
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 });
	$sth->execute();
	$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 });
	$sth->execute();
	$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 });
	$sth->execute();
}
runrun:

Open in new window

0
Comment
Question by:zfrankowski
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
14 Comments
 
LVL 11

Expert Comment

by:Todd Mummert
ID: 24381535

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
0
 

Author Comment

by:zfrankowski
ID: 24381557
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...
0
 

Author Comment

by:zfrankowski
ID: 24381559
I even used the same SQL statement thinking it was something with the statement.
0
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

 
LVL 11

Expert Comment

by:Todd Mummert
ID: 24381591

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?

0
 
LVL 39

Expert Comment

by:Adam314
ID: 24385376
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";

Open in new window

0
 

Author Comment

by:zfrankowski
ID: 24387081
cool I will add some error statements to it.  never thought about that.
0
 

Author Comment

by:zfrankowski
ID: 24391681
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;
}

Open in new window

0
 
LVL 11

Expert Comment

by:Todd Mummert
ID: 24392323

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?

0
 
LVL 39

Assisted Solution

by:Adam314
Adam314 earned 400 total points
ID: 24409193
This line:
    @Raw = "";
is probably not doing what you want.  It is not setting @Raw to an empty array - it is setting it to an array with a single element, and that element is the empty string.  If you want to empty @Raw, you can use:
    @Raw = ();


This line:
    $a = ++$a;
Would be better written as:
    $a++;
The ++$a means to increment $a, then return the incremented value.  You then take that value and assign it to $a again.  This is unnecessary.

But neither of these will cause your script to fail.  Do you get an error message? If so, what is the message?  Are you sure it is dieing in the block you've posted?

0
 

Author Comment

by:zfrankowski
ID: 24456580
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>";

Open in new window

0
 
LVL 11

Expert Comment

by:Todd Mummert
ID: 24456676

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?  

0
 

Author Comment

by:zfrankowski
ID: 24463009
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>";

Open in new window

0
 
LVL 11

Accepted Solution

by:
Todd Mummert earned 1600 total points
ID: 24463842

your problem is scoping of the $sth variables.

use strict;   would have caught this.

also, calling perl with  #!/usr/bin/perl -w   helps as well.

$sth is only defined inside this block...it doesn't exist outside.  

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";
}

So you're calling
$sth->fetchrow on something which is not defined.   I would expect it to scream error, or return undef, ...if it did the latter you would see the Run3<br> stuff when the problem ended.  So it may be dieing a horrible death..but I'm a bit surprised you don't see anything.

In any case,

try the following:



#!/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();
 
# climbgunks
my $sth;
 
$a = 0;
if ($COM eq "AAC") {
print "COM: AAC";
        $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";
        $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";
        $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>";

Open in new window

0
 

Author Comment

by:zfrankowski
ID: 24463902
thanks everyone for your help!
0

Featured Post

Basic Security of Your VPC

So, you’ve got this shiny new VPC and a fancy new application configured on your EC2 servers ready to go. This application is only accessible from your computer, which is great for security, but you need your users to be able to access it! So, what’s the easiest way to do this?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Batch, VBS, and scripts in general are incredibly useful for repetitive tasks.  Some tasks can take a while to complete and it can be annoying to check back only to discover that your script finished 5 minutes ago.  Some scripts may complete nearly …
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question