Substituting '--' for Nulls

johnirl has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks
I am currently working on a program that retrieves results from a database. However when my program retrieves a null result, instead of printing a dash ('-') as I would like it to, it prints the error "Use of uninitialized value".
My question is how do I print out a dash each place there is a null?

my $dbh = DBI->connect('DBI:DB2:dbname', 'username', 'password.')
    or die "Couldn't open database: $DBI::errstr; stopped";

#Prepare the SQL query for execution
my $sth = $dbh->prepare(<<End_SQL) or die "Couldn't prepare statement:
+ $DBI::errstr; stopped";
SELECT values FROM table;
End_SQL

# Execute the query
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stop
+ped";

# Fetch each row and print it
while ( my (@result) = $sth->fetchrow_array() ) {
        my $result_to_print = join(",",@result);
        print $result_to_print . "\n";
        }

# Disconnect from the database
$dbh->disconnect();
[download]

j o h n i r l .

Sum day soon I'Il lern how 2 spelI (nad tYpe)

Comment on Substituting '--' for Nulls Download Code

Replies are listed 'Best First'.
Re: Substituting '--' for Nulls by Abigail-II (Bishop) on Aug 13, 2002 at 10:09 UTC
Why would you expect it to print a dash? You never even try to substitute a dash. Use something like: `print map {defined () ? $_ : "--"} @results; print "\n";` [download] as the body of your `while` loop. And with the latest development version, you can write that as `print map {$_ // "--"} @results; print "\n";` [download] Abigail	[reply] [d/l] [select]
Re: Re: Substituting '--' for Nulls by virtualsue (Vicar) on Aug 13, 2002 at 12:24 UTC
I believe the main test here needs to be for "", not for definedness. DBI is going to give him an array, and some of the values in it may be empty strings, if I understand the problem correctly. The empty strings are what needs to be replaced. I think this variation will do the job: `my @results_tmp = map {($_ ne "") ? "$_" : "--"} @results; print join(",", @results_tmp),"\n";` [download] For those who aren't comfortable with map, this is about the same as: `my @results_tmp = @results; ($_ eq "") && ($_ = "--") for @results_tmp; print join(",", @results_tmp),"\n";` [download]	[reply] [d/l] [select]
Re: Substituting '--' for Nulls by Abigail-II (Bishop) on Aug 13, 2002 at 12:40 UTC
Could you give us an example where Perl issues the warning "Use of uninitialized value" where the value is actually defined and equal to `""`? Abigail	[reply] [d/l]
Re: Re: Re: Substituting '--' for Nulls by Anonymous Monk on Aug 13, 2002 at 12:42 UTC
The error "use of uninitialized value" described in the root node is a good indication that DBI was, in fact, properly handing back undefined values that were then being used as strings.	[reply]
Re: Re: Substituting '--' for Nulls by johnirl (Monk) on Aug 13, 2002 at 10:26 UTC
Thanks Abigail I have tried a couple of subroutines to do it but they didn't work so I ommitted them from the code. Didn't want to embarass myself :-) j o h n i r l . Sum day soon I'Il lern how 2 spelI (nad tYpe)	[reply]
Re: Substituting '--' for Nulls by rattusillegitimus (Friar) on Aug 13, 2002 at 14:57 UTC
Not knowing DB2 myself, I may be wrong, but couldn't you do something like the following (untested) SQL statement to make the database to the substitution for you? `SELECT if(field,field,'--') as field FROM table;` [download] MySQL also has an IFNULL expression, but again, I don't know enough about DB2 to know if it has one too. - $0.02 from the rat `__________` He seemed like such a nice guy to his neighbors / Kept to himself and never bothered them with favors - Jefferson Airplane, "Assassin"	[reply] [d/l]
Re: Substituting '--' for Nulls by johnirl (Monk) on Aug 13, 2002 at 12:53 UTC
Thanks Abigail and Sue but your both right. I'm using DB2 which defines null, "0" or omitted entries as null. So all are defined by the DBMS even if they are not actually "defined". So both your solutions are correct as DB2 manages to return nulls as undefined using sues solution and Abigails works as the columns are defined. Basically my problem is solved and I thank you for your help. Being presumptious and presuming you're both female :-X big kiss for all your help. j o h n i r l . Sum day soon I'Il lern how 2 spelI (nad tYpe)	[reply]


Just another Perl shrine
	PerlMonks