Do unbuffered queries for one request

Question

I'm looking to do unbuffered queries only on some requests.

In MySQL I was doing this:

$req = mysql_unbuffered_query('SELECT * FROM forum_topics
ORDER BY (topic_id/topic_stick) DESC, topic_last_post DESC');
while($data = mysql_fetch_assoc($req)) {
   // display results...
}

I looked at PHP doc, and according to it in pdo we must proceed this way to do queries unbuffered:

$pdo = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_pass');
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$uresult = $pdo->query("SELECT Name FROM City");

if ($uresult) {
   while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) {
       echo $row['Name'] . PHP_EOL;
   }
}

But is it possible to do it unbuffered only for the "forum_topics" table results without setting all pdo instance to unbuffered?

Answer 1

You can set the attribute on the PDO connection:

$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

then run this particular query which result needs to be unbuffered,

$uresult = $pdo->query("SELECT Name FROM City");
while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) {
    echo $row['Name'] . PHP_EOL;
}

and then set the attribute back

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

Answer 2

Re, this doesn't work, I obtain an error while using your method:

SQLSTATE[IM001]: Driver does not support this function: This driver doesn't support setting attributes

What's wrong?

Edit : I found the solution on php.net doc.

If you use this:

$sth->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

It doesn't work.

But if you set it in an array in prepare(), it works fine.

$sth = $pdo->prepare('SELECT * FROM my_table',
array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false));

I hope this will help people who haven't found a way for this problem.

Answer 3

MySQL does not implement statement-level attribute setting.

Source:

Here is your error message:

• https://github.com/php/php-src/blob/master/ext/pdo/pdo_stmt.c#L1630

  pdo_raise_impl_error(stmt->dbh, stmt, "IM001", "This driver doesn't support setting attributes");
  

And the condition above is checked on the stmt->methods->set_attribute above.

The stmt->methods is defined above and the type is declared at:

• https://github.com/php/php-src/blob/5d6e923d46a89fe9cd8fb6c3a6da675aa67197b4/ext/pdo/php_pdo_driver.h#L417

  struct pdo_stmt_methods
  

The set_attribute parameter is the 10th struct entry.

Here is the MySQL PDO implementation. And the statements methods are defined here:

• https://github.com/php/php-src/blob/2ba4fb126391c578d20d859c841c4d31cd14adef/ext/pdo_mysql/mysql_statement.c#L935

  NULL, /* set_attr */
  

This shows that the MySQL PDO module does implement that feature.

Discussion:

I have reviewed other extensions. And only the Firebird PDO database module supports that feature.

Answer 4

The answers on here are all trying to use MYSQL_ATTR_USE_BUFFERED_QUERY on the statment. and MYSQL_ATTR_USE_BUFFERED_QUERY only operates on the entire connection, as you seem to have sussed out.

MYSQL_ATTR_USE_BUFFERED_QUERY also only works if you're using the mysqlnd library - which odds are good you are if you're using PHP 7 or higher.

Your original method of setting the connection as unbuffered was the correct and only actually functional method.

$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

But is it possible to do it unbuffered only for the "forum_topics" table results without setting all pdo instance to unbuffered?

Not all instances are set to unbuffered, only that "instance" of that connection. You can either simply immediately turn buffering back on ala:

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

The problem is that you can only iterate a single query per connection when in unbuffered mode, so you cannot run a second query until you have retrieved all data from the first set.

Normally you only want to use unbuffered queries for very large datasets. I would recommend to use a second PDO connection to the database specifically for unbuffered queries that you open only when you need to run an unbuffered query, aka:

$pdo2 = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_pass');
$pdo2->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

Answer 5

As an workaround if my query is a SELECT, I don't call the fetchAll function.

                $query = 'SELECT ...... ';
                $arr = explode(' ', $query);
                $query_type = strtolower($arr[0]);
                if ($query_type == 'select') {
                    $query_response = $query_prepare->fetchAll(PDO::FETCH_ASSOC);
                } else {
                    $query_response = '';
                }

Also you must treat the exception when you accidentaly put a space at the begining of the query. Hope this is helpful.