How to catch an error generated by db2_fetch_assoc() if the query is successful?

Question

I'm using DB2 for IBM i (an iSeries (AS/400)) using PHP 5.6.5, ibm_db2 driver on Zend Server 8.0.2.

I've run into an instance where a view which contains sub-selects which are aliased to a name may or may not return multiple rows. I did not create the view, I understand the error and can correct it. The odd part is that in PHP an error isn't thrown on the db2_exec() instead it is thrown on the db2_fetch_assoc(). I started looking into it and I can't find a way to gracefully detect an error generated by db2_fetch_assoc(). It is logged in the Zend error logs as "db2_fetch_assoc(): Fetch Failure", E_WARNING status.

db2_stmt_error() and db2_stmt_errormsg() only return errors relating to db2_exec(), db2_execute(), and db2_prepare(). I even attempted to use something like:

try{
    //fetch record
}catch(Exception $exc){
    print_r($exc);
} 

and it doesn't register as an exception.

The best method I could come up with was to issue a preliminary query to get the number of rows which should be returned. Then use a for loop like this:

for($rows = 0; $rows < $ttlRows; $rows++){
        if($row = db2_fetch_assoc($stmt, $rows)){
           //Do some stuff 
        }else{
            //Still never get to see information about the error or the afflicted row.
        }
    }

Does anyone know of any other method to gracefully detect, track, or handle such instances?

EDIT1: I manage to find something worth mentioning here: PHP: How to manage errors gracefully? This answer describes how you can force an exception when a warning occurs (which is what I'm getting from the db2_fetch_assoc()). The downside is that any warning would result in a halt of page execution if not caught.

The thought I'm entertaining is wrapping my entire page(s) in a try-catch clause in which the catch does nothing (say include try{ in a header include and }catch(Exception $exc){} in a footer include). This would allow the page to execute and since nested try-catch clauses are allowed I can still use try-catch as if I normally would. It seems dirty though.

EDIT2: Just to clarify, I am not trying to figure out what error I am getting or how to correct it - I already know what it is and how to correct it. But for the record the error I am getting is SQL0811 which is defined here. What I'm trying to figure out is how to detect and gracefully handle an E_WARNING which is generated by a db2_fetch_assoc(). The reasoning being is because if I do not detect it then the fetching process will preemptively end on the first afflicted record but the page will render as if everything is fine. So for the future I would like to have a safeguard in place which will detect these situations rather than realizing months down the line something wasn't working correctly. Unfortunately the minor solution I posted in my first edit is not a viable solution since all it really reports is that there was a fetch failure - nothing in regards to what that failure was due to. It's something, but not much. Ideally I would imagine there would be a way to get the SQL State codes or something (for my error it would be any of these: SQL0811, -811, 21000 which are defined in the link above).

Furthermore, I can't figure out why a SQL error is only considered as an E_WARNING in php. Seems kind of odd to me, but I get the same behavior in iNavigator (ie. it dies only when you get to an afflicted row) but not green screen (which throws an error on the query execution instead). So it must have something to do with the DB drivers and how they handle this.

Answer 1

Have you tried using db2_stmt_errors if db2_prepare fails ? Then you can write it out to a log

class ISeriesDB2 {

   protected $errorLog = '/tmp/error.log';
   protected $Conn;
   protected err;

   function __construct()
   {

       $this->Conn = $db2_connect(YOUR_HOST,YOUR_USER_YOUR_PASSWD);

   } 


  function query($SQL)

  {



  $this->sql = $SQL;
 
   
  
  $stmt = db2_prepare($this->Conn, $this->sql);



        if ($stmt)
  {

   
   $result = db2_execute($stmt);
   

   while ($row = db2_fetch_assoc($stmt))
     {
    
   
    $resultSet[] = $row;
     }


   db2_free_stmt($stmt); 
   return $resultSet;
  }
  else
  {
   
   $this->err = db2_stmt_errormsg();


   $this->writeError();

   return null;
  }
           
  
 



   }

   function writeError()
    {
       // write to the log

    }