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?

Larkins

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);

Billat

This works ONLY for the Firebird database. See details at https://mcmap.net/q/1612936/-do-unbuffered-queries-for-one-request – Tokharian

@WilliamEntriken I edited this answer to make it factually correct. – Redstart

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.

Larkins

Have you checked, that setting this for the PDOStatement acutally results in an unbuffered query? I tried the vary same thing, and it worked, but it didn't have any effect. The only time when I really get an unbuffered query is when I set the attribute for the PDO object. – Zymolysis

I ran into the same result as @Zymolysis – Boast

This information is false. You cannot set the attribute in prepare call. It fails silently. – Na

Answer 3

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);

Hounding

Still it is not clear what you are trying to tell here. Why would you need a second PDO connection at all? It's a very bad practice per se, and totally unnecessary in this particular case. – Redstart

The use of the second pdo connection is to have one in buffered mode and another in unbuffered mode. A connection in unbuffered mode can only execute a single query at once - so you cannot for instance query the database mid loop of the data - which can be very limiting and cause unexpected errors. In normal use, you'd want nearly all of your queries to be executed in buffered mode except for very large data sets. It makes sense to only create the unbuffered connection when you need to use an unbuffered query. – Hounding

Ok it makes sense, but you better focus your answer on this particular use case. – Redstart

Answer 4

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.

Tokharian

It's a correct finding, and good to know pre se, but from the practical point of view it doesn't really matter as PHP execution is linear, and you can set this setting on the connection back and forth depends on your current needs. – Redstart

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.

Oralla

How is this a workaround? What does this actually solve? – Na