0

I've introduced $PDO->query('SET NAMES utf8;'); in my connection library code to get some Unicode compliance. This works on some hosts where I can follow this with PDO statement executions with INSERT using the .execute().

However, on some hosting plans, I'm encountering this error:

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.

So, before SET NAMES, I introduced $PDO->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, TRUE);, but I'm still getting the error. I then set that value to FALSE and I'm still getting the error.

Of course, if I remove the "SET NAMES utf8" statement, the problem goes away. And again -- this problem only occurs on some web hosts.

What's the proper way to switch MySQL to Unicode that works on most Linux hosting plans?

Volomike
  • 23,743
  • 21
  • 113
  • 209
  • Is it the first query you run using this mysql connection (as a PDO object) ? I were to SET NAMES once a connection is open. – doc_id Apr 16 '12 at 11:14
  • Yes, my code does it only when the connection is first opened, never again afterwards. – Volomike Apr 16 '12 at 13:42

1 Answers1

2

The preferred way to set the connection encoding to UTF-8 is the use of PDO::MYSQL_ATTR_INIT_COMMAND in the PDO constructor, as shown in the manual:

$dsn      = 'mysql:host=localhost;dbname=testdb';
$username = 'username';
$password = 'password';
$options  = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'); 

$dbh = new PDO($dsn, $username, $password, $options);

This should always work.

deceze
  • 510,633
  • 85
  • 743
  • 889
  • Unfortunately this only worked if I created a fresh PDO object each time I needed to do a PDO task. I couldn't implement a static object variable and reuse this after a concluded PDO task. I would get the user buffered query error. So, this wasn't suitable. What do you suggest? – Volomike Apr 18 '12 at 07:58
  • I'm experimenting with using $stmt->closeCursor(); in my code where I execute some SQL and am finished, but want to enable the connection for reuse in another function. Evidently some versions of PHP will give me grief on this unless I use closeCursor()? I'll post back here tomorrow if that works. – Volomike Apr 19 '12 at 08:11
  • My tests check out okay now. I found on one particular hosting plan that I had to implement $stmt->closeCursor() after every call because an unset() or a function end didn't seem to fix it. Most other hosting plans worked without needing that, however. Must be some funky version of PHP, I guess. We're also now running Unicode support without a hitch. – Volomike Apr 20 '12 at 01:26