We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

PDO

Victor Kimura
on
Medium Priority
629 Views
Last Modified: 2013-12-12
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

Comment
Watch Question

Commented:
the method closeCursor() should be applied to the statement (not the database)
At line 55 add :
$stmt->closeCursor();

Open in new window

SEO, Web Developer
Commented:
Hello psadac,

Doesn't work still. Any suggestions?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
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';

Victor KimuraSEO, Web Developer

Author

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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.