I have a problem with fetchall_arrayref that is driving me batty. When I am running a query to gather data from a table, the script is freezing right at the _arrayref({}) call in weird ways. This happens with no consistency in regard to the number of values that exist in the data set coming back - it works fine up 400 and more than 2000 in one script, but then in another the script will hang with 70-80 returned items. Nothing happens after the arrayref call - I can put a print statement with no result. Any help? We have had the number of places that this is happening increasing over the past few months, and it is concerning. Code sample below.

my $sql = 'SELECT DP1,DP2 FROM Table1';
    my $sth = $dbh->prepare($sql);
    my $rc = $sth->execute || &pl_fatalSQLError($sql,$DBI::errstr);
    my $resultsArrayRef = $sth->fetchall_arrayref({});# <-- nothing ha
+ppens past here
    $sth->finish();

my $data;
foreach my $hashRef (@$resultsArrayRef) {
        $data .= "$$hashRef{DP1}' = $$hashRef{DP2};\n";
    }

print $data;

[download]

Update: Forgive me, a sick child drew me away from the computer. When running a trace, I see the statement come up as normal; I don't think that it is freezing but will also be posting on a SQL forum to validate that as well.

WRT to version and other data -

In this case, the table is really simple (below), two columns that have no constraints. That being said, I have super complex queries that have multiple joins, unique constraints, etc that also hang on occasion. Sometimes it is a single day's worth of data, and sometimes it seems that adding a WHERE parameter will cause is to hang - e.g. without one of the WHERE clauses, it runs and returns 800 rows, but when the where clause is added that restricts it to 50 results, it hangs (nothing happens after the fetchall call). 

CREATE TABLE dbo.Table1
(
    DP1  varchar(32) NULL,
    DP2  varchar(32) NULL
)

[download]

Further Update: Almost certain that this is not in the database - I don't see a long running query; the query is not showing running immediately after execution - I think it has been handed back to DBD::ODBC and DBI.

In reply to fetchall_arrayref({}) call freezing by banesong

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.