ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

db2_fetch_assoc() fails mid-way through looping through results

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • db2_fetch_assoc() fails mid-way through looping through results

    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:

    Code:
    $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:

    Code:
    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?

    V7R2
    PHP v5.6.5
    Zend Server v8.0.2

  • #2
    Re: db2_fetch_assoc() fails mid-way through looping through results

    Look in your PHP error log, it should show you why the request is terminating.

    Chris...

    Comment


    • #3
      Re: db2_fetch_assoc() fails mid-way through looping through results

      Originally posted by dlanz12345
      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:

      {some code}

      $numRows ends up only being a fraction of the expected result set...
      Impossible to make good guesses without seeing the query, and without seeing general statistics of the table, and without knowing what is meant by "execute from iNavigator", and without knowing the connection attributes used in iNavigator. Way too much is missing.

      For example, the table might have 20000 rows, and the query is SELECT col1, col2 from MYTABLE. It takes "less than a second" for iNav to return results in the SQL window. However, it's almost certain that it would take significantly more than "a second" to scroll through the entire 20000-row result set. Returning the first block(s) of rows might be very fast, but processing the entire result set causes more internal code to access more rows and take more time. Optimization attributes can give slightly misleading results.

      Details can be important when trying to compare two very different processing methods.
      Tom

      There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

      Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

      Comment


      • #4
        Re: db2_fetch_assoc() fails mid-way through looping through results

        It turns out there was a sub-select which return multiple rows on very few records. I finally realized when I tried to scroll down in iNavigator to view more results that it would error out once I got to an afflicted query. So that is why everything seemed fine in iNavigator but due to the loop in PHP it would always cause an error (techinically a warning, so it wouldn't halt execution which made it harder to even realize there was a problem).

        Comment

        Working...
        X