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)

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]
      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]
        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]
        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]
      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]

Back to Seekers of Perl Wisdom