FAQ
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

Search Discussions

  • Christian Merz at Apr 29, 2005 at 11:56 am
    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@gmail.com>
    To: <dbi-users@perl.org>
    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
  • Christian Merz at May 4, 2005 at 6:03 am
    Hello April,
    I'm not a SQL Server user (I use Oracle). So I foreward this to the list.
    I suppose there should be no drawbacks in using placeholders but you risk SQL
    injection if you don't use them.
    ---------------------------------------------------------
    Landeshauptstadt München
    Direktorium - AFID 3.3 - Oracle DBA
    C.A. Merz

    ----- Original Message -----
    From: April Blumenstiel
    To: Christian Merz
    Sent: Monday, May 02, 2005 5:10 PM
    Subject: Re: fetchrow_hashref stops returning data


    Thank you.

    This brings up something I'm unclear on when it comes to using the ?
    placeholders. Even if I've run $sth->finish() on the statement handle, will SQL
    Server keep the statement/optimization plan in cache for reuse? I assumed that
    it didn't, and that's why I didn't use these.

    --April


    On 4/29/05, Christian Merz wrote:
    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@gmail.com>
    To: <dbi-users@perl.org>
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedApr 28, '05 at 3:52p
activeMay 4, '05 at 6:03a
posts3
users2
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase