Trimming an array (filled via DBI)

Question

Hello,

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;
}

Answer 1

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;
    my @arr = @$arrRef;
    for my $val (@arr) {
        $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;

Answer 2

Thanks, although your code did not give the result I'm expecting. It produces:

var1,var2,var3
1
1
1
1
1
1
1
1
0
etc...

Answer 3

That worked, many thanks!!!

Answer 4

You could also check to see if DBD::ODBC supports the ChopBlanks attribute:

my $dbh = DBI->connect($dbs, $username, $password, { ChopBlanks => 1 } )

the ChopBlanks attribute trims the trailing whitespace of any CHAR fields (that is if your driver supports it ... I'm not sure if DBD::ODBC does).

Answer 5

Why does that field have trailing whitespace? Usually that points to some sort of problem with the database model. Besides your trim() function, you might investigate why the data is dirty.