in reply to DBI Inserting undef as literal string 'null'

I'm presuming you are using DBD::ODBC although you did not say so.

To insert a NULL you bind an undef - that should work. If that does not work for you then you are either not binding an undef or your DBD or ODBC driver is faulty. However, when you say the column contains 'null' afterwards how did you ascertain this - i.e., what did you use to view this column?

You may be able to use ParamValues after the execute to see what you are inserting but not all DBDs support it - though should and DBD::ODBC certainly does so long as you have not got an ancient version.

Another alternative for seeing what you are doing is using DBIx::Log4perl which shows bound parameters and a whole lot more. However, before you go down this route I'd suggest you examine carefully what you are binding. Add Data::Dumper and dump those "@values" before the call to execute.

BTW, you should not need to use that finish - see the DBI pod on finish.

You might want to look up the chomp function instead of you method of removing newlines.

  • Comment on Re: DBI Inserting undef as literal string 'null'

Replies are listed 'Best First'.
Re^2: DBI Inserting undef as literal string 'null'
by marmanold (Novice) on Feb 23, 2010 at 21:32 UTC

    Thanks for your help!

    I should have been more clear. I'm seeing the literal string when I do a select in the database. (Before anyone asks it's not just a different font or something. SELECT * FROM table_name WHERE field IS NULL does not work. It's actually a string with the value 'null'. I've inserted NULL values into the table manually and they work fine.)

    I dumped the bound parameters as you suggested (see below). Values being set to undef is what I want, right?

    $VAR1 = {
          '11' => '117082.50',
          '7' => '116500.00',
          '2' => '0028363075',
          '17' => '.0050000',
          '1' => '08/31/09',
          '18' => '09-01-09',
          '16' => '.0650000',
          '13' => undef,
          '6' => '462.76',
          '3' => '1709777884',
          '9' => '48.54',
          '12' => undef,
          '14' => undef,
          '15' => '736.36',
          '8' => '631.04',
          '4' => '08-26-09',
          '10' => '582.50',
          '5' => '1'
        };

    [download]
[reply]
[d/l]

      Assuming parameters 13, 12 and 14 coincide with the columns you expect to see NULL in then this looks right. Just make sure you are actually inserting into the column you expect to see NULL in as if you omit the column it may have a default of "null" - you'd have to check your schema for that.

      Other than that all I can suggest is you enable DBI tracing and assuming you are using DBD::ODBC add the following to the start of your script:

      use DBD::ODBC;
  DBI->trace(DBD::ODBC->parse_trace_flags('odbcconnection|odbcunicode'
+));

      [download]

      then set trace level 15 on your connection handle immediately after the connect or run with DBI_TRACE=15=x.log. See DBD::ODBC Tracing and DBI Tracing.

[reply]
[d/l]

In Section Seekers of Perl Wisdom