[RESOLVED] PDO : "Cannot execute queries while other unbuffered queries are active"

[steamPunk]

Hi

I'm getting an error message :

PDO::errorInfo()Array ( [0] => HY000 [1] => 2014 [2] => 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. )

after scouting about on the interweb, here's one thing I tried with my connection :

try {
    $conn = new PDO("mysql:host=$hostname;dbname=esma", $username, $password, array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));

	// force connection encoding to uft8
	$conn->exec('SET CHARACTER SET utf8');

/*** echo a message saying we have connected ***/
op('Connected to database');

}
catch(PDOException $e)
    {
    op($e->getMessage());
    }

I also tried

try {
    $conn = new PDO("mysql:host=$hostname;dbname=esma", $username, $password);

	$conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, 1);

but that gives an additional warning :

Warning: PDO::setAttribute() expects parameter 1 to be long, string given in /home/

the php version is 5.0.44

has anyone got any idea what i can do ?

[steamPunk]

when i do

    $conn = new PDO("mysql:host=$hostname;dbname=esma", $username, $password);

	//$conn->setAttribute(1,PDO::MYSQL_ATTR_USE_BUFFERED_QUERY);

	PDO::setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

as suggested here : http://php.morva.net/manual/en/ref.pdo-mysql.php

i get the message :

Fatal error: Non-static method PDO::setAttribute() cannot be called statically in /home/e

anyone ?

this is hugely doing my head in, lol !

[steamPunk]

but I've narrowed down the field .....

it seems that the order of these two bits of code is important :

// DISPLAY ON LINE OR NOT
if(isset($_GET['online'])&&($_SESSION['logged'] == "ok")){	
	$sql = "UPDATE pages SET page_online='".$_GET['online']."' WHERE page_id='".$_GET['modifID']."' LIMIT 1";
	updateQuery($conn,$sql);
}


// GET CATEGORY INFO
if(isset($_GET['cat_id'])){
	$_SESSION['cat_id']= $_GET['cat_id'];
	$sql = "SELECT cat_title FROM cats WHERE cat_id='".$_SESSION['cat_id']."'";
	$res = selectQuerySingleRow($conn,$sql);
	$_SESSION['cat_title'] = $res['cat_title'];
}else{
	if(!isset($_SESSION['cat_id'])){
		jsRefresh("admin_main.php");
	}
}

in the above order it works because the UPDATE query is done and then the SELECT query

but if i inverse the order it doesn't work because PDO seems to hog the cursor after the SELECT and it isn't freed for the UPDATE query.

However i can't figure out how to reset the cursor after the SELECT to make it available for for the UPDATE query

does anyone know ??

thanks

[steamPunk]

that was the answer - i've never used them before but it seems to have fixed my problem because you can reset the cursor

so, for anyone who has the same problem ...

you need to put some statement stuff into your query code :

function selectQuery($conn,$sql){

op($sql);
$out = array();
$sth = $conn -> prepare($sql);
$sth->execute();
$res = $sth->fetchAll();
$sth->closeCursor();

if (!$res) {
	op("\nPDO::errorInfo()");
	print_r($conn->errorInfo());
}else{
	$i = 1;						
	foreach ($res as $row) {
		$out[$i] = array();
		foreach($row as $k => $v){												
			$out[$i][$k] = $v;
		}	
		$i++;
	}		
}	
return $out;	
}

hope this is of use to someone and can save them some pain !

I think that maybe the fetchAll does the same thing as my nested foreach loops - have to check that out