ID: 39858 Comment by: james dot cordon at btinternet dot com Reported By: develar at gmail dot com Status: Assigned Bug Type: PDO related Operating System: Windows XP SP2 PHP Version: 5.2.0 Assigned To: wez New Comment:
previous doesn't address stored procedure prob (from what i've read this is a win prob only). my revised approach: Essentialy after you use a stored procedure call, burn another query to force an exception. ### private $connect_a=array(); private $connected=0; public function __construct($dsn=NULL, $user=NULL, $pass=NULL){ if(is_array($dsn)){ $this->connect_a['DSN']=$dsn[0]; $this->connect_a['U']=$dsn[1]; $this->connect_a['P']=$dsn[2]; } else { $this->connect_a['DSN']=$dsn; $this->connect_a['U']=$user; $this->connect_a['P']=$pass; } }// public function query($q){ if($this->connected==0){ $this->connect(); } return parent::query($q); }// public function dropConnection($stmt){ $stmt->closeCursor(); $this->connected=0; }## public function callStoredProcedure($stmt){ $resultset; try{ #multi array while ($row= $stmt->fetch()) { $resultset[]=$row; echo '<br />'; var_dump($row); echo '<br />'; } #burn if(stripos($_SERVER['SERVER_SOFTWARE'], 'win') ){ $stmt=$this->query("select 1+1"); } }catch(PDOException $e){ if($e->getCode()=='HY000' AND strpos ($e->getMessage(), 'Lost connection' )){ print "<-!!!- Error!: Caught 'Lost connection error, dropConnection() -!!!-><br />"; $this->dropConnection($stmt); } else { throw $e; } } return $resultset; }// public function connect(){ #if($this->connected==1) return true; try{ parent::__construct($this->connect_a['DSN'], $this->connect_a['U'], $this->connect_a['P']); } catch (Exception $e) { throw($e); } $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $this->connected=1; }// #EXAMPLE #just to prove obj is same one $pdodl_1->temp_id='AAAAAA'; $i=100; do{ echo 'LOOP NUM:'.$i.'<br />'; echo '<br />PDODL OBJ: '; var_dump($pdodl_1); echo '<br /><br />'; $stmt=$pdodl_1->query("call testMany()"); $stmt->setFetchMode(PDO::FETCH_ASSOC); $rset=$pdodl_1->callStoredProcedure($stmt); $i--; } while($i>0); This is all from another project, so may need some tinkering. Previous Comments: ------------------------------------------------------------------------ [2007-02-23 11:29:16] martin dot schmitz at uni-bielefeld dot de I've got a dirty solution for your problem (works under linux, not tested on windows) if i do: $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $dbh->setAttribute( PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true); $datasets = $dbh->query( 'SELECT * FROM table_name_1 WHERE id=5); $datasets->execute(); $result_1 = $datasets->fetch( PDO::FETCH_ASSOC); $datasets = $dbh->query( 'SELECT * FROM table_name_2 WHERE id=7); $datasets->execute(); $address = $datasets->fetch( PDO::FETCH_ASSOC); it gets the same unbuffered error. But if I do a fetch twice on the first result and store it to a dummy array, the second query works: $datasets = $dbh->query( 'SELECT * FROM table_name_1 WHERE id=5); $datasets->execute(); $result_1 = $datasets->fetch( PDO::FETCH_ASSOC); // test, remove if bug is fixed $dummy = $datasets->fetch( PDO::FETCH_ASSOC); $datasets = $dbh->query( 'SELECT * FROM table_name_2 WHERE id=7); $datasets->execute(); $address = $datasets->fetch( PDO::FETCH_ASSOC); I think the cursor-pointer does not work correctly?? You can also do a fetchAll(), but then your result looks like: array[0] => ( array=>([prename] => 'Martin' [name] => 'Schmitz')) instead of array=>([prename] => 'Martin' [name] => 'Schmitz') Hope it works for you ------------------------------------------------------------------------ [2007-02-22 12:39:23] james dot cordon at btinternet dot com AHHHHHH!!!! My bodge-it above doesn't work correctly, it just opens but doesn't close many connections. This does work (tried several times on 200 consec' queries) added closeCursor(). $i=100; while($i>0){ echo 'LOOP NUM:'.$i.'<br />'; try{ $stmt=$pdodl_1->query("call testMany()"); $stmt->setFetchMode(PDO::FETCH_ASSOC); echo '<br />PDODL OBJ: '; var_dump($pdodl_1); echo '<br />PDO::STATEMENT OBJ: <br />'; var_dump($stmt); echo '<br /><br />'; while ($row= $stmt->fetch()) { echo '<br />'; var_dump($row); echo '<br />'; } $i--; }catch(PDOException $e){ if($e->getCode()=='HY000'){ $stmt->closeCursor(); $pdodl_1->connect(); $i--; } else { throw $e; } } } ------------------------------------------------------------------------ [2007-02-22 11:37:01] james dot cordon at btinternet dot com php 5.2.1 win xp pro mysql 5x apache 2x I also built a project assuming stored procedures would work as they are mentioned in the php docs, anyway. to cut a long story short, I extended PDO with methods that included not connectimg to DB until actually needed. public function connect(){ try{ parent::__construct($this->connect_a['DSN'], $this->connect_a['U'], $this->connect_a['P']); } catch (Exception $e) { throw($e); } ### always use exception error handling $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $this->connected=1; }// When calling a 2nd query (after a stored procedure) I get the "SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query" every time. I discovered calling "$pdoextended->connect();" after each use of a stored procedure revived the connection (without instantiating another PDOextended obj) with no errors. This is ONLY a bodge-it but it is a very lightweight one. ------------------------------------------------------------------------ [2007-02-20 14:24:37] barney dot hanlon at markettiers4dc dot com That would technically be a workaround rather then a fix. The issue is still there, and switching to ODBC is not necessarily a good or acceptable solution. Also as we have taken onboard the Zend Framework prior to beginning work on Zend Platform, creating non-standard solutions doesn't sound like a forward step. However, until the PHP team pull their fingers out and assess PDO to work with Windows properly, I may have to implement it. Thank goodness that the partnership with Microsoft will force PHP to stop treating IIS as a perochial platform and get proper support. ------------------------------------------------------------------------ [2007-02-19 18:45:52] denis dot podgurskiy at cofelab dot ru Hi once again. Step by step instruction how to work with MySQL sp/transaction under Win XP SP 2 1. Install MySQL ODBC driver. 2. Create database with tables in InnoDB format (to support transaction - see my.cnf). 3. Create DSN with connection to your database (see Admin tools->ODBC). 4. Enable pdo_odbc within php.ini file. 5. Use this to create connection if(strstr($_SERVER['SERVER_SOFTWARE'],'Win')){ $this -> Db = new PDO("odbc:DSN=MY_MySQL_ODBC;", user, password); } else { $this -> Db = Zend_Db::factory( PDO_MYSQL); 6. When the sp has been executed do not forget to fetch the statement: $command -> execute(); do { $rows = $command->fetchAll(); } while ($command -> nextRowset()); That's all. This code will work under win/nix without any diffireneces - checked by me. If you still have any problems - just email me and I'll contact you by ICQ/Skype to help (I spent four weeks to solve it). Regards, Denis ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/39858 -- Edit this bug report at http://bugs.php.net/?id=39858&edit=1