nafri has asked for the wisdom of the Perl Monks concerning the following question:

i got perl script which was working fine till recently .nothing was changed .This is back office script and the data it gets is from a trusted source.i didnt write it and i know very little about perl

foreach my $xmlfile (@productfiles) {
  my $XML = XML::Smart->new($xmlfile) or warn("Unable to parse $xmlfil
+e: $!"), next;
  $XML = $XML->cut_root();
  
  next if (!defined $XML->{Product}{Code} || $XML->{Product}{Code}->co
+ntent != 1);
  my $prodid = $XML->{Product}{ID};
  my @features = @{$XML->{Product}{ProductFeature}};
  foreach my $feature (@features) {
    my $cat_featureid = $feature->{CategoryFeature_ID};
    my $value = $feature->{Presentation_Value};
    my $sql = "INSERT INTO products_features (product_id, feature_id, 
+value) 
        VALUES (".$prodid.", ".$cat_featureid.", ".$dbh->quote($value)
+.") 
        ON DUPLICATE KEY UPDATE value=".$dbh->quote($value);
    $dbh->do($sql);
     print Dumper $sql;
  }
 
  undef $XML;
  $c_processed++;
  print "." if ($c_processed % 10 == 0); # indicate progress as operat
+ions can take a long time
}

print "$c_processed product feature values imported or updated.\n";

[download]
I tried to debug the script to see where the erroe happens and this is what i got from dumper. 
$VAR1 = 'INSERT INTO products_features (product_id, feature_id, value)
        VALUES (9589372, 34200, \'Brother HL3040\')
        ON DUPLICATE KEY UPDATE value=\'Brother HL3040\'';

[download]
$VAR1 = 'INSERT INTO products_features (product_id, feature_id, value)
        VALUES (9589372, 41695, \'1\')
        ON DUPLICATE KEY UPDATE value=\'1\'';

[download]

DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '') ON DUPLICATE KEY UPDATE value=''' at line 2 at import_productfeatures.pl line 71.

I would appreciate if anyone can look at it..

Replies are listed 'Best First'.
Re: error ..ON DUPLICATE KEY UPDATE value='''
by runrig (Abbot) on Aug 02, 2013 at 16:03 UTC
    Does not seem like a perl problem. What if you directly execute that insert/update statement in a sql client?
[reply]

      Have you got DBI's RasieError set? Look for a call to DBI->connect and see if RaiseError => 1. If yes, then your call to do on line 14 just before your Dumper call will die and all your seeing in the Dumper output is from the last successful call and not the one that is failing. If so do:

      eval {
    $dbh->do($sql);
};
if (my $ev = $@) {
    print Dumper($sq);
    die $ev;
}

      [download]

      Now you'll only get Dumper output for the failing case.

[reply]
[d/l]
        RaiseError => 1 is set. the error i get now is this.. 
        $VAR1 = 'INSERT INTO products_features (product_id, feature_id, value)
        VALUES (5652151, , \'\')
        ON DUPLICATE KEY UPDATE value=\'\'';

        [download]
        looks like the feature_id, value is empty here..What can i do to fix this..
[reply]
[d/l]

Back to Seekers of Perl Wisdom