Re: Code flow not going to while loop

Consider replacing the hash of start/end dates with YEAR(date) and MONTH(date)functions.

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

my $sql =<< "SQL";
  SELECT distinct FID_CUST 
  FROM session 
  WHERE YEAR(DAT_END) = ? AND MONTH(DAT_END) = ?
SQL

my $dbh = get_dbh();
my $sth = $dbh->prepare($sql);    

my $year = 2017;    
for my $mth (1..9) {   
  print "$year-$mth\n";
  $sth->execute($year,$mth);   
  while (my ($fid_cust)= $sth->fetchrow_array ){
    print "$fid_cust\n";
  }
  $sth->finish();
}
$dbh->disconnect;


# connect
sub get_dbh{
  my $database = "xxx"; my $user = "xxx"; my $pw = "xxx";
  my $dsn = "dbi:DB2:$database"; 
  my $dbh = DBI->connect($dsn, $user, $pw, 
     { RaiseError=>1, AutoCommit=>1 } )
     or die "Could not connect to database :".DBI->errstr;
  return $dbh;
}
[download]

poj

Comment on Re: Code flow not going to while loop Select or Download Code

Replies are listed 'Best First'.
Re^2: Code flow not going to while loop by dipit (Sexton) on Aug 02, 2017 at 18:18 UTC
Hi, i have tried this also. Not working. #! /usr/bin/perl use DBI; use strict; my $dbh = DBI->connect("DBI:DB2:URTDB",'db2inst1','db2inst7',{ RaiseEr +ror => 0, AutoCommit => 1 }) or die ("Could not connect to database :".DBI->errstr); my $sql = "SELECT distinct FID_CUST FROM session WHERE YEAR(DAT_END) = + ? AND MONTH(DAT_END) = ?"; =head my %hash = ( '2017-01-01 00:00:00' => '2017-01-31 00:00:00', '2017-02-01 00:00:00' => '2017-02-28 00:00:00', '2017-03-01 00:00:00' => '2017-03-31 00:00:00', '2017-04-01 00:00:00' => '2017-04-30 00:00:00', '2017-05-01 00:00:00' => '2017-05-31 00:00:00', '2017-06-01 00:00:00' => '2017-06-30 00:00:00', '2017-07-01 00:00:00' => '2017-07-31 00:00:00', '2017-08-01 00:00:00' => '2017-08-31 00:00:00', '2017-09-01 00:00:00' => '2017-09-30 00:00:00' ); =cut #open(my $fh , "+>/var/www/bin/filesample.txt"); my $sth = $dbh->prepare($sql); my $year = 2017; foreach my $month(1..9) { print "$year-$month\n"; $sth->execute($year,$month) or die $DBI::errstr; while (my $fid_cust= $sth->fetchrow_arrayref()) { print "in while\n"; print "@$fid_cust\n"; } $sth->finish(); } #close FH; $dbh->disconnect; [download]	[reply] [d/l]
Re^3: Code flow not going to while loop by poj (Abbot) on Aug 02, 2017 at 18:51 UTC
Do you have any records in that date range ?. Check with this `#!/usr/bin/perl use DBI; use strict; my $sql =<< "SQL"; SELECT YEAR(DAT_END),MONTH(DAT_END),COUNT(*) FROM session GROUP BY YEAR(DAT_END),MONTH(DAT_END) SQL my $dbh = get_dbh(); my $ar = $dbh->selectall_arrayref($sql); print "YEAR\tMONTH\tCOUNT\n"; for (@$ar){ print join "\t",@$_,"\n; }; $dbh->disconnect;` [download] poj	[reply] [d/l]