in reply to Re^6: MySQL INSERT in Perl
in thread MySQL INSERT in Perl

wait a minute, I figured it out... here's what happened: for those records that have a price that as N/A, it gives that silly error message, because it cannot understand what 'N' means.....oops! so, I edited my code to handle the N/A in the bid column as follows: 
 foreach my $hr1 (@calls) 
      {
      my %h1 = %{$hr1};

      $h1{'bid'} = ($h1{'bid'} eq 'N/A') ? 0 : $h1{'bid'}; 
          next unless defined $h1{'symbol'};
      
    my $ins_rec="INSERT INTO $table (symbol,valuation_dt,expn_dt,strik
+e,open,bid,ask,last,volume) VALUES(\'$h1{'symbol'}\',now(),\'$exp\',$
+h1{'strike'},$h1{'open'},$h1{'bid'},$h1{'ask'},$h1{'last'},$h1{'volum
+e'});";
      print "my ins_rec is $ins_rec\n";
      my $sth=$dbh->do($ins_rec);
      }

[download]
thanks all for your help, especially Dave and NetWallah :-)

Replies are listed 'Best First'.
Re^8: MySQL INSERT in Perl
by davido (Cardinal) on Aug 04, 2011 at 19:54 UTC

    Now incorporate placeholders as described in the documentation for DBI. That's even more important lifestyle advice than actually solving your problem. :)


    Dave

[reply]
      will do so:-)
[reply]
        this is what I did, and it doesn't like it... I seem to run into problems using prepare/execute, and they seem to disappear when I use do 
        my $ins_rec="INSERT INTO $table (symbol,valuation_dt,expn_dt,strike,op
+en,bid,ask,last,volume) VALUES(?,?,?,?,?,?,?,?,?);";

     # print "my ins_rec is $ins_rec\n";
      my $sth=$dbh->prepare($ins_rec);
      $sth->execute(\'$h1{'symbol'}\',now(),\'$exp\',$h1{'strike'},$h1
+{'open'},$h1{'bid'},$h1{'ask'},$h1{'last'},$h1{'volume'});

        [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom