in reply to MySQL INSERT in Perl

Please don't edit your original post without any indication of the edit.
It confuses people trying to follow a thread.

Did you "use strict;" ? It seems like $ins_rec may have gone out of scope by the time you called the "prepare".

Also, I acknowledge the excellent suggestions by others to use placeholders, to avoid sql-injection, as well as better ways to compose the query.

If you use the perl debugger, you could step you way to right before the prepare, and view the content of $ins_rec, at that point.

            "XML is like violence: if it doesn't solve your problem, use more."

Replies are listed 'Best First'.
Re^2: MySQL INSERT in Perl
by baperl (Sexton) on Aug 04, 2011 at 14:45 UTC
    I did use

    use strict;

    unfortunately, when I use it, I get the following error: 
    Global symbol "$ins_rec" requires explicit package name at otest.pl li
+ne 53.
Global symbol "$ins_rec" requires explicit package name at otest.pl li
+ne 55.
Execution of otest.pl aborted due to compilation errors.

    [download]
    so I commented it out... how does $ins_rec go out of scope?
[reply]
[d/l]
      OK - strict, did identify your problem, which does indeed show a scoping issue.

      Do you have BLOCKS in your code - i.e - segments of code enclosed in {curly braces} ?

      Is the "my $ins_rec" line inside one of these blocks ?
      If so, that variable is not visible outside the block - you need to move the "my" statement outside the block.

      Please do yourself a favour and ALWAYS "use strict;" and "use warnings;". Professional programmers recommend this for very good reasons.

                  "XML is like violence: if it doesn't solve your problem, use more."

[reply]
        it was indeed a scoping problem...I fixed it. it runs and it inserts records into the database, but it gives a new error now: 
        DBD::mysql::db do failed: Unknown column 'N' in 'field list' at otest.
+pl line 52.

        [download]
        I am perplexed...it says do failed, but it did insert the records and it does it all correctly...then why the error? this is my updated code: 
        
my $dbh=DBI->connect($dsn,$user,$pwd);

      foreach my $hr1 (@calls) 
      {
      my %h1 = %{$hr1};

          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]
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom