Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

getting 0 to print

by rbc (Curate)
on Jan 13, 2003 at 20:25 UTC ( #226582=perlquestion: print w/ replies, xml ) Need Help??
rbc has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

I have am writing a script that will create sqlldr control
files for me. I am having trouble printing 0.
I am sure this a common problem that you wise monks have
seen before and have overcome with easy.
Here's my script:
#!/usr/bin/perl -w use strict; use DBI; my $table = shift; my $dbuser = shift; my $dbpass = shift; my $dbname = shift; my $dbd = 'Oracle'; my $dbh = DBI->connect ( $dbname, $dbuser, $dbpass, $dbd); if (!$dbh) { die "$0 Error conecting to DB ($dbuser/$dbpass\@$dbname); $DBI::er +rstr\n"; } my $SQL = <<SQL; Select * from $table SQL $dbh->{LongReadLen} = 65500; my $sth = $dbh->prepare($SQL) || die $dbh->errstr; $sth->execute(); my $colNames = $sth->{'NAME'}; my $nColumns = $sth->{'NUM_OF_FIELDS'}; print <<DOIT; OPTIONS (ROWS=1) LOAD DATA INFILE * REPLACE INTO TABLE $table FIELDS TERMINATED BY '|' TRAILING NULLCOLS ( DOIT my $colString; for my $colName ( @$colNames ) { $colString .= "\n\t$colName,"; } chop($colString); print "$colString\n"; print<<DOIT; ) BEGINDATA DOIT while ( my @r = $sth->fetchrow_array ) { # print join ( '|', @r ); # This causes another bug of putting 0's where # I wanted || outputted :( # print join ( '|', map ( $_ || '0', trim( @r )) ); # print join ( '|', map ( $_, trim( @r )) ); print "\n"; } $sth->finish || die; $dbh->disconnect; sub trim { my @out = @_; if ( !defined(@_)) {return "";} if ( $#out == -1 ) {return "";} for(@out) { #next if !($_); if (!($_)) { $_ = ""; } s/^\s+//; s/\s+$//; s/\|//g; s/\n//g; s/\cM//g; s/\r//g; } return wantarray ? @out : $out[0]; }
And here is a example of the bug:
OPTIONS (ROWS=1)
LOAD DATA 
INFILE *
REPLACE INTO TABLE DECODE_TABLE
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(

        VALUE,
        DESCRIPTION
)
BEGINDATA
1|Something
2|Something else
3|Sometimes
4|Never
5|Always
6|Only once in a while
7|Once in a bluemoon
|All the time
Note the line ...

|All the time

... should of been outputted as ...

0|All the time

As per this query below ...
$ sqlplus blah...

SQL> select * from DVAL_ADR_AVAIL
  2  /

V DESCRIPTION
- ------------------------------
1 Something
2 Something else
3 Sometimes
4 Never
5 Always
6 Only once in a while
7 Once in a bluemoon
0 All the time

8 rows selected.
Please help! Thanks :)

