CGI/Perl Script issues moving to dreamhost

software development

#1

Hi,

I have a script that utilizes FedEx shipping scripts for calculating the shipping on a product. When I run the script I get the following errors

mahasim:/home/visualrealityink/logs/greatgreenapple.com/http# tail
error.log.2012-01-31
[Tue Jan 31 14:25:27 2012] [error] [client 208.104.22.48] DBD::mysql::st
execute failed: called with 1 bind variables when 0 are needed at
rate_service.cgi line 67., referer:
http://www.ggatesttest.inkcontrolsystems.com/cgi-bin/FedEx/rate_service.cgi?merchant=1&product=1102&zip=29720
[Tue Jan 31 14:25:27 2012] [error] [client 208.104.22.48] DBD::mysql::st
fetch failed: fetch() without execute() at rate_service.cgi line 68.,
referer:
http://www.ggatesttest.inkcontrolsystems.com/cgi-bin/FedEx/rate_service.cgi?merchant=1&product=1102&zip=29720
[Tue Jan 31 14:25:29 2012] [error] [client 208.104.22.48] DBD::mysql::st
execute failed: called with 1 bind variables when 0 are needed at
rate_service.cgi line 67., referer:
http://www.ggatesttest.inkcontrolsystems.com/cgi-bin/FedEx/rate_service.cgi?merchant=1&product=1102&zip=29720
[Tue Jan 31 14:25:29 2012] [error] [client 208.104.22.48] DBD::mysql::st
fetch failed: fetch() without execute() at rate_service.cgi line 68.,
referer:
http://www.ggatesttest.inkcontrolsystems.com/cgi-bin/FedEx/rate_service.cgi?merchant=1&product=1102&zip=29720
[Tue Jan 31 14:25:29 2012] [error] [client 208.104.22.48] DBD::mysql::st
execute failed: called with 1 bind variables when 0 are needed at
rate_service.cgi line 67., referer:
http://www.ggatesttest.inkcontrolsystems.com/cgi-bin/FedEx/rate_service.cgi?merchant=1&product=1102&zip=29720
[Tue Jan 31 14:25:29 2012] [error] [client 208.104.22.48] DBD::mysql::st
fetch failed: fetch() without execute() at rate_service.cgi line 68.,
referer:
http://www.ggatesttest.inkcontrolsystems.com/cgi-bin/FedEx/rate_service.cgi?merchant=1&product=1102&zip=29720
[Tue Jan 31 23:55:46 2012] [error] [client 208.104.22.48] File does not
exist:
/home/visualrealityink/greatgreenapple.com/cgi-bin/logos/ARRAY(0x9b14c8),
referer:
http://www.ggatesttest.inkcontrolsystems.com/cgi-bin/FedEx/rate_service.cgi?merchant=1&product=1102&zip=29720
[Tue Jan 31 23:55:46 2012] [error] [client 208.104.22.48] File does not
exist: /home/visualrealityink/greatgreenapple.com/missing.html, referer:
http://www.ggatesttest.inkcontrolsystems.com/cgi-bin/FedEx/rate_service.cgi?merchant=1&product=1102&zip=29720
[Tue Jan 31 23:55:49 2012] [error] [client 208.104.22.48] File does not
exist:
/home/visualrealityink/greatgreenapple.com/cgi-bin/logos/ARRAY(0x9b14c8),
referer:
http://www.ggatesttest.inkcontrolsystems.com/cgi-bin/FedEx/rate_service.cgi?merchant=1&product=1102&zip=29720
[Tue Jan 31 23:55:49 2012] [error] [client 208.104.22.48] File does not
exist: /home/visualrealityink/greatgreenapple.com/missing.html, referer:
http://www.ggatesttest.inkcontrolsystems.com/cgi-bin/FedEx/rate_service.cgi?merchant=1&product=1102&zip=29720

The code for that file is as follows:

[code]#!/usr/bin/perl

use CGI qw(:all);
use DBI;

require ‘…/common_includes/includes.pl’;
require ‘…/common_includes/shipping_modules.pl’;
require ‘./sub_includes.pl’;

my($dbh) = db_open();

my $merchant = param(‘merchant’);
my $product = param(‘product’);
my $zip = param(‘zip’);
my $remember = param(‘remember’);
my $relative_path = ‘/cgi-bin/logos’;
my $page = ‘http://www.ggatesttest.inkcontrolsystems.com/cgi-bin/FedEx/rate_service.cgi’;
my $service_php = ‘http://www.ggatesttest.inkcontrolsystems.com/ci-bin/FedEXphp/rate_service.php’;

