On Mon, 10 Nov 2014 20:20:08 -0500, "Daniel Kasak via RT"
<bug-Text-CSV_XS@rt.cpan.org> wrote:
Show quoted text> I'm investigating using Text::CSV_XS to write data from a database to a
> pipe, for loading into another database. There doesn't seem to be a way to
> represent NULL values. Example:
>
> Whether I use "quote_null => 0" or "quote_null => 1", I get the same output:
>
> " o n e " , " 2 " , , " f o u r "
> 22 6F 6E 65 22 2C 22 32 22 2C 2C 22 66 6F 75 72 22
quote_null is all about the NULL *BYTE* ((char)0, '\0') not about the
NULL as used in databases, which has no representation other than
undefined (undef in perl speak and as documented in DBI)
Show quoted text> ... noting the "2C 2C" sequence of commas above. I would expect to see a
> NULL byte character: 0x00 in between the commas. Is this a bug? Am I doing
> it wrong?
,, is exactly what you are supposed to see. Note the difference with
the empty string:
1,""0",,"",b
^ ^^----- empty string
| +------ NULL
+---------- NULL byte
you correctly achieved that with always_quote or quote_always
You will now be able to detect the difference when parsing the data on
the receiving side by using the attribute "blank_is_undef => 1"
blank_is_undef
my $csv = Text::CSV_XS->new ({ blank_is_undef => 1 });
$csv->blank_is_undef (0);
my $f = $csv->blank_is_undef;
Under normal circumstances, "CSV" data makes no distinction between
quoted- and unquoted empty fields. These both end up in an empty
string field once read, thus
1,"",," ",2
is read as
("1", "", "", " ", "2")
When writing "CSV" files with "always_quote" set, the unquoted
empty field is the result of an undefined value. To enable this
distinction when reading "CSV" data, the "blank_is_undef"
attribute will cause unquoted empty fields to be set to "undef",
causing the above to be parsed as
("1", "", undef, " ", "2")
undef in DBI speak is what NULL is in perl speak:
undef NULL values are represented by undefined values in Perl
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");
I'm rejecting this as a bug
Show quoted text> Thanks
>
> Dan
--
H.Merijn Brand
http://tux.nl Perl Monger
http://amsterdam.pm.org/
using perl5.00307 .. 5.21 porting perl5 on HP-UX, AIX, and openSUSE
http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/
http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/