db2_fetch_assoc()在遍历结果集的过程中失败 [英] db2_fetch_assoc() fails midway through iterating over result set

问题描述

我正在IBM i(AS/400)V7R2，PHP v5.6.5，Zend Server v8.0.2.上运行.

I'm running on IBM i (an AS/400) V7R2, PHP v5.6.5, Zend Server v8.0.2.

我有一个查询，只需不到一秒钟即可从iNavigator执行.当我从PHP脚本运行相同的查询，然后使用以下命令遍历它:

I have a query which takes less than a second to execute from iNavigator. When I run the same query from a PHP script and then loop through it using:

$numRows = 0;
while ($row = db2_fetch_assoc($stmt))
{
//Do stuff
$numRows++;
}
echo $numRows++;

$numRows最终只是预期结果集的一小部分，我在Zend日志中得到此错误:

$numRows ends up only being a fraction of the expected result set and I get this error in the Zend logs:

PHP Warning:  db2_fetch_assoc(): Fetch Failure in /path/to/script.php on line XXX

请注意，每次运行$ numRows的值都会有所不同.这几乎就像是超时并结束，然后才可以遍历所有结果集，但是页面加载仅需几秒钟.除了结果集中缺少的结果之外，所有内容似乎都可以正常运行并在页面上完美加载.

Note that the value of $numRows varies every time I run it. It is almost like it is timing out and ending before it can iterate through all of the result sets, but the page loads in seconds. Outside of results missing from the result set everything seems to function and load perfectly fine on the page.

有人知道这可能是造成这种现象的原因吗?

Does anyone know what might be contributing to this behavior?

推荐答案

@Buck Calabro让我步入正轨.问题不是十进制数据错误，而是视图定义中的一个子查询，该查询返回多于一行.因此，这是选择多个行的结果"错误.

@Buck Calabro got me on the right track. The issue wasn't decimal data errors but rather a sub-query in the view definition which was returning more than 1 row. So it was a "Result of select more than one row" error.

如果我在iNavigator或PHP中做了一个简单的SELECT * FROM VIEW1，一切似乎都会正常进行.直到我要么在STRSQL中运行了所提到的查询，要么手动运行了视图定义，就好像该错误不是iNavigator中视图的一部分一样.

If I did a simple SELECT * FROM VIEW1 in iNavigator or PHP everything seemed to come up fine. It wasn't until I either ran the mentioned query in STRSQL or ran the view definition manually as if it weren't part of a view in iNavigator that the error would be reported.

基本上，这是在帮助未来的用户.

To help future users here is basically what was happening.

TABLEA包含具有10行的单列. 我写这样的视图:

TABLEA contains a single column with 10 rows. I write a view such as this:

CREATE VIEW VIEWA (COL1, COL2, COL3)
AS SELECT 1, 2, (
    SELECT * FROM TABLEA
);

子选择将返回10行，并且数据库引擎不知道如何处理它.相反，如果您添加FETCH FIRST 1 ROW ONLY作为子查询的一部分，则该错误已修复.但这并不是说从逻辑上讲您会得到正确的结果，因为您可能需要第二行而不是第一行.其次，还建议您指定一个ORDER BY和/或WHERE子句，以确保返回的第一行(也是唯一行)将是您想要的.

The sub-select is returning 10 rows and the DB engine doesn't know how to handle it. If instead you add FETCH FIRST 1 ROW ONLY as part of the sub-query the error is fixed. That isn't to say logically you will get the correct results though, since you may need the 2nd row not the first. Second it would also be suggested you specify an ORDER BY and/or WHERE clause to ensure the first (and only) row returned would be what you want.

这篇关于db2_fetch_assoc()在遍历结果集的过程中失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！