my($cookie_remember,$cookie_zip) = get_cookie_zip($page,$merchant,$zip,$remember);
if($cookie_remember){
$remember = ‘1’;
$zip = $cookie_zip;
}

if($zip){
pop_header($dbh,$merchant,$relative_path);
zip_form($dbh,$merchant,$product,$zip,$remember);
my($error) = product_data($dbh,$merchant,$product,$zip,$service_php);
if($error){
print “

Error

\n”;
}
quote_message($dbh,$merchant);
pop_footer($dbh,$merchant,$relative_path);
}else{
pop_header($dbh,$merchant,$relative_path);
zip_form($dbh,$merchant,$product,$zip,$remember);
my($error) = product_data($dbh,$merchant,$product);
if($error){
print “

Error

\n”;
}
pop_footer($dbh,$merchant,$relative_path);
}

$dbh->disconnect;
exit(0);

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

Sub-Routines Follow Theese Comments

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

sub get_look_elements{
my($database,$merchant) = @_;
my $query = $database->prepare(“select header_graphic,background_graphic,footer_graphic,font_color,background_color,label_bg_color,label_color,data_bg_color,data_color from fedex_merchant_popup_look where merchant = ?”);
$query->execute($merchant);
my($header,$background,$footer,$color,$bg_color,$label_bg_color,$label_color,$data_bg_color,$data_color) = @{$query->fetch};
$query->finish;
return($header,$background,$footer,$color,$bg_color,$label_bg_color,$label_color,$data_bg_color,$data_color);
}

sub pop_header{
my($database,$merchant,$rel_path,$zip,$remember) = @_;
my ($header,$background,$footer,$color,$bg_color,$label_bg_color,$label_color,$data_bg_color,$data_color) = get_look_elements($database,$merchant);
my $query = $database->prepare(“select width,height from fedex_merchant_popup_look where merchant = ?”);
$query->execute($merchant);
my($width,$height) = @{$query->fetch};
$query->finish;
print “Content-type: text/html\n\n”;
print “\n\n Shipping Quote\n”;
print “\n”;
print “TD.Main {\n”;
if($background){
print " background-image: url($rel_path/$background);\n";
print " background-position: bottom left;\n";
}
if($color){
print " color: $color;\n";
}
if($bg_color){
print " background-color: $bg_color;\n";
}
print “}\n”;
print “TD {\n”;
if($color){
print " color: $color;\n";
}
print “}\n”;
print “TH {\n”;
if($color){
print " color: $color;\n";
}
print “}\n”;
print “TD.Label {\n”;
if($label_color){
print " color: $label_color;\n";
}
if($label_bg_color){
print " background-color: $label_bg_color;\n";
}
print “}\n”;
print “TH.Label {\n”;
if($label_color){
print " color: $label_color;\n";
}
if($label_bg_color){
print " background-color: $label_bg_color;\n";
}
print “}\n”;
print “TD.Data {\n”;
if($data_color){
print " color: $data_color;\n";
}
if($data_bg_color){
print " background-color: $data_bg_color;\n";
}
print “}\n”;
print “\n”;
print “\n”;
print “<BODY onload=“javascript: window.moveTo(0,0)”;
if($width and $height){
print “; window.resizeTo($width,$height)”;
}
print “;”>\n”;
print “<TABLE Align=“center” Width=“99%”>\n”;
print “

\n”;
print " <TD Align=“center” Class=“Main”>\n";
if($header){
print “<IMG SRC=”$rel_path/$header" ALT=“Shipping Quote”>\n";
}else{
print “

Shipping Quote

\n”;
}
return();
}

