Re: fetchrow_hashref stops returning data

Hello April,

my first impressions are:

1) use the ? placeholder (for security! and performance reasons):
--- source :
WHERE PartnerCode = $partnerCode
....
$sh->execute || throw util::backEndException -value => {code =>

--- change to :
WHERE PartnerCode = ?
....
$sh->execute($partnerCode) || throw util::backEndException -value => {code =>

2) perldoc DBI says:
If there are no more rows or if an error occurs, then
"fetchrow_hashref" returns an "undef". You should check
"$sth->err" afterwards (or use the "RaiseError"
attribute) to discover if the "undef" returned was due
to an error.

So check for errors after the fetchrow_hashref:

my $partnerInfo = $sh->fetchrow_hashref('NAME_lc');
throw util::backEndException -value => { ... } if $dbh->err;

3) from perldoc DBI:
$rc = $sth->finish;
When all the data has been fetched from a "SELECT"
statement, the driver should automatically call "finish"
for you. So you should not normally need to call it
explicitly except when you know that you've not fetched
all the data from a statement handle. The most common
example is when you only want to fetch one row, but in
that case the "selectrow_*" methods are usually better
anyway.

Better use selectrow_* or check the $rc.

HTH,
---------------------------------------------------------
Landeshauptstadt München
Direktorium - AFID 3.3 - Oracle DBA
C.A. Merz


----- Original Message -----
From: "April Blumenstiel" <aprilmb@xxxxxxxxx>
To: <dbi-users@xxxxxxxx>
Sent: Thursday, April 28, 2005 5:52 PM
Subject: fetchrow_hashref stops returning data


Hello,

I have a program in perl running on Linux, accessing a SQL Server database
via ODBC. It runs fine. But yesterday, the process had been running for six
days when suddenly, one specific select statement/fetchrow_hashref stopped
returning any data from the database. There were other very similar lines of
code that were still returning data. Just this one particular statement was
not. A manual restart of the perl process fixed the problem. And everything
is still working fine since then.

Reliability is important here, so I need to address this, but it's tough
because I don't know how to replicate it.

Any ideas/advice?

Here is the code that was failing (throws an exception):
-------------------------------------------------------------------
# Get partner info.
my $sql = "
SELECT *
FROM Partners
WHERE PartnerCode = $partnerCode
";
my $sh = $dbh->prepare($sql) || throw util::backEndException -value => {code
=> code('DATABASE_ERROR'), message => $dbh->errstr};
$sh->execute || throw util::backEndException -value => {code =>
code('DATABASE_ERROR'), message => $sh->errstr};
my $partnerInfo = $sh->fetchrow_hashref('NAME_lc');
$sh->finish;
if (!$partnerInfo) {
throw util::backEndException -value => {code => code('DATABASE_ERROR'),
message => "Unexpected error looking up partner for $item"};
}
-------------------------------------------------------

It's the check for $partnerInfo that fails. And prior to this code,
"$partnerCode" is checked, so the SQL code should be correct.

Thank you for any ideas,
April


.