Doctrine and Symfony2: How to execute raw queries (General error: 2014 Cannot execute queries while > other unbuffered queries are active)

Question

I'm trying to execute a raw query using Doctrine into the Symfony framework.

Here there is the code:

class MessagesHandler
{
    /** @var \Doctrine\Common\Persistence\ObjectManager The entity manager used to access entities */
    protected $em = null;

    public function __construct($em)
    {
        $this->em = $em;
    }

    public function getMessagesToPost($user)
    {
        $query = 'SELECT COUNT(*) FROM `messages` WHERE `posted_by` = ' . $user->getId() . ' AND `message_posted` = 0';

        return $this->em->getConnection()->exec($query);
    }

I have set the class MessagesHandler as a service:

services:
    shq.handler.Messages:
                class: AppBundle\Handler\MessagesHandler
                arguments: ["@doctrine.orm.entity_manager"]

Now, when I execute the code, I receive the following error:

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.")...

How can I use PDOStatement::fetchAll()?

Could you try to call $em->getConnection()->fetchAll() or $em->flush before you call your query ? Dump the result to see if it's important data you might be fetching and that you want to keep somewhere. — Answers_Seeker, Jun 23 '15 at 13:09

score 1 · Answer 1 · answered Jun 23 '15 at 13:16

1

Use the createNativeQuery method:

$query = $entityManager->createNativeQuery('SELECT COUNT(*) FROM `messages` WHERE `posted_by` = ? AND `message_posted` = 0', $rsm);
$query->setParameter(1, $user->getId());

$count = $query->getResult();

Documentation available here: http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html

answered Jun 23 '15 at 13:16

Pi Wi

1,076
1
11
20

It seems to me a little bit complex just to execute a count query, isn't it? Is there a more simple way to achieve the same result? I want only a simple count. – Aerendir Jun 23 '15 at 14:36
1

If you do `$query->getSingleScalarResult()` you get the number directly in your `$count` variable. I don't see why this is complex? I do the same query as you do but I use a parameterized one (to prevent sql injection). – Pi Wi Jun 24 '15 at 08:52
You used the $rsm variable that is a new class I have to create, or not? – Aerendir Jun 24 '15 at 13:38
Thank you for the advice about getSingleScalarResult()! – Aerendir Jun 24 '15 at 13:38
@Aerendir please read the documentation. You should use the result mapping which is the `$rsm` – Pi Wi Jun 24 '15 at 13:40

score 0 · Answer 2 · edited May 23 '17 at 12:00

As the suggested solutions seems too much complicate to me, I continued to search for a more simple solution and I came up with this:

public function getMessagesToPost($user)
{
    $query = 'SELECT COUNT(*) FROM `messages` WHERE `posted_by` = :userId AND `message_posted` = 0';

    $params = array(
        'userId' => $user->getId()
        );

    /** @var \Doctrine\DBAL\Statement $preparedQuery */
    $preparedQuery = $this->em->getConnection()->prepare($query);
    $preparedQuery->execute($params);

    $result = $preparedQuery->fetch();

    // Return the first value of the returned array
    return array_shift($result);
}

Inpiration form here.

Doctrine and Symfony2: How to execute raw queries (General error: 2014 Cannot execute queries while > other unbuffered queries are active)

2 Answers2