fetchall_arrayref({}) call freezing

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

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 -

MS SQL 2005
ODBC connection
DBI 1.607
DBD::ODBC 1.16

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.

Comment on fetchall_arrayref({}) call freezing Select or Download Code

Replies are listed 'Best First'.
Re: fetchall_arrayref({}) call freezing by McA (Priest) on Mar 11, 2014 at 19:11 UTC
Hi, we can't see whether this is a perl related problem or a database problem. To give some advice or to collect some experience here with several databases you should add some information: Database version Database client version DBI version DBD::xxx version create statement of Table1 does it happen always with the same table or with different sql statements? Regards McA	[reply]
Re^2: fetchall_arrayref({}) call freezing by Laurent_R (Canon) on Mar 11, 2014 at 22:20 UTC
I agree, it looks as a database problem. The time to execute a DB query is really not proportional to the number of matching results, but on the complexity of what it has to do to fetch these results. You are not saying enough to elicit a really useful answer.	[reply]
Re^3: fetchall_arrayref({}) call freezing by banesong (Acolyte) on Mar 12, 2014 at 12:52 UTC
The thing that is killing me on this particular query is it really as simple as fetching rows from the table - no joins, no where statement, nothing! This is as simple a query as I can do. When executing the query in SSMS, it takes 00:00:00 to run, and returns 369 rows.	[reply]
Re: fetchall_arrayref({}) call freezing by GotToBTru (Prior) on Mar 11, 2014 at 18:01 UTC
Have you had your DBA check the logs to see what the database is seeing in these instances?	[reply]
Re: fetchall_arrayref({}) call freezing by mje (Curate) on Mar 12, 2014 at 13:17 UTC
DBI 1.607 is from July 2008 and DBD::ODBC 1.16 is from May 2008 almost 6 years old now. I was going to suggest you supplied a DBD::ODBC trace but as your version is so old it is almost before I took over its maintenance and the tracing is rather poor. I know people always say this but can't you upgrade to a newer DBI and DBD::ODBC? As a last resort, you can try enabling tracing in the ODBC Administrator and then when your code hangs, examine it to show me what were the last calls.	[reply]
Re^2: fetchall_arrayref({}) call freezing by banesong (Acolyte) on Mar 12, 2014 at 14:07 UTC
I am currently locked in to the module due to versioning restrictions, but hope to be able to update things soon. I will turn on tracing and see what I can return to you.	[reply]
Re^3: fetchall_arrayref({}) call freezing by banesong (Acolyte) on Mar 13, 2014 at 10:55 UTC
Okay.... No matter what I do, I get nothing in the ODBC trace file, even though I know for a fact that I am shoving data across the pipe. Even a DBA that I know is confused about that fact.	[reply]
Re^4: fetchall_arrayref({}) call freezing by mje (Curate) on Mar 13, 2014 at 12:01 UTC
Re^5: fetchall_arrayref({}) call freezing by banesong (Acolyte) on Mar 24, 2014 at 18:53 UTC
Some notes below your chosen depth have not been shown here
Re: fetchall_arrayref({}) call freezing by banesong (Acolyte) on Mar 13, 2014 at 10:53 UTC
Okay, it gets more interesting. Changing the query from: `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();` [download] To: `my $sql = 'SELECT * FROM Table1'; my $sth = $dbh->prepare($sql); my $rc = $sth->execute \|\| &pl_fatalSQLError($sql,$DBI::errstr); my $resultsArrayRef = $sth->fetchall_arrayref({}); $sth->finish();` [download] allows the script to function. Can anyone explain why? I am totally flummoxed. I am running into similar behavior elsewhere (currently troubleshooting to see if it is the same problem), but need to get to the bottom of this while I still have hair to pull out!	[reply] [d/l] [select]
Re^2: fetchall_arrayref({}) call freezing by McA (Priest) on Mar 14, 2014 at 12:18 UTC
Hi, in this case I have two guesses: Columns DP1 and DP2 don't exist, so that `select ` is fetching something different. You have a weird case sensitivity problem, where one part of the stack seems to be not case sensitive and the other part is case sensitive. But, by the way: Whe you work with DBI and want to do the error handling on your own, than you have to check every* DBI method, e.g. `my $sth = $dbh->prepare($sql);` should also be checked. On the other handside you can enable exceptions with `$dbh->{RaiseError} = 1;` which is IMHO much easier and an exception is thrown as soon as something went wrong. Especially in debugging cases I would prefer to set RaiseError. McA	[reply] [d/l] [select]
Re^3: fetchall_arrayref({}) call freezing by banesong (Acolyte) on Mar 24, 2014 at 18:55 UTC
I know that DP1 and DP2 exist, and I have the column name correctly capitolized. I will enable raise error and see what happens, thanks.	[reply]