in reply to how to save data to new array after retrieving from sql server

selectall_arrayref() combines "prepare", "execute" and "fetchall_arrayref" into a single call. It returns a reference to an array containing a reference to an array for each row of data fetched.

Also, you could remove the trailing spaces with RTRIM

#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use DBI;

# establish the connection
my $dbh = DBI->connect('dbi:ODBC:myDSN','username','userpassword',
      { RaiseError => 1, 
        PrintError => 1 } )
   or die "Could not connect to database: $DBI::errstr";
   
# sql query statement
my $sql =<< "SQL";
  SELECT RTRIM(location),RTRIM(CompletionStatus) 
  FROM ewNetworkFaults
  WHERE Type like '%Audio Visual%'
    AND CompletionStatus not like '%Completed%'
SQL
  
my $table = $dbh->selectall_arrayref($sql);
print Dumper($table);

[download]
poj

Replies are listed 'Best First'.
Re^2: how to save data to new array after retrieving from sql server
by mhoang (Acolyte) on Jul 28, 2017 at 01:36 UTC

    Hi Pol Let say I like to build a hash from that array reference. I wrote like this, but I got error. where am i wrong?

    my %hdata = ();
for my $i (0 .. $#{$table}) {
print $#{$table}; ### why cant find the length of array here
my $location = $table[i]->[0];
my $room = $table[i]->[1];
push $hdata{$location}=$room;    
}

    [download]
    Nirvana is Now or Never
[reply]
[d/l]
      where am i wrong

      $#{$table} should be either $#${table} or just $#$table

      $table is an arrayref so these 

        my $location = $table[i]->[0]; # missing $ on i
  my $room = $table[i]->[1];

      [download]
      should be 
        my $location = $table->[$i][0];
  my $room     = $table->[$i][1];

      [download]

      and push is for building an array not a hash, so this

      push $hdata{$location}=$room;

      should be

      $hdata{$location} = $room;

      or alternatively

      #!/usr/bin/perl
use strict;
use Data::Dump 'pp';
my $table = [
 ['Wellbeing Office', 'Pending'],
 ['Library','Pending'], ['Y219','InProgress'],
 ['B201','InProgress'], ['B108','InProgress'],
 ['LAB1','InProgress'], ['C303','InProgress'],
];

my $last_index = $#${table};
my $count = scalar @$table;
print "
records    = $count 
last index = $last_index\n";

#my %hdata = ();
#for my $i (0..$last_index){
#  my $location = $table->[$i][0];
#  my $room     = $table->[$i][1];
#  $hdata{$location} = $room;    
#}
#pp \%hdata;

my %hdata = ();
for my $row (@$table){
  my $location = $row->[0];
  my $status   = $row->[1];
  $hdata{$location} = $status;    
}
pp \%hdata;

      [download]
      poj
[reply]
[d/l]
[select]

        Hello All, I follow all your advice and finally I got the HTML page that I want with the cell change color based on condition, now I do not want table width = 100% and I want fix size for all the cells I am stucked. When I changed the table width to auto and cells to some px all the headings just jumps. any idea please? 

        #!/usr/bin/perl
use strict;
use warnings;
use Time::Piece;   

#for testing
my $dest = "C:/Perl-Script/Network Faults/";

# current date/time
my $t = localtime;
my $date = $t->wdayname.' '.$t->dmy('/');
my $time = $t->hms;

# input data
my %hdata = build_hash($dest."RoomName.txt");
my $href = build_sqlData();
my @lrooms=();

foreach my $room (sort keys  %{$href}){
    #print "room is $room\n";
    #print "status is ${$href}{$room}\n";
    push @lrooms, $room;
    #print "num is @lrooms\n";
}

