RE: missing records while running a query using DBI

[Ian Harisay]

I don't see how you are getting your results.  Are you doing something along 
these lines?

Note:  You should always use a placeholder with your variables.  It is safer.

Use Data::Dumper;

$query = qq{
            SELECT item.*
            FROM tmp_loadplan_items item,tmp_loadplans loadplan
            WHERE loadplan.nreference_code = ?
            AND loadplan.id = item.tmp_loadplans_id
        };

my $sth = $dbh->prepare( $query ) or die "Can't prepare statement: 
$DBI::errstr";
$sth->execute($cReference) or die "Can't prepare statement: $DBI::errstr";

while(my $rec = $sth->fetchrow_hashref('NAME_lc')){
  #-- do something with each record.
  print Dumper($rec);
}

-----Original Message-----
From: Abi Alias [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 07, 2008 8:13 AM
To: dbi-users@perl.org
Cc: Mattias Hakansson; Niklas Palmqvist
Subject: missing records while running a query using DBI

Hello,
I am generating a report in Perl and data's are from oracle. Connecting via DBI 
module.

I run the following query directly in SQLPLUS and got 25 result.


    SELECT item.*   FROM tmp_loadplan_items item,tmp_loadplans loadplan
         WHERE loadplan.nreference_code = 'NYCWHAM30011258'
         AND loadplan.id = item.tmp_loadplans_id ORDER BY nposition ASC

But When I run the same  query using DBI, I am getting only 14 result.



Part of the code I am using.
=================

$query = qq{
            SELECT item.*
            FROM tmp_loadplan_items item,tmp_loadplans loadplan
            WHERE loadplan.nreference_code = '$cReference'
            AND loadplan.id = item.tmp_loadplans_id
        };

my $sth = $dbh->prepare( $query )
    or die "Can't prepare statement: $DBI::errstr";


print "\n Query is \n";
print "\n";
print $query;
print "\n";

print " Total results set: ".$sth->execute(); undef $query; print "\n"; exit;


Version of DBI : 1.53
SQL*Plus: Release 3.3.4.0.0


Is this a bug, or have I done something monstrously wrong?

Thanks and regards,

Abi Alias