PDO unbuffered queries

PHP
1

Hi guys.

I’m just starting out on a new project and I’m getting this 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.’”

This is my db class:
Notice the buffered query setting…

class DB extends PDO {

public function __construct(){

	parent::__construct( 'mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWORD );
	try {
		$this->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); // i've tried true/false
		$this->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
		$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$this->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
	} catch(PDOException $e) {
		die( 'DB Connection error:  ' . $e->getMessage() );
	}
}
}

I’m using new instances of the database in other classes like the one below… Probably a bad practice but shouldn’t be whats causing my problem, right?

And this is the method causing the problem (another class with $this->db as an instance of DB class)

function insert($url, $title, $content){

	$insert = $this->db->prepare('INSERT INTO tenders (url, title, content) VALUES(:url, :title, :content)');
	$data = array(
		':url' => $url, // i've tried using simple sample strings here...
		':title' => $title,
		':content' => $content,
	);
	$insert->execute($data); // this is where things break
	$insert->closeCursor(); // this should solve the "unbuffered queries"-issue but it doesn't
	unset($insert); // nor does this...
}

Environment:
vagrant/scotchbox 2.5
PHP 5.6.14-1+deb.sury.org~trusty+1
mysql Ver 14.14 Distrib 5.5.46

From what I can find closeCursor method or unsetting the var should have solved it but I can’t get it to work.
Any ideas? Getting desperate here and loosing sleep… :disappointed_relieved:

2

Well, first of all some clarifications:

  • The error message, although trying to be helpful, is rather misleading. As this error message can appear without any buffering issues at all.
  • Calling closeCursor() by no means a solution, but rather a crutch. There are some results pending, but you are trying to just cut them off. Either fetch all the pending results if you need them, or make your code return no any extra results if you don’t.

Next,

If you’re indeed using several instances of DB class during script execution, it’s, although irrelevant to your problem, is a BIG no-no. A connection to the same database should be only one, no exceptions.

Now to your problem.
The code you posted is not causing this error but rather suffering from it. While it was caused by some other query (that actually returns some results). You have to find a query that has been called before this one and see what’s wrong with it. The most probable case for this error is a stored procedure call, which you have to be solved with nextRowset() call.

If it’s not the issue, post the relevant code and I’ll see what it could be.

3

Thank you!

You were absolutely right. I was using a PDO::exec SELECT id query to check if a post existed thinking it would return number of affected rows. Should have read the manual more carefully.

And from now on, one connection only. :blush:

4

Please note that you should not use exec() to check if a post existed! You should prepare()/execute() instead, using ? in the query, instead of a variable, while variable itself have to be moved into execute().

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.