inserting date into mysql

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

hi, I have the following code:

 my $tdate = $fields[0];
  print "$tdate\n";
my $trade_dt=UnixDate($tdate,"%Y-%m-%d");
#  my $trade_dt = DATE(\"$tdate\");
  print "$trade_dt\n";
my $ins_rec = "INSERT IGNORE INTO $tblname(\`trade_dt\`,\`symbol\`) va
+lues (?,?);";

my $sth=$dbh->prepare($ins_rec);
$sth->execute($trade_dt,$symbol);
$sth->finish;
[download]

and when I run it, I get the following message:

"10/14/2011"
Use of uninitialized value $trade_dt in concatenation (.) or string at
+ Set.data.pl line 42, <FIN> line 1.
[download]

any thoughts on how I can fix it....it is obviously reading the date correctly from the file as the print statement shows the correct date, but I am obviously making an error in the INSERT statement.

Comment on inserting date into mysql Select or Download Code

Replies are listed 'Best First'.
Re: inserting date into mysql by Corion (Patriarch) on Oct 16, 2011 at 19:13 UTC
Why don't you believe Perl when it says that `$trade_dt` is not initialized? Which one of these three lines is line number 42? `my $tdate = $fields[0]; print "$tdate\n"; my $trade_dt=UnixDate($tdate,"%Y-%m-%d"); # my $trade_dt = DATE(\"$tdate\"); print "$trade_dt\n";` [download] If you want to help us help you better, please post a complete, self-contained example of your program. Please do spend the effort and make it shorter than 20 lines. As a debugging tip, I always do my debug print statements with some prefix and delimiters around the data: `... print "TDATE: [$tdate]\n"; ... print "TRADE_DT: [$trade_dt]\n"; ...` [download] That way, I can also see whether something is the empty string or did not print at all. Update: My prediction is that you will see the following output: `TDATE: [10/14/2011] TRADE_DT: []` [download] ... and that would be because whatever unspecified date library you use does not raise an error when you pass it a malformed date. Your date input is in MM/DD/YYYY format, but you specify it as `%Y-%m-%d` format, and the conversion/deparsing fails.	[reply] [d/l] [select]
Re: inserting date into mysql by AnomalousMonk (Archbishop) on Oct 16, 2011 at 23:00 UTC
In addition to what Corion has already posted, the first print statement `print "$tdate\n";` seems to produce the output `"10/14/2011"` in which " (double-quote) characters are present at the beginning and end of the string that is printed. Is this something the `UnixDate()` function is going to be happy with? BTW: What does baperl think would be the result of executing the `my $trade_dt = DATE(\"$tdate\");` statement? I have my answer...	[reply] [d/l] [select]
Re^2: inserting date into mysql by NetWallah (Canon) on Oct 17, 2011 at 06:02 UTC
Spot-on! (AnomalousMonk ++) This modification fixes the problem: `my $trade_dt=UnixDate($x=~/([\/\d]+)/,qq\|%Y-%m-%d\|);` [download] (Assuming you are using UnixDate from Date::Manip) UnixDate silently returns undef when provided a quoted value. "XML is like violence: if it doesn't solve your problem, use more."	[reply] [d/l]