[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

PDO

Hi,

I have this code. I successfully retrieve the data from the select statements. There are basically two (line 43 and 71) calls to stored procedure which are simply select statements with joins. However, this is the PDO Exception:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs2\test_pdo3.php:73 Stack trace: #0 C:\Program Files\Apache Software Foundation\Apache2.2\htdocs2\test_pdo3.php(73): PDOStatement->execute() #1 C:\Program Files\Zend\ZendStudio-5.5.0\bin\php5\dummy.php(1): include('C:\Program File...') #2 {main} thrown in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs2\test_pdo3.php on line 73

I know that if I set the the error mode to SILENT then I get a zero (0).

The solutions I've tried thus far:

1) http://bugs.php.net/bug.php?id=38243 
Tried $db->closeCursor();
after the execute() as suggested in the link.

2) Am already using fetchAll().

3) Tried the buffered query statement as seen in the code.

This is what the respondent stated:
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php

Selects in pdo_mysql are normally buffered which means that
before running a 2nd query you must either close the cursor or
fetch all records. Alternatively you can use buffered queries,
this is all expected behavior.

Any help is appreciated. Thank you,
Vkimura

<?php
 
class DBWriter extends PDO
{
	private $username;
	private $hostname;
	private $password;
	private $db;
 
	public function __construct()
	{
		$username = 'root';
		$hostname = 'localhost';
		$password = 'htmys2007';
		$db = 'howtopr1_test';
 
	    try
	    {
	    	parent::__construct("mysql:host=$hostname;dbname=$db",$username,
$password, array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
 
	    	$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	    }
	    catch (PDOException $e) {
  			throw new Exception("Failed to connect: " . $e->getMessage());
 
			/*** show the error info ***/
			foreach($dbh->errorInfo() as $error) {
		    	echo $error.'<br />';
		    }
		}
	}
}
 
$db = new DBWriter();
//$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, 1);
$in_timestamp = '1233244800';
$in_classRoom_id = '1';
 
/***
 *  first query
 ***/
$sql = "CALL selectHour2($in_timestamp, $in_classRoom_id)";
$stmt = $db->prepare($sql);
$stmt->execute();
//$stmt->fetchAll();
/*$rows = $stmt>fetchAll(PDO::FETCH_NUM);
	if ($rows) {
		print_r($rows);
	}*/
 
/*** fetch the results ***/
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$rowCount = $stmt->rowCount();
 
/*** loop of the results ***/
foreach($result as $row) {
	echo $row['splitYes'].'<br />';
	echo $row['FIRSTNAME'].'<br />';
	echo $row['LASTNAME'].'<br />';
	echo $row['firstName'].'<br />';
	echo $row['timeStamp'].'<br />';
	echo $row['classSize'].'<br />';
	echo $row['classType'].'<br />';
}
echo $rowCount.'<br />';
 
/***
 * second query
 ***/
$sql2 = "CALL selectCustDbFirstNames($in_timestamp, $in_classRoom_id)";
$stmt2 = $db->prepare($sql2);
$stmt2->execute();
 
/*** fetch the results ***/
$result2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
$rowCount2 = $stmt2->rowCount();
 
/*** loop of the results ***/
foreach($result2 as $row2) {
	echo $row2['first_name'].'<br />';
	echo $row2['last_name'].'<br />';
}
echo $rowCount2;
/*** close the database connection ***/
//$db = null;
 
/**************************
 * Optional feature of nextRowset() not functioning
 **************************/
/*$stmt2 = $db->query($sql);
 
do {
   $rows2 = $stmt2->fetchAll(PDO::FETCH_NUM);
   if ($rows2) {
       print_r($rows2);
   }
} while ($stmt2->nextRowset());*/
 
?>

Open in new window

0
Victor Kimura
Asked:
Victor Kimura
  • 2
  • 2
1 Solution
 
psadacCommented:
the method closeCursor() should be applied to the statement (not the database)
At line 55 add :
$stmt->closeCursor();

Open in new window

0
 
Victor KimuraAuthor Commented:
Hello psadac,

Doesn't work still. Any suggestions?
0
 
psadacCommented:
no idea. could you give your versions of php and mysql ? if you don't extend PDO class but use it directly does it work better ?

additionally, but i don't think your problem is related to that , you should replace :

                $username = 'root';

by :

                $this->username = 'root';

0
 
Victor KimuraAuthor Commented:
Hi psadec,

Apparently, this is a php/mysql bug. There is an update from mysql but only currently for php 5.3 or php 6. http://bugs.php.net/bug.php?id=39858
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now