Troubles inserting NULLs into MySQL DB using DBI

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

From the perldoc DBI:
     NULL Values

     Undefined values, or "undef", are used to indicate NULL
     values.  You can insert and update columns with a NULL value
     as you would a non-NULL value.  These examples insert and
     update the column "age" with a NULL value:

       $sth = $dbh->prepare(qq{
         INSERT INTO people (fullname, age) VALUES (?, ?)
       $sth->execute("Joe Bloggs", undef);

       $sth = $dbh->prepare(qq{
         UPDATE people SET age = ? WHERE fullname = ?
       $sth->execute(undef, "Joe Bloggs");

In my script:

use Finance::YahooQuote;
use Time::HiRes qw( usleep  gettimeofday tv_interval stat );
use DBI;
use DBD::mysql;

$| = 1;


my $dbh = DBI->connect($dsn,$user,$pwd) or die "Could not connect to
the database.\nError: ",$dbh->errst,"\n";
my $sth;
my $q = "INSERT INTO quote_history
VALUES (?,?,?,STR_TO_DATE(?,'%m/%d/

$sth = $dbh->prepare($q) or die $dbh->errstr;


my @symbols = ('F','V','GOOG');
my @quotes = getquote(@symbols);


  foreach my $q (@quotes) {
    my @h = @;
    my $c = @h;
    for ( my $i = 0 ; $i < $c ; $i++ ) {
      my $j = 1+ $i;
      $h[$i] =~ s/ +//;
      $h[$i] =~ s/ %//;
      $h[$i] = ($h[$i] eq 'N/A') ? undef : $h[$i];
      if ($i =3D 4) {
    $h[$i] =~ s/pm/:pm/;
    $h[$i] =~ s/am/:am/;
    my ($hr,$min,$s) = split(':',$h[$i]);
    if ($s eq 'pm') {
      $hr +=3D 12;
    my $v = "$hr:$min";
    $h[$i] = $v;
      } else {
     $sth->execute(@h) or die $dbh->errstr;

And I get as output:

Use of uninitialized value in die at c:/Work/ line 59.
Died at c:/Work/ line 59.

in my script, the $sth->execute(@h); is line 59, and in this data
feed, much of the time, $h[$i] = ($h[$i] eq 'N/A') ? undef : $h[$i];
sets $h[$i] to undef for about 1% of the fields about half the time,
and is expected to do so.  The columns in the table that might get
NULL values are defined to accept them.  (if necessary, I can send the
whole script, but it is a little long.

NB: it does not matter if I use the above syntax or a few dozen calls
to bind_param, the result is the same.

So, then, why does DBI+DBD::mysql not permit me to insert records into
my table when the documentation clearly indicates that I ought to be
able to do so?

If it matters, I am using MySQL 5.1.50, Activestate perl 5.10, and DBI
is version 1.616 and DBD::mysql is version 4.019

I know I can fix things if I reconstruct the insert SQL de novo for
each row, based on whether or not any of the fields have null values,
but I am concerned about performance; the outer loop needs to run to
completion in less than 1 second (and in principle the @symbols array
could have thousands of values).

Thanks for any insights.


Site Timeline