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
