Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Re:(5)Null fields

by busunsl (Vicar)
on Mar 27, 2001 at 21:37 UTC ( #67554=note: print w/replies, xml ) Need Help??

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

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'

Replies are listed 'Best First'.
(arturo) Re:(6)Null fields
by arturo (Vicar) on Mar 27, 2001 at 21:40 UTC

    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://67554]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (5)
As of 2018-10-17 17:54 GMT
Find Nodes?
    Voting Booth?
    When I need money for a bigger acquisition, I usually ...

    Results (96 votes). Check out past polls.