db2_fetch_assoc() fails midway through iterating o

Question

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

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 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

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?

Answer 1

@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.

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 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
);

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.

Answer 2

Is it possible that the data has errors? One possibility is decimal data errors.