undef usually maps to NULL. It looks like you're inserting the empty string which is not the same as undef.
David M
2010-05-18 16:50:40
A:
you tested the code? With only two fields?
MkV
2010-05-18 18:03:00
A:
The DBI package maps undef to NULL. (Perl's defined-ness vs. falseness logic is actually a pretty good fit for SQL's trinary logic.)
So, in your while loop, just check if the indicated field is an empty string, and if so, make it undef instead:
while (<>){
...
#Parse the fields.
@field=split(/\|/,$_);
if ( $field[1] eq '' ) {
# handle NULLs
$field[1] = undef;
}
#Do the insert.
$sth->execute($field[0],$field[1]);
}
friedo
2010-05-18 17:31:27
your if is never true, and if you put use strict; use warnings; on your code you get 'Use of uninitialized value $field[1] in string eq'
MkV
2010-05-18 17:53:23
@james, I'm not sure what you mean. It works fine with `strict` and `warnings` enabled and the conditional succeeds. `perl -Mstrict -Mwarnings -MData::Dumper -le 'my $str="a|b||d"; my @a = split /\|/, $str; if ( $a[2] eq "" ) { $a[2] = undef; }; print Dumper \@a'`
friedo
2010-05-18 19:02:00
It works now, but as the question was originally posed (and as you answered it), there were only 2 fields and the empty field was at the end, returning undef for that index.
MkV
2010-05-19 09:56:53
+1
A:
I'm not sure you tested whether your pasted code and data together, they work with Perl 5.10.1, DBD::Pg 2.15.1 and Postgres 8.4. Also you should use strict and warnings and not rely on package scope for your variables.
If you change your code and data to use three or more fields, leaving a non-terminal one empty, then you can trigger the error from DBD::Pg. Add a line like this to your code before executing the prepared statement:
map { $_ eq '' and $_ = undef } @field;
To map empty strings in @field to undef
MkV
2010-05-18 18:01:57
That took care of converting the empty strings to nulls. It's odd that removing a field in the input caused the empty string to become a null (at least that what appears to have happened).
User1
2010-05-18 19:41:24
If a string ends in the characters you are splitting on, like 'a|4|' then the final array element doesn't get filled in at all, not turned into an empty string, so it is undef, see perldoc -f split.
MkV
2010-05-19 09:54:19