# output html
open OUT, ">". $dest."ICT Report.html" or die "$!";
print OUT qq(
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
  "http://www.w3.org/TR/html4/loose.dtd"><html>
<head>
   <title>Bulletin</title>
    <style type="text/css">
     h4   { color: #0; font-size: 1em; }  
   </style>     

    <meta name="viewport" content="width=device-width, initial-scale=1
+">
    <link rel="stylesheet" type='text/css' media='screen' href="/Perl-
+Script/Extra/w3.css"> 
    
</head>
<body>
    <div class="w3-container w3-blue">
    <h4>NewYork Time</h4>
    <h4>Report Date $date  $time</h4>
    </div>
    <div class="w3-control-bar"></div>

);

foreach my $key (sort keys %hdata) {
        
      my $h4 = $key;
    $h4 =~ s/[\"\$]//g;
    print OUT qq!<h4>$h4</h4>
    <table border="1" width="100%" style="border-color:#e0e0e0" cellpa
+dding="5" align="left">\n!; #how to change width of table here????
                
    my @arrays = @{$hdata{$key}}; 
    for my $i (1..$#arrays) {
      print OUT '<tr>';
      my @col = split(/,/,$arrays[$i]);
                    
      for my $j (0..$#col) {
        $col[$j] =~ s/\"Period\"/\"Class\"/g;   
        $col[$j] =~ s/"//g;
        my $bg = "bgcolor=#e0e0e0";
        #print @lrooms;

         if ($col[$j] ~~ @lrooms) {
              $bg = (${$href}{$col[$j]} =~ /Pending/) ? "bgcolor=#FF00
+00" :  
                     (${$href}{$col[$j]} =~ /InProgress/) ? "bgcolor=#
+FFFF00" :
                  "";        
         }

        print OUT "<td $bg align=left>$col[$j]</td>"; # how to set the
+ fixed width of cells ???
      };
      print OUT "</tr>";      
    };
    print OUT "</table>&nbsp;<br>\n";
}
print OUT "</body></html>";
close OUT;

#-------------------------------------
# This subroutine split text file to 
# a hash of arrays for data processing
# -------------------------------------
sub build_hash{
  
  my $infile = shift;
  my %data;
  my $last ='';
  open my $ifh,'<',$infile or die "$!";
  while (my $line=<$ifh>) { 
    chomp $line; 
    if ( substr($line,0,2) eq '"$' 
      || substr($line,0,1) eq '$') { 
      $last = $line; 
    } 
    push @{$data{$last}},$line;   
  }
  close $ifh;
  return %data;
}

#--------------------------------------------
# This subroutine extracts data from sql server 
# to an array of arrays for data processing
# -------------------------------------------
sub build_sqlData{
use Data::Dumper;
use DBI;

# establish the connection
my $dbh = DBI->connect('dbi:ODBC:myDSN','user','pass',
      { RaiseError => 1, 
        PrintError => 1 } )
   or die "Could not connect to database: $DBI::errstr";
   
# sql query statement
my $sql =<< "SQL";
  SELECT RTRIM(location),RTRIM(CompletionStatus) 
  FROM ewNetworkFaults
  WHERE Type like '%Room Audio Visual%'
    AND CompletionStatus not like '%Completed%'
SQL
  
my $table = $dbh->selectall_arrayref($sql);
print Dumper $table;

my $last_index = $#${table};
my $count = scalar @$table;
print "
records = $count
last_index = $last_index\n";
my %hdata;
for my $row (@$table) {
    my $location = $row->[0];
    my $status = $row->[1];
    $hdata{$location} = $status;
}

#print Dumper \%hdata;
return \%hdata;
}

        [download]
        Nirvana is Now or Never
[reply]
[d/l]
      where am i wrong?
      ...
      print $#{$table}; ### why cant find the length of array here

      Your syntax for "highest array index" by reference is correct (as are the more common variations given by poj here), but be aware of the fact (pointed out by poj) that the highest array index is not the same as the array length (i.e., number of elements) for the default value of  $[ (see perlvar). (BTW:   Don't ever touch $[   :) 

      c:\@Work\Perl\monks>perl -wMstrict -le
"my $ar = [ qw(a b c d e) ];
 ;;
 print $#{ $ar };
 print $#${ ar };
 print $#$ar;
"
4
4
4

      [download]

      For readability and maintainability, I tend to prefer the for-loop approach used by poj for populating a hash from an array or array reference, but here's a variation that's a bit more terse: 

      c:\@Work\Perl\monks>perl -wMstrict -MData::Dump -le
"my $table = [
  ['Wellbeing Office', 'Pending'],
  ['Library','Pending'], ['Y219','InProgress'],
  ['B201','InProgress'], ['B108','InProgress'],
  ['LAB1','InProgress'], ['C303','InProgress'],
 ];
 ;;
 my %hdata =
   map @{ $_ }[0, 1],
   @$table
   ;
 ;;
 dd \%hdata;
"
{
  B108 => "InProgress",
  B201 => "InProgress",
  C303 => "InProgress",
  LAB1 => "InProgress",
  Library => "Pending",
  "Wellbeing Office" => "Pending",
  Y219 => "InProgress",
}

      [download]

      Give a man a fish:  <%-{-{-{-<

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom