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?

Saberdream · Answer 1 · 2014-01-10T20:34:51.787

10

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.

edited Jan 10 '14 at 20:34

answered Jan 10 '14 at 20:18

Saberdream

187
1
9

7

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. – Ragas Mar 22 '17 at 08:55
I ran into the same result as @Ragas – Emilio P. Feb 13 '19 at 18:35
This information is false. You cannot set the attribute in `prepare` call. It fails silently. – Dharman Jan 14 '23 at 16:52

score 4 · Accepted Answer · edited Jul 01 '20 at 06:22

4

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

edited Jul 01 '20 at 06:22

Your Common Sense

156,878
40
214
345

answered Jan 09 '14 at 17:07

Félix Adriyel Gagnon-Grenier

8,422
10
52
65

This works ONLY for the Firebird database. See details at https://stackoverflow.com/a/62140100/300224 – William Entriken Jun 01 '20 at 20:02
2

@WilliamEntriken I edited this answer to make it factually correct. – Your Common Sense Jul 01 '20 at 06:27

donatJ · Answer 3 · 2020-07-01T15:08:12.897

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

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. — Your Common Sense, Jul 01 '20 at 06:18
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. — donatJ, Jul 01 '20 at 14:54
Ok it makes sense, but you better focus your answer on this particular use case. — Your Common Sense, Jul 01 '20 at 14:58

score 2 · Answer 4 · edited Jun 01 '20 at 20:21

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.

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. — Your Common Sense, Jul 01 '20 at 06:29

score -3 · Answer 5 · answered Jun 06 '19 at 11:48

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.

How is this a workaround? What does this actually solve? – Dharman Jan 14 '23 at 16:56 — Dharman, Jan 14 '23 at 16:56

Do unbuffered queries for one request

5 Answers5