in reply to Code flow not going to while loop

Good grief -- I didn't even notice that you weren't using placeholders. I was too focused on sorting out the array error.

Short answer: Always use placeholders. Your code should look more like this: 

my $sql = "select distinct FID_CUST from session " .
          "where DAT_END between ? and ?";
print "sql is $sql\n";

my $sth = $dbh->prepare($sql);
$sth->execute( $key, $hash{$key} ) or
  die "Couldn't execute statement: $DBI::errstr";

[download]
Finally, in general, it's better programming practice (in any language) to deal with references and not arrays. In this context, getting the arrayref is better than asking for an array.

Alex / talexb / Toronto

Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

Replies are listed 'Best First'.
Re^2: Code flow not going to while loop
by dipit (Sexton) on Aug 02, 2017 at 15:44 UTC

    Hey, i have tried your solution but it is not working. Not printing while loop statements. 

    my $dbh = DBI->connect("DBI:DB2:xxx",'xxx','xxx',{ RaiseError => 0, Au
+toCommit => 1 })
or die ("Could not connect to database :".DBI->errstr);

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'
);

#open(my $fh , "+>/var/www/bin/filesample.txt");

foreach my $key(sort keys %hash) {
chomp($key);
my $sql = "select distinct FID_CUST from session where DAT_END between
+ ? and ?";
chomp($sql);
#print "\$sql = $sql\n";

my $sth = $dbh->prepare($sql);
$sth->execute($key,$hash{$key}) or die "Couldn't execute statement: $D
+BI::errstr";
print "sth: $sth\n";

while (my $arr = $sth->fetchrow_arrayref()){
print "in while\n";
print "@$arr\n";
        }
$sth->finish();
}
#close FH;
$dbh->disconnect;

    [download]
[reply]
[d/l]
      Not printing while loop statements.

      This is what you should expect if your query returns no matching rows. Use a query which you know will return a dataset first.

      As you are now using placeholders (which is good) consider preparing the query outside the foreach loop. It makes no sense to re-prepare the exact same query over and over.

[reply]
        Man, i have run the same query in my database :p and it is running fine. Even here, if i use some select * statement, it is working fine. The datatype of these columns are 'TIMESTAMP' in my DB, may be there is some problem with that while parsing but again not sure.
[reply]

In Section Seekers of Perl Wisdom