Uncaught Exception Pdoexception error with message 'SQLSTATE[HY000]: General error: 2014

Question

I have a code that works perfectly on the local wampserver server, it is a query interspersing the result the problem is that when finishing the site and hosting it I get an error.

The code is this:

$conn->exec('SET @orderA := 0; SET @orderB := 0;');
$q = $conn->prepare("
SELECT id, voz
FROM (
    SELECT id, voz
    ,IF(voz = 'voz-feminina', @orderA := @orderA + 1, IF(voz = 'voz-masculina', @orderB := @orderB + 1, null)) AS idx
    FROM musica
    WHERE audio = 'pop'
) AS a
ORDER BY idx, voz;
");
$q->execute();
while ($linha = $q->fetch(PDO::FETCH_ASSOC)) {
    echo $linha['voz'];
}

And that’s the mistake:

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 D: web localuser www new index.php:15 Stack trace: #0 D: web localuser www new index.php(15): Pdostatement->execute() #1 {main} thrown in D: web localuser www new index.php on line 15

What can it be?

Answer 1

Good person. Thank you all for your efforts. When I was developing this code in php on a local server I used the version of php 5.5.12 and it worked perfectly, my problems started when I uploaded the code to a hosting server with the version of php 5.2.xx then presented this problem. when I realized the difference of versions I asked for a migration to a server with version php 5.5.12 or higher, and fixed problem. I don’t know if this was really a solution or a gambiarra. but now it’s all running perfectly. Thanks to all the stackoverflow programmers, you’re 10.

Answer 2

Try it like this:

$conn->exec('SET @orderA := 0; SET @orderB := 0;');

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

$q = $conn->prepare("
SELECT id, voz
FROM (
    SELECT id, voz
    ,IF(voz = 'voz-feminina', @orderA := @orderA + 1, IF(voz = 'voz-masculina', @orderB := @orderB + 1, null)) AS idx
    FROM musica
    WHERE audio = 'pop'
) AS a
ORDER BY idx, voz;
");
$q->execute();

$resultados = new ArrayIterator($q->fetchAll(PDO::FETCH_OBJ));

foreach($resultados as $res) {
    echo $res->voz;
}

Answer 3

If you have an appointment before the exec(), close it before starting the next with closeCursor(), this is recommended in the case of multiple queries and in the use of fetch().

$stmt->closeCursor(); //fecha a consulta anterior
$conn->exec('SET @orderA := 0; SET @orderB := 0;');