Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Mmonks!

I have this section code in my program that looks for a particular record in a SQL Server database. My problem is that, why when I am looking for a record that isn't in the database the code doesn't even get to the "while" loop in my code, and I need to print a message saying that the particular record has not been found. Any reason why the code is not going into the while loop? By the way, it works fine is there is a record in the db, it then goes into the while loop like I expect. 

$dbh = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 1});
my $sql_s="SELECT  *  FROM main WHERE record_number='$db_number'";
$sth = $dbh->prepare($sql_s);
$sth->execute() || die $sth->errstr;

while ($pointer = $sth->fetchrow_hashref)
{

$record_id = $pointer->{'record_id'};$record_id=~s/\s//g;
if($record_id){
print "<br><font color=red>Record Found: $record_id
</font><br>";
}else{print "<br><font color=red>No Record Found
</font><br>";}
     
}

[download]


Replies are listed 'Best First'.
Re: Perl and SQL issue.
by derby (Abbot) on Jun 28, 2007 at 12:43 UTC

    The reason the code doesn't go into the while loop is because the result set is empty! Some drivers may give you some meta data on the number of records returned (or you could use fetchall_hashref or fetchall_arrayref) or you could just use a sentinel: 

    $sth->execute() || die $sth->errstr;
my $flag = 0;
while ($pointer = $sth->fetchrow_hashref) 
{
  $flag++;
  ...
}

if( ! $flag ) 
{
   print "No records for ...."
}

    [download]

    -derby
[reply]
[d/l]
      Thanks, you're right, better just do another check like you did for $flag.
[reply]
Re: Perl and SQL issue.
by Corion (Patriarch) on Jun 28, 2007 at 12:47 UTC

    As an aside, you should use strict - it makes it possible for Perl to tell you about typos in your code.

    A while() loop never starts if the condition is false. You most likely want to use a different construct then, if you want your code to behave differently, or, if your database driver returns a reliable rowcount from the ->execute method, use that:

    Variant 1 - database returns a reliable rowcount

    my $count = $sth->execute;
if (! $count) {
    die "Couldn't execute >>$sql<< : " . $sth->errstr;
};
if ($count == 0) {
    print "No rows were affected.";
} else {
    while (my $pointer = $sth->fetchrow_hashref) {
    ... do magic ...
    };
};

    [download]

    Variant 2 - the DB driver does not return a reliable rowcount

    my $res = $sth->execute;
if (! $res) {
    die "Couldn't execute >>$sql<< : " . $sth->errstr;
};

my $rows = 0;
while (my $pointer = $sth->fetchrow_hashref) {
    ... do magic ...
    $rows++;
};
if (! $rows) {
    print "No rows were affected.";
};

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

Back to Seekers of Perl Wisdom