Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Re: Re: Re: Re: Null fields

by Tyke (Pilgrim)
on Mar 27, 2001 at 20:16 UTC ( #67526=note: print w/replies, xml ) Need Help??

in reply to Re: Re: Re: Null fields
in thread Null fields

Ah, but I always understood that '' was not the same as null, at least for strings. After all '' ne undef in Perl.

On the other hand, I also believe that this distinction is database dependent :-(

Update: As merlyn rightly points out, '' eq undef is true. I goofed.

Replies are listed 'Best First'.
Re: Re: Re: Re: Re: Null fields
by merlyn (Sage) on Mar 27, 2001 at 21:25 UTC

      But just to be absolutely clear about it, even though '' eq undef, defined('') == 1 while defined(undef) == 0. Which is why that function is there, I suppose.

      Philosophy can be made out of anything. Or less -- Jerry A. Fodor

Re:(5)Null fields
by busunsl (Vicar) on Mar 27, 2001 at 21:37 UTC
    You're right, '' is not the same as NULL, at least in databases following the ANSI standard.
    This way you can always distinguish between 'something entered in this column, even if it's nothing'
    and 'nothing entered in this column'

      Heh, how long this thread has gone on ... so, to distinguish null from empty string in CSV, you could to

      my $csv = qq{foo,,bar,''};

      And write your code so that the second item in that list is NULL, while the fourth is a blank string. Unfortunately I don't know if there's a standard for that or not. I suppose I should try both in DBD::CSV and see what pops up. (update down the road)

      as promised here's some code ... I think the upshot is that DBD::CSV doesn't distinguish between blank strings and undef values, but I can't tell because the code isn't working ATM. posted for educational purposes only.

      #!/usr/bin/perl -w use DBI; use strict; my $db = DBI->connect("DBI:CSV:f_dir=/home/arturo/testing") or die "Cannot connect: $DBI::errstr\n"; =pod # used to create the table my $sth = $db->prepare("CREATE TABLE foo (id INTEGER, firstname VARCHAR(32), lastname VARCHAR(32) )" +); $sth->execute; $sth = $db->prepare("INSERT INTO foo (id, firstname, lastname) VALUES +(?,?,?)"); $sth->execute(1, 'Charo', ''); $sth->execute(2, 'Wynonna', undef); $sth->execute(3, undef, 'Cher'); $sth->execute(4, '', "Sting"); # note: Charo has a blank last name, Wynonna has an undef one # Cher has undef first name, Sting has blank first name =cut my $sth = $db->prepare("SELECT id, firstname, lastname FROM foo") or die "Cannot prepare: $DBI::errstr\n"; # next line currently causes an error on my system # (fresh installs of all the modules on which DBD::CSV depends) # UPDATE the answer, of course, is to execute it first # /me LARTs himself $sth->execute(); while ( my ($id, $first, $last) = $sth->fetchrow_array() ) { print "First name '$first' is " . ($first eq '' && defined $first) ? "blank" : "undefine +d"; print "Second name '$last' is " . ($last eq '' && defined $last ) ? "blank" : "undefined +"; } $db->disconnect;

      As I mentioned, looking at the file that got created, I don't see a difference between blank and undef, EXCEPT in the first two cases: the "Charo" line has a trailing comma, the "Wynonna" line does not. And thanks to busunsl for pointing out I forgot to execute my statement (sigh, it's not the best of days for me when it comes to details).

      Philosophy can be made out of anything. Or less -- Jerry A. Fodor

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://67526]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (6)
As of 2017-05-27 01:27 GMT
Find Nodes?
    Voting Booth?