NULLs in Text::CSV_XS and DBD::CSV
Ronald J Kimball wrote: CAMPBELL, BRIAN D (BRIAN) [mailto:[EMAIL PROTECTED] wrote: I'm guessing that when a field is empty in your CSV file, then the method $csv-fields puts an empty string value (e.g. ) in the corresponding element in your @data array (not an undef value). Yes, currently Text::CSV_XS returns empty string for both empty strings and NULLs. I am considering providing an option to differentiate the two and return undef for NULLs. I'm used to Oracle, which treats an empty string as NULL. Is it different for MySQL? DBD::CSV also currently treats empty string as NULL, that may change if I make the change to Text::CSV_XS. -- Jeff
RE: NULLs in Text::CSV_XS and DBD::CSV
-Original Message- Jeff Zucker [mailto:[EMAIL PROTECTED] wrote: Yes, currently Text::CSV_XS returns empty string for both empty strings and NULLs. I am considering providing an option to differentiate the two and return undef for NULLs. How do you distinguish between empty string and NULL in a text file? In field1,,field3 is the second field an empty string or NULL? Ronald
Re: NULLs in Text::CSV_XS and DBD::CSV
Jeff Zucker wrote: I forgot to mention that DBD::AnyData (which also handles CSV and pipe delimited formats) returns undef for NULL, so if you use DBD::AnyData or plain AnyData you can get the undefs you want. Using plain AnyData, this will generate use of unitialized value warning for all NULL fields e.g. a||b, the second of the three fields is NULL and returns undef. use AnyData; my $table = adTie('Pipe',$pipe_file); while (my $row = each %$table) { for (keys %$row) { printf %s , $row-{$_}; } print \n; } -- Jeff
Re: NULLs in Text::CSV_XS and DBD::CSV
Ronald J Kimball wrote: Jeff Zucker [mailto:[EMAIL PROTECTED] wrote: Yes, currently Text::CSV_XS returns empty string for both empty strings and NULLs. I am considering providing an option to differentiate the two and return undef for NULLs. How do you distinguish between empty string and NULL in a text file? In field1,,field3 is the second field an empty string or NULL? 1,,2 1,,2 Obviously, it would only work on files created with the DBD and if the user inserted empty string when they meant empty string and NULL when they meant NULL. -- Jeff
Re: NULLs in Text::CSV_XS and DBD::CSV
Jeff Zucker wrote: 1,,2 1,,2 In case I was unclear: the first is three fields with the second field NULL, the second is three fields with the second field an empty string. -- Jeff
RE: NULLs in Text::CSV_XS and DBD::CSV
Still banging my head into a wall... Now I am getting NULLs inserted as expected into the database, but I am getting errors on the compare. my @old = $test-fetchrow_array (); foreach $n (0..20) { chomp($file_val = $data[$n]); $file_val =~ s/\s*$//; #chomp($db_val = $old[$n+1]); #if ($file_val eq ) { # print NULL found in $n value. $file_val\n; # $file_val = 0; #} # print Comparing $file_val to $db_val. \n; # Testing line if (defined $old[$n+1]) { if ($file_val eq $$old[$n+1]) { $update = 1; } else { $update = 0; # print Comparing $file_val to $db_val. \n; # Testing line last; } } } Here are the errors. Name main::old used only once: possible typo at ./host_tbl_update2.pl line 65. Use of uninitialized value in string eq at ./host_tbl_update2.pl line 65, CSV line 1. Use of uninitialized value in string eq at ./host_tbl_update2.pl line 65, CSV line 2. DBD::mysql::st execute failed: Column 'Vantive_HW' cannot be null at ./host_tbl_update2.pl line 80, CSV line 2. I am pretty much confused by all of this. That is until I was typing this up and noticed I had typo'd an extra '$' on the comparison line. Maybe now all will be good. Thanks again. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -Original Message- From: Jeff Zucker [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 16, 2005 11:49 AM Cc: dbi-users@perl.org Subject: Re: NULLs in Text::CSV_XS and DBD::CSV Jeff Zucker wrote: 1,,2 1,,2 In case I was unclear: the first is three fields with the second field NULL, the second is three fields with the second field an empty string. -- Jeff
Re: NULLs in Text::CSV_XS and DBD::CSV
On 03/16/2005 09:50 AM, NIPP, SCOTT V (SBCSI) said: Still banging my head into a wall... Now I am getting NULLs inserted as expected into the database, but I am getting errors on the compare. my @old = $test-fetchrow_array (); foreach $n (0..20) { chomp($file_val = $data[$n]); $file_val =~ s/\s*$//; chomp() is unneeded if it's followed by s/\s*$//. #chomp($db_val = $old[$n+1]); chomp() is unneeded and probably dangerous for values fetched from the database. Is there a reason you are fetching an extra column from the database? You're comparing column[1] to field[0] ... column[21] to field[20]. #if ($file_val eq ) { # print NULL found in $n value. $file_val\n; # $file_val = 0; #} # print Comparing $file_val to $db_val. \n; # Testing line if (defined $old[$n+1]) { if ($file_val eq $$old[$n+1]) { You already noticed the extra '$'. Is this line 65? $update = 1; } else { $update = 0; # print Comparing $file_val to $db_val. \n; # Testing line last; } } } Here are the errors. Name main::old used only once: possible typo at ./host_tbl_update2.pl line 65. Use of uninitialized value in string eq at ./host_tbl_update2.pl line 65, CSV line 1. Use of uninitialized value in string eq at ./host_tbl_update2.pl line 65, CSV line 2. You probably need to change the NULLs you are now getting in the database back to . I'd feel better about my diagnosis if I knew which line is line 65. Another possiblity is that $db_val is being used somewhere, but you've commented out where it was set. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.