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

hi Dave, I got it to work, and the $ins_rec is printing what it should. the insert is also working, and I checked the database to make sure it is all correct, and it is. however, I get this error message for the do(): 
DBD::mysql::db do failed: Unknown column 'N' in 'field list' at otest.
+pl line 52.

[download]
I just posted the revised code in my new reply.

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

    I can't provide any additional help without knowing what $ins_rec looks like when you print it immediately prior to preparing your query.


    Dave

[reply]
      hi Dave, here's what $ins_rec looks like with the print: 
      my ins_rec is INSERT INTO options (symbol,valuation_dt,expn_dt,strike,
+open,bid,ask,last,volume) VALUES('MCO130119C00055000',now(),'20130118
+',55.00,13,0.67,1.03,1.03,0);

      [download]
[reply]
[d/l]
        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 :-)
[reply]
[d/l]

In Section Seekers of Perl Wisdom