http://www.perlmonks.org?node_id=226582

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 :)

Replies are listed 'Best First'.
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.

    A reply falls below the community's threshold of quality. You may see it by logging in.
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

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