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!

In reply to DBI Inserting undef as literal string 'null' by marmanold

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.