When in doubt, check for errors. You're called fetchrow_array and not getting what you expect, so I would follow the documentation (DBI: You should check $sth->err afterwards (or use the RaiseError attribute) to discover if the empty list returned was due to an error.).

I'm not embarrassed to admit that I'm a fan of the Perl debugger .. much is revealed when running a script through the debugger.

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]

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]

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.

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]

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]

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

I would like to comment on the SQL here. As has been suggested already and you replied, you should use placeholders to avoid SQL Injection, and to allows the database to reuse the same statement.

It looks as if you are looking for year and month, if so, unless you are required to pass those dates, you should create them on the fly, especially because of leap years adding an extra day to February.

Anyway, the issue here is likely that the values are not being changed into DATEs. Let me explain, because a lot of people misunderstand how dates work in databases. A database stores dates in an internal format which is not available to the user. Instead, every time a date is input or output, it is formatted from or to text (or any other type you specify). That is, you input text and ask the rdbms to turn it into a database-date for you via a function, and when you output a date, you ask the rdbms to turn it into text for you. Both of these can always be done explicitly, though at times it is done implicitly. There are usually default date formats and rdbms-specific functions to do these actions for you. OTOH, you can usually use the ANSI date, by predicating the date string with DATE, as long as you only specify the year, month, and date. Also, while the the date can be turned into a string for comparison, this is redundant, and can lead to calculation errors, so it is best to leave it as a date.

As i would rewrite the statement as follows, using something like TIMESTAMP:

Hi, This is really useful information and from the starting i knew there is something wrong with parsing dates. I have read the doc, thanks for that. But now it is giving some ambiguity error and i do not know why it is coming.

DBD::DB2::db prepare failed: [IBM][CLI Driver][DB2/AIX64] SQL0245N  Th
+e invocation of routine "TIMESTAMP" is ambiguous. The argument in pos
+ition "1" does not have a best fit.  SQLSTATE=428F5
DBD::DB2::st execute failed: [IBM][CLI Driver][DB2/AIX64] SQL0245N  Th
+e invocation of routine "TIMESTAMP" is ambiguous. The argument in pos
+ition "1" does not have a best fit.  SQLSTATE=428F5
[IBM][CLI Driver][DB2/AIX64] SQL0245N  The invocation of routine "TIME
+STAMP" is ambiguous. The argument in position "1" does not have a bes
+t fit.  SQLSTATE=428F5
[download]

Seems like TIMESTAMP can take a 2nd argument for microsecond precision so try

SELECT DISTINCT Fid_Cust FROM Session WHERE Dat_End BETWEEN TIMESTAMP(?,0) AND TIMESTAMP(?,0)

You might need to change your values to match those expected by TIMESTAMP.

Try explicitly converting the character string to a date with the to_date(...)

Good idea. TO_DATE() is a synonym for the TIMESTAMP_FORMAT. Being clearer, i would use the latter.

Judging from the documentation, he could just go all the way with TIMESTAMP_FORMAT(?, 'YYYY-MM-DD HH24:MI:SS').

Hello dipit,

Welcome to the Monastery. It is really difficult for us to replicate your problem without having input data (sample of table).

Having said that, I have found MySQL select statements in the past failing because of not using back ticks (``). So try something like this:

Give it a try and let us know if it worked.

Update: I have found really useful the phpMyAdmin it helps me to check my requests before applying them.

Update2: In case the SELECT statement is correct run something like this as fellow monk talexb proposed:

eval {      ### Catch _any_ kind of failures from the code within

    ### Enable auto-error checking on the database handle
    $dbh->{RaiseError} = 1;

    ### Prepare a SQL statement for execution
    my $sth = $dbh->prepare( "select distinct `FID_CUST` from `session
+` where `DAT_END` between `$key` and `$hash{$key}`" );

    while (1) {
    ### Execute the statement in the database
    $sth->execute();

    ### Retrieve the returned rows of data
    while ( my @row = $sth->fetchrow_array() ) {
        print "Row: @row\n";
    }
    }
};
warn "Monitoring aborted by error: $@\n" if $@;
[download]

Looking forward to your update, BR.

Hi I tried using back quotes and it did not work. I got this : DBD::DB2::db prepare failed: IBMCLI DriverDB2/AIX64 SQL0007N The statement was not processed because a character that is not supported in SQL statements was included in the SQL statement. Invalid character: "`". Text preceding the invalid character: "select distinct ". SQLSTATE=42601

Hello again dipit,

You are right bakcticks are not working on SQL. Remove them from the SELECT statement and execute again. See the update2 with the eval.

Hope this helps, BR.

Seeking for Perl wisdom...on the process of learning...not there...yet!

Are you sure that you understood what was suggested? Reply to the various helpful posts where you attempted to follow their suggestions and in your responses include the code after you modified it along with the output, warnings and errors.

Yes, tried all the solutions but unfortunately not worked.