sub product_data{
my($database,$merchant,$product,$zip,$service_php) = @_;
my $query = $database->prepare(“select id,product_code,product_name,description,price from products where merchant = ? and id = ?”);
$query->execute($merchant,$product);
my($valid,$sku,$name,$description,$price) = @{$query->fetch};
$query->finish;
if(not $valid){
return(1);
}
if($description){
($description) = convert_newline($description);
}
print “
\n”;
print “

\n”;
print “<TD Valign=“top”>
\n”;
print “\n”;
print " <TH Colspan=“2” Class=“Label”>Product Details\n";
print “\n”;
if($sku){
print “\n”;
print " <TD Align=“right” Class=“Label”>Sku:\n";
print " <TD Class=“Data”>$sku\n";
print “\n”;
}
print “\n”;
print " <TD Align=“right” Class=“Label”>Product:\n";
print " <TD Class=“Data”>$name\n";
print “\n”;
print “\n”;
print " <TD Align=“right” Class=“Label”>Price:\n";
print " <TD Class=“Data”>$ $price\n";
print “\n”;
if($description){
print “\n”;
print " <TH Align=“center” Colspan=“2” Class=“Label”>Description\n";
print “\n”;
print “\n”;
print " <TD Colspan=“2” Class=“Data”>$description\n";
print “\n”;
}
print “
\n”;
my($transit_time,$num_tables);
if($zip){
($transit_time,$num_tables) = run_fedex_rates($database,$merchant,$product,$zip,$service_php);
}
print “\n”;
if($transit_time){
my $cols = $num_tables + 1;
print “\n”;
print " <TD Colspan="$cols" Align=“center”>Transit Time: $transit_time\n";
print “\n”;
}
print “\n”;
return();
}

sub test_body{
print “
Testing This\n”;
print “
I need to place the item specific code\n”;
}

sub run_fedex_rates{
my($database,$merchant,$product,$zip,$service_php) = @_;
my($product_hash) = fedex_product_hash($dbh,$merchant,$product,$zip);
my $query = $database->prepare(“select id,results from fedex_rate_results where merchant = ? and product = ? and hash = ? and created > DATE_SUB(NOW(),INTERVAL 6 HOUR)”);
$query->execute($merchant,$product,$product_hash);
my($res_id,$res_string) = @{$query->fetch};
$query->finish;
my($transit_time,$num_tables);
my(@results);
my @raw_rate;
if($res_id){
@raw_rate = split(/\n/,$res_string);
}else{
# print “
get_fedex_rates($merchant,$product,$zip,$command)\n”;
(@raw_rate) = get_rates($merchant,$product,$zip,$service_php,$product_hash);
}
my $size = @raw_rate;
my $loop = 0;
while($loop < $size){
chomp($raw_rate[$loop]);
$loop ++;
}
if((not $res_id) and $size){
my $res_string = ‘’;
my $loop = 0;
while($loop < $size){
$res_string .= $raw_rate[$loop] . “\n”;
$loop ++;
}
my $query = $database->prepare(“insert into fedex_rate_results(created,merchant,product,hash,results) Values(NOW(),?,?,?,?)”);
$query->execute($merchant,$product,$product_hash,$res_string);
$query->finish;
}
if($size){
# Create response parser and call it here
my $loop = $size;
while($loop < $size){
print “
$raw_rate[$loop]\n”;
$loop ++;
}
($transit_time,$num_tables) = fedex_parse_response($database,$merchant,$product,@raw_rate);
}else{
# FedEX non-response
no_fedex_response($database,$merchant,$product);
}
return($transit_time,$num_tables);
}

sub pop_footer{
my($database,$merchant,$rel_path) = @_;
my ($header,$background,$footer,$color,$bg_color) = get_look_elements($database,$merchant);
if($footer){
print “
<IMG SRC=”$rel_path/$footer">
\n";
}
print “
\n”;
print “

\n”;
print “\n”;
print “<div align=“center”>\n”;
print “<script language=“JavaScript1.1” type=“text/javascript”>\n”;
print “\n”;
print “\n”;
print “\n”;
print “\n\n”;
}

sub zip_form{
my($database,$merchant,$product,$zip,$remember) = @_;
my $button = “Enter Zip Code\n”;
print “

\n”;
print “<FORM Method=“get”>\n”;
print “<INPUT Type=“hidden” Name=“merchant” Value=”$merchant">\n";
print “<INPUT Type=“hidden” Name=“product” Value=”$product">\n";
print “

\n”;
print “\n”;
print " <TD Align=“right”>Zip Code:\n";
print " \n";
print “\n”;
print “\n”;
print " <TD Colspan=“2” Align=“center”><INPUT Type=“checkbox” Name=“remember” Value=“1"”;
if($remember){
print " CHECKED";
$button = “Change Zip Code”;
}
print “> Remember my zip code\n”;
print “\n”;
print “\n”;
print " <TD Colspan=“2” Align=“center”><INPUT Type=“submit” Value="$button">\n";
print “\n”;
print “
<INPUT Type=“text” Name=“zip” Value="$zip">
\n”;
print “\n”;
}

