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

Great Perl Monks,
after using this site as a valued reference for the last few months I finally have a question! Please excuse my question if it is silly; I've only been using Perl for six months.

I'm using the FreeTDS ODBC driver and DBI to connect to a Microsoft SQL Server 2000 database. When I attempt to insert an undefined value - which from everything I've read here and elsewhere should insert a NULL value into the database - I get the literal string 'null'. I've searched far and wide during the last few days and either I'm the only person who's ever had this problem or I'm very bad at querying the Google.


My code works as follows: I open a pipe delimited text file, split the line into an array, cycle through the array and set each blank element to undef, using the normal DBI prepare & execute I insert the data into the database.

Code that cycles through the text file and inserts into the database.

while (!eof(INPUT)) {
   $record = <INPUT>;
   my @records = split(/\|/, $record);
   for (my $i=0; $i<scalar(@records); $i++) {
      if ($records[$i] eq '' || $records[$i] eq "\n") {$records[$i] = 
+undef;} #Setting blank fields to undefined
      else {$records[$i] =~ s/\n//g;} #Clearing endline characters
   }
   $DB->doQuery($report->{insertSQL}, $reportDate, @records);
}

[download]

Below is the method from my class which handles the DBI calls. (Note I've also tried this with without using my class, just straight DBI, and I have the same issue.) 

sub doQuery {
   my $self = shift;
   my $sql = shift;
   my @values = @_;
        
   #Running the new query
   eval{
      my $sth = $self->{"connection"}->prepare($sql);
      $sth->execute(@values);
      $sth->finish();
   };
   #If there are errors
   if ($@) { 
      warn "Error!";
   }
    
   return $self;
}

[download]

Below is an example of the SQL I'm using. 

INSERT INTO QID.dbo.ReportT52 (report_date, insert_date, loan_num, loa
+n_id, date_paid, payment_num, escrow, principal, interest, service_fe
+e, net_interest, deposit_remit, balance, late_chg, other_trust, pi_co
+nstant, ann_ir, sf_rate, due_date) VALUES (?, GETDATE(), ?, ?, ?, ?, 
+?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

[download]

I hope I've provided everything you might need. Any assistance anyone is able to provide would be much appreciated.
Thanks!

Replies are listed 'Best First'.
Re: DBI Inserting undef as literal string 'null'
by mje (Curate) on Feb 23, 2010 at 20:43 UTC

    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.

[reply]

      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]
Re: DBI Inserting undef as literal string 'null'
by hossman (Prior) on Feb 24, 2010 at 06:21 UTC

    Here's three questions for you to consider...

    1. what does using whatever tool you are using to inspect your database return when you execute "select NULL"? (ie: is your tool giving you missleading info)
    2. what exactly does your table definition look like? ... do any of the columns have default values declared (ie: did someone declare the fields to have default values of the string "null" when they ment null)
    3. what do you get when you bind undef to an insert into a numeric column? (ie: if you still find that you have a string value of "null" in a numeric column, something is seriously screwed up in your database or your driver)
[reply]
Re: DBI Inserting undef as literal string 'null'
by Anonymous Monk on Feb 24, 2010 at 06:14 UTC
    In that case, first thing I'd do is reduce the code to the absolute bare minimum; ie 1 simple hard-coded insert of 1 row with one undef in it and see what happens.
    Then try assigning the undef to a var and using bind vars.
[reply]
Re: DBI Inserting undef as literal string 'null'
by marmanold (Novice) on Mar 25, 2010 at 16:20 UTC

    Thanks for your help everyone!

    It turns out that the issue I was having had to do with the version of the FreeTDS driver I was using. The version I was using, 0.61, had an issue with NULL bindings that has since been fixed in versions 0.61.1 and newer.

    My systems administrator has upgraded our driver to version .82 and I am now able to insert NULL values.

[reply]

Back to Seekers of Perl Wisdom