We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Unbuffered queries error in mysql, pdo config doesn't working, every third query returns empty data

Hi!

Situation

I'm using MySQL and Phalcon 2.0.9 and have this snippet in my Models\Base:

    /**
     * @param string $sql
     * @param array  $params
     *
     * @return Resultset\Simple
     */
    public static function findByRawSql($sql, array $params = [])
    {
        $self = new static;
        try {
            /**
             * @type \Phalcon\Db\Adapter\Pdo $conn
             * @type \Phalcon\Db\Result\Pdo  $query
             */
            $conn   = $self->getReadConnection();
            $query  = $conn->query($sql, $params);
            $result = new Resultset\Simple(null, $self, $query);
        }
        catch (\PDOException $e) {
            return false;
        }

        return $result;
    }

What i do

Then i do in my Models\User:

$friends = static::findByRawSql('CALL GetUserFriends(:id)', ['id' => $this->id]);

But have PDOException:

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.

Solvings and problems

I've googled about this and PDO::MYSQL_ATTR_USE_BUFFERED_QUERY doesn't help. If i do fetchAll then i can't use Resultset.

Plus, i can't explain this case:

  1. Send request to my site – in controller do: $friends = static::findByRawSql("CALL GetUserFriends(:id)", ["id" => $this->id]); – all is OK – array of arrays with users data
  2. Repeat step no.1 – all is OK – same
  3. Repeat step no.1 one more time (third) – FAIL – empty array.

Repeats only if i use findByRawSql – other requests to databse working everytime and returning same values.

Questions

So my questions are simple:

  1. How can i use Resultset and not getting into this error?
  2. Why PDO configuration doesn't work?
  3. Why findByRawSql returns different empty result every third query? o.0


58.4k

Hi

You can take look an example how to use Resultset at https://phalcontip.com/discussion/36/how-to-use-subqueries-in-phalcon



21.7k
edited Jan '16

Thank you for reply, Thien!

Got it, difference between direct toArray in your tip (i guess, it internally calls fetchAll?). But in this case i need Resultset – with ability to use methods like count/filter/valid/serialize/usw. Plain old php array not suitable here.

Furthermore, if i will need plain array, i can just reach my goal without any resultset and other side objects simplier:

return (new static)->getReadConnection()->query($sql, $params)->fetchAll();

My question is about getting data as Resultset (or any structure that have model interface) and use it anywhere else (in response transformer inside api for example). Maybe, i can call fetchAll and then somehow use it as result while creating Resultset? It requires \Phalcon\Db\Result\Pdo as a result in constructor.