Comment on getting 0 to print
Download Code
Re: getting 0 to print
by RMGir (Prior) on Jan 13, 2003 at 20:39 UTC
    I think the culprit is in sub trim:
    if (!($_)) { $_ = ""; }
    You're asking it to make 0's go away...
    --
    Mike
      Thank you wise monk yet if I modify my sub trim to ...
      55 sub trim { 56 my @out = @_; 57 if ( !defined(@_)) {return "";} 58 if ( $#out == -1 ) {return "";} 59 for(@out) { 60 #next if !($_); 61 #if (!($_)) { $_ = ""; } 62 s/^\s+//; 63 s/\s+$//; 64 s/\|//g; 65 s/\n//g; 66 s/\cM//g; 67 s/\r//g; 68 } 69 return wantarray ? @out : $out[0]; 70}
      ... I get these messages ...
      Use of uninitialized value in join or string at ./b.pl line 49
      Use of uninitialized value in join or string at ./b.pl line 62
      Use of uninitialized value in join or string at ./b.pl line 63
      ... etc 
      
      I would like to not have any extraneous output.
      Any suggestion?

        You appear to be passing undefined values to your trim routine. If you want to substitute an empty string for undefined values, test with defined. if (!$_) is true for 0 and undefined values and your code was substituting an empty string for either. I think you only wanted the substitution for undefined values.

        59 for(@out) { 60 #next if !($_); 61 if (!defined $_) { $_ = ""; } 62 s/^\s+//; 63 s/\s+$//; 64 s/\|//g; 65 s/\n//g; 66 s/\cM//g; 67 s/\r//g; 68 }
        --- print map { my ($m)=1<<hex($_)&11?' ':''; $m.=substr('AHJPacehklnorstu',hex($_),1) } split //,'2fde0abe76c36c914586c';
Re: getting 0 to print
by dws (Chancellor) on Jan 13, 2003 at 20:41 UTC
    I am having trouble printing 0.

    Here's some meta-help. When faced with problems like this, it is very good practice to try to reduce your code to a small test case that illustrates the problem. 90+% of the time, the answer will jump out at you once the clutter is cleared, and you've posted a lot of clutter. For the remaining 10-% of cases, the fact of posting a small example will increase the chances of getting help. Many people shy away from digging through big blobs of unrelated code.

    In the case of your script, the fact of data coming out of a database is irrelevant, as you might note when you attempt to demonstrate the problem in a small snippet.

      I have to disagree with you.

      The fact the data is coming from a database is very
      relevant and key to the solution to this problem.

      Instead of trying to output the data coming from the query:
      select * from $table
      
      ... I decided to deal with the formatting of the output
      in SQL like so ...
      select $cols from $table
      
      ... where $cols is built up with ltrim, rtrim and ||
      and the previous query. $cols for this particular
      example would look like this ...
      ltrim(rtrim(VALUE)) || '|' || ltrim(rtrim(DESCRIPTION)) || '|'
      
      Thanks for your input anyways.
      Sorry your meta-help couldn't of been of more use.
        ...and I have to disagree with you. The fact that your DB formats the output a certain way is merely a convenience. Your data could have come from a delimited file and it would have been all the same. You had a broken piece of logic in if($_){do stuff}, because your particular $_ was 0, which evaluates to false.

        thor

        The fact the data is coming from a database is very relevant and key to the solution to this problem.

        No, the database is incidental. Let's do a remedial debugging review to see why.

        You notice that '0' is getting printed as ''. Having seen a zero come back when you ran the query in a command-line tool, you suspect that the issue isn't the database, so you work from the end backwards. You note that the path between the '0' that you have and the print that is printing as an empty string includes a join, a map, and a call to trim.

        Knowing (or strongly suspecting) that join and map don't zap zeros, you direct your attention to trim. What comes back when we pass a '0' to trim()? An empty string!

        Voila. The database (and most the rest of the script) is now out of the loop completely. You have isolated the problem to a single subroutine.

        Assuming that you're unable to discern how trim() could possible turn a zero into an empty string, you nonetheless have a small snippet to post.

Re: getting 0 to print
by Enlil (Parson) on Jan 13, 2003 at 20:43 UTC
    In this statement:
    print join ( '|', map ( $_ || '0', trim( @r )) );
    You can't really use || 0 as the 0 will result in false. something like this should work though:
    print join ( '|', map ( $_ ? $_ : '0', trim( @r )) );
    Note this is untested code. but what this does is check for the "trueness" of $_ and if it is true return it otherwise return 0

    -enlil

Re: getting 0 to print
by Arien (Pilgrim) on Jan 13, 2003 at 23:21 UTC

    In addition to other changes, you may want to write trim like this:

    sub trim { my @out = wantarray ? @_ : shift; return "" unless @out; for (@out) { $_ = "" => next unless defined; tr/|\n\r//d; s/^\s+|\s+$//g; } return @out; }

    — Arien

      Oooh! that work too! thanks!

      I have never seen this ...
      $_ = "" => next unless defined;
      ... before. Could you explain how that works.
      It would be most enlightening.

        I have never seen this ...

        $_ = "" => next unless defined;

        ... before. Could you explain how that works.

        I'm not sure what part puzzles you, so...

        • defined implicitly refers to $_.
        • unless (...) { ... } can be written as ... unless ... if the block is just a simple statement.
        • ... => ... is another way to write ... , ... (apart from the fact that => will autoquote any word left of it).
        • In scalar context (ie the way used here) the comma (in either notation) evaluates its both arguments from left to right (and returns the value value of the last argument evaluated).

        (You can find all details in perlsyn and perlop.)

        In other words, that line is just one of the many variations of writing:

        unless (defined $_) { $_ = ""; next }

        — Arien

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://226582]
Approved by pfaut
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (10)
As of 2014-10-21 19:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (108 votes), past polls