Trimming an array (filled via DBI)

arrays, dbi, perl, sql-server

Solution

Your trim() function does not modify the list in place (nor it handles a list).

So, in real TIMTOWTDI fashion, you should either modify the function to return a new array:

sub trimArray {
    my @arr = @_;
    my @rv;
    for my $val (@arr) {
        $val =~ s/^\s+//;
        $val =~ s/\s+$//;
        push @rv, $val;
    }
    return @rv;
}

#and then

print OUTFILE join(",", trimArray(@re)), "\n";

or pass a reference to your function and then modify the array in place

sub trimInPlace {
    my $arrRef = shift;
    for my $val (@$arrRef) {
        $val =~ s/^\s+//;
        $val =~ s/\s+$//;
    }
}

#and then

trimInPlace(\@re); #Note the \
print OUTFILE join(",", @re), "\n";

or use map

#!/bin/perl
use warnings;
use strict;
use DBI;

#... the same

while (my @re = $sth->fetchrow_array) {
   print OUTFILE join(",", map { trim($_); } @re), "\n"; #Applies
                                                         #trim() to each element
}

#...

############## subroutines ##################
sub trim { #Don't use prototypes
    my $string = shift;
    $string =~ s/^\s+//;
    $string =~ s/\s+$//;
    return $string;
}

or try using chomp, by modifying $/, which will only remove a trailing space, nothing more.

#!/bin/perl
use warnings;
use strict;
use DBI;

#... the same

my $old_sep = $/;
$/ = " ";
while (my @re = $sth->fetchrow_array) {
   chomp(@re); #Modifies in place, returning number of changes
   print OUTFILE join(",", @re), "\n";
}
$/ = $old_sep;

Problem

I'm trying to read data from SQL Server database using Perl and the DBI module. My intention is to read the data and print it into a text file (comma separated). When I do this, I get the result like this: ``` var1,var2,var3 40406,20 ,783 50230,78 ,680 50230,78 ,680 50230,78 ,680 50230,78 ,680 ``` So there is a whitespace between the second variable data and the comma. I tried to trim this using the code below, but it did not work. How should I modify my code to get rid of those whitespaces? My code is here: ``` #!/bin/perl use warnings; use strict; use DBI; sub trim; my $dbs = "dbi:ODBC:DRIVER={SQL Server};SERVER={xxxx}"; my ($username, $password) = ('un', 'pwd'); my $dbh = DBI->connect($dbs, $username, $password) or die "Can't connect to $dbs: $DBI::errstr"; my $sth = $dbh->prepare("select var1, var2, var3 from db.dbo.table") or die "Can't prepare statement: $DBI::errstr"; $sth->execute(); my $outfile = 'temp.txt'; open OUTFILE, '>', $outfile or die "Unable to open $outfile: $!"; print OUTFILE join(",", @{$sth->{NAME}}), "\n"; while (my @re = $sth->fetchrow_array) { print OUTFILE join(",", trim(@re)), "\n"; } close OUTFILE; $sth->finish(); $dbh->disconnect(); ############## subroutines ################## sub trim($) { my $string = shift; $string =~ s/^\s+//; $string =~ s/\s+$//; return $string; } ```

Original source