1

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.

Community
  • 1
  • 1
d.lanza38
  • 2,525
  • 7
  • 30
  • 52
  • Any chance of showing the problem code? Hard to say much without at least seeing the failing code. – user2338816 Aug 08 '15 at 06:05
  • See [bug fix note #6765](http://www.phpdoc.info/commits/6765), search for "Fetch failure". Indicates only a non-success RC from SQLFetchScroll(), not necessarily a SQL error, possibly dropped connection? ...corrupted _$stmt_? ... _$stmt_ used in a different API in the loop? Hard to guess without seeing **all** code that runs after successful fetch and before failing fetch. – user2338816 Aug 08 '15 at 22:36
  • I understand the error I happen to be getting and can correct it, it is a "Result of SELECT more than one row." error. It is this error which is occurring during the fetch rather than the execution of the query. This post/answer will describe this particular error (http://stackoverflow.com/questions/31815515/db2-fetch-assoc-fails-midway-through-iterating-over-result-set) , but my question isn't specific to the error, I'm trying to figure out how can I detect and handle any errors which occur on the `db2_fetch_assoc()` call. – d.lanza38 Aug 10 '15 at 12:23
  • copy to temp table??? – danny117 Aug 11 '15 at 18:28
  • @danny117 I'm sorry, I don't know what you mean by that. But again, I'm not looking for a way to solve the error I am getting - I know how to do that. I'm looking for a way to gracefully DETECT E_WARNINGS which are triggered by a `db2_fetch_assoc()`. – d.lanza38 Aug 12 '15 at 19:45
  • I mean fetch into a temp table catch the error. No error process from temp table. – danny117 Aug 13 '15 at 13:16
  • I still may not understand exactly what you are saying. The way I'm interpreting your comment, it doesn't make sense to me. Wouldn't I have to fetch through the results just how I am right now in order to insert them into a temporary table? If while I'm fetching the first time in order to create the temporary table and the error is triggered how am I supposed to detect that? Am I supposed to implement this logic for every single query I execute? Again, I'm probably not understanding you correctly so I apologize. – d.lanza38 Aug 13 '15 at 15:43

1 Answers1

0

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

    }
Izabela Skibinska
  • 453
  • 6
  • 8
  • I appreciate the answer. The error does not occur on `db2_prepare`. It occurs on `db2_fetch_assoc()` when it attempts to retrieve an afflicted row. – d.lanza38 Oct 07 '16 at 19:37