sub get_cookie_zip{
my($path,$merchant,$zip,$remember) = @;
my $item = $ENV{QUERY_STRING};
my $referrer = $ENV{HTTP_REFERER};
$referrer =~ /(.)?./;
$referrer = $1;
my $redirect_page = $path . ‘?’ . $item;
my $cookie_zip = cookie("rzip
$merchant");
my $cookie_remember = 0;
if($cookie_zip){
$cookie_remember = 1;
}
if($cookie_remember and (not $remember)){
# Expire Cookie and Re-Direct if referrer is this page
if($referrer eq $path){
my $q = new CGI;
$cookie = $q->cookie(-name => “rzip_$merchant”, -value=>$cookie_zip, -expires => “-1h”);
print $q->redirect(-url =>$redirect_page, -cookie=> $cookie);
exit(0);
}
}elsif($remember and (not $cookie_remember)){
# Set Cookie and Redirect
my $q = new CGI;
$cookie = $q->cookie(-name => “rzip_$merchant”, -value=>$zip, -expires => “+96h”);
print $q->redirect(-url =>$redirect_page, -cookie=> $cookie);
exit(0);
}elsif(($remember and $cookie_remember) and ($cookie_zip ne $zip)){
# Re-Set Cookie with New Zip
my $q = new CGI;
$cookie = $q->cookie(-name => “rzip_$merchant”, -value=>$zip, -expires => “+96h”);
print $q->redirect(-url =>$redirect_page, -cookie=> $cookie);
exit(0);
}
return($cookie_remember,$cookie_zip);
}

sub get_rates{
my($merchant,$product,$zip,$service_php,$product_hash) = @;
my @good_results;
my $attempt = 0;
while($attempt < 10){
my @raw_results = $service_php $merchant $product $zip $product_hash;
my @check_results;
my $size = @raw_results;
my $loop = 0;
while($loop < $size){
chomp($raw_results[$loop]);
if($raw_results[$loop]){
push(@check_results,$raw_results[$loop]);
}
$loop ++;
}
my $find_fail = 0;
foreach(@check_results){
if($
eq ‘RequestFailed’){
$find_fail = 1;
}
}
if(not $find_fail){
$attempt = 10;
@good_results = @check_results;
}
$attempt ++;
}
return(@good_results);
}

sub no_fedex_response{
my($database,$merchant,$product) = @_;
my $query = $database->prepare(“select fedex_failure_message,fedex_failure_show_flat_rate from fedex_merchant_popup_look where merchant = ?”);
$query->execute($merchant);
my($fedex_failure_message,$fedex_failure_show_flat_rate) = @{$query->fetch};
$query->finish;
my $query = $database->prepare(“select ship_cost from products where id = ? and merchant = ?”);
$query->execute($product,$merchant);
my($flat_ship) = @{$query->fetch};
$query->finish;
print “
$fedex_failure_message\n”;
if(($fedex_failure_show_flat_rate eq ‘Y’) and ($flat_ship)){
print “

Estimated Shipping Charge: $flat_ship\n”;
}
print “

\n”;
return();
}

sub fedex_parse_response{
my($database,$merchant,$product,@raw_rates) = @_;
my $query = $database->prepare(“select include_list_rates from fedex_merchant_account where merchant = ?”);
$query->execute($merchant);
my($show_list_rates) = @{$query->fetch};
$query->finish;
my $quote_rates = ‘Account’;
my $size = @raw_rates;
# Get Severity, Transit Time, Rate Type, Number of Shipments
my($severity,$transit_time,$rate_type,$shipments) = rate_main_details(@raw_rates);
my @ship_rate_types;
my $loop = 0;
while($loop < $shipments){
my($type) = get_shipment_rate_type($loop,@raw_rates);
push(@ship_rate_types,$type);
$loop ++;
}
($transit_time) = clean_transit_time($transit_time);

print “
Transit Time: $transit_time\n”;

# Set the ship order to list rate, then account rate
my $size = @ship_rate_types;
my $num_ship = $shipments + 1;
print "<TD Width=\"10\">&nbsp;</TD>\n";
my @sorted = sort {$b cmp $a} @ship_rate_types;
# Print Rate Tables
if($shipments){
	my $loop = 0;
	while($loop < $shipments){
		my $display;
		my $s_loop = 0;
		while($s_loop < $shipments){
			if($sorted[$s_loop] eq $ship_rate_types[$loop]){
				$display = $s_loop;
				$s_loop = $shipments;
			}
			$s_loop ++;
		}
		print "<TD Valign=\"top\">\n";
		shipment_rate_table($display,@raw_rates);
		print "</TD>\n";
		$loop ++;
		if($loop < $shipments){
			$num_ship ++;
			print "<TD Width=\"10\">&nbsp;</TD>\n";
		}
	}
}else{
	my $junk;
	$num_ship = 2;
	print "<TD Valign=\"top\">\n";
	shipment_rate_table($junk,@raw_rates);
	print "</TD>\n";
}
return($transit_time,$num_ship);

}

sub shipment_rate_table{
my($shipment,@results) = @_;
my $size = @results;
my @seeks;
my @returns;
my ($shipment,$ship_null) = zero_null_check($shipment);
my $header = ‘RatedShipment->’;
if(not $ship_null){
$header .= $shipment . ‘->’;
}
push(@seeks,"$header" . “RateType:”);
push(@seeks,"$header" . “TotalBaseCharge:”);
push(@seeks,"$header" . “TotalFreightDiscounts:”);
push(@seeks,"$header" . “TotalSurcharges:”);
push(@seeks,"$header" . “NetCharge:”);
my $s_size = @seeks;
my $loop = 0;
while($loop < $size){
my $s_loop = 0;
while($s_loop < $s_size){
my $seek = $seeks[$s_loop];
if($results[$loop] =~ /^$seek/){
$results[$loop] =~ /^$seek (.*)/;
$returns[$s_loop] = $1;
$s_loop = $s_size;
}
$s_loop ++;
}
$loop ++;
}
if($ship_null){
$returns[0] = ‘Shipping Rate’;
}else{
($returns[0]) = clean_rate_type($returns[0]);
}
print “

\n”;
print “\n”;
print " <TH Colspan=“2” Class=“Label”>$returns[0]\n";
print “\n”;
print “\n”;
print " <TD Align=“right” Class=“Label”>Base Charge:\n";
print " <TD Align=“right” Class=“Data”>";
printf ("%.2f",$returns[1]);
print “\n”;
print “\n”;
print “\n”;
print " <TD Align=“right” Class=“Label”>Surcharges:\n";
print " <TD Align=“right” Class=“Data”>";
printf ("%.2f",$returns[3]);
print “\n”;
print “\n”;
print “\n”;
print " <TD Align=“right” Class=“Label”>Discounts:\n";
print " <TD Align=“right” Class=“Data”>";
printf ("%.2f",$returns[2]);
print “\n”;
print “\n”;
print “\n”;
print " <TD Align=“right” Class=“Label”>Total:\n";
print " <TD Align=“right” Class=“Data”>";
printf ("%.2f",$returns[4]);
print “\n”;
print “\n”;
print “
\n”;
return();
}

sub quote_message{
my($database,$merchant) = @_;
my $query = $database->prepare(“select quote_message from fedex_merchant_popup_look where merchant = ?”);
$query->execute($merchant);
my($quote_message) = @{$query->fetch};
$query->finish;
if($quote_message){
($quote_message) = convert_newline($quote_message);
print “
$quote_message\n”;
print “
\n”;
}
return();
}

exit(1);
[/code]
Thanks for the help,
:slight_smile:


#2

What do you need help with?

execute failed: called with 1 bind variables when 0 are needed at rate_service.cgi line 67.
This means it didn’t like substituting a variable when using a ? in the sql statement.

fetch failed: fetch() without execute() at rate_service.cgi line 68.,
Can’t fetch results if the query did not run.

File does not exist: /home/visualrealityink/greatgreenapple.com/missing.html
This error can be elimiinated by using a custom 404 page, ie named missing.html OR specified via ErrorDocument or perhaps through mod_rewrite in .htaccess

File does not exist:
/home/visualrealityink/greatgreenapple.com/cgi-bin/logos/ARRAY(0x9b14c8),

Oh dear, programmer is using a reference to an array instead of the name of a file in the HTML output. This might be a side effect of the database queries failing to run since the results are needed for the HTML.

Hope that helps.

[hr]
OK turns out you have two other issues:

my $service_php = ‘http://www.ggatesttest.inkcontrolsystems.com/ci-bin/FedEXphp/rate_service.php’;
Mistyped the URL there!

Lines 66, 68, and 70 have hidden characters in them! It’s tricky - they are visible in Notepad++ but not Notepad+ on Windows. They would interfere with both SQL and Perl code though.