the problem with PDO

At long last I’ve decided to use the latest and greatest PDO in a new app, switching from good old PEAR MDB2/DB for all database access. This is supposed to give you added speed, uniformity and a consistent standard to use throughout PHP-land. But the infatuation was short-lived.

So far the following ugly heads have been reared:

1. as has been discussed many times, PDO is not a database abstraction layer, but a simple database access layer. I am usually in favor of the removal of bells and whistles, and I know trying to make mySQL, postgreSQL, Oracle, DB2, MSSQL , SQLLite, etc. all act alike is not a trivial undertaking. But the advantages to abstraction are many. Not sure of the exact reasoning for this, perhaps the abstraction is to be left to yet another layer.
2. there is no $sth->numRows() or equivalent. This may be an abstraction issue, as some RDBMSs are not able or not willing to determine how many rows are in a result set before actually retrieving it all (Oracle). But for mySQL and others this presents a crippled interface. There are ways around it, but one of the points of using a database access class is to make life easier, not to put up more hoops you have to jump through.
3. No sequences. PDO does not plan to support them. I haven’t been able to figure out why, but again this presents either a broken, crippled version of my driver, or fails to give me a feature that is easily emulated.

And lastly, but most fatally:

• The PDO class apparently expects only one open statement handle or query at a time. This means the following code becomes impossible:

  $sth_ins = $pdo->prepare("INSERT INTO some_table (name) VALUES (?)");
  $sql = "SELECT foo FROM tmp_table";
  $sth = $pdo->query($sql);
  while ($row = $sth->fetch(PDO::FETCH_NUM)) {
      $res = $sth_ins->execute(array($row[0]));
  }

This is from a script being used to migrate some data from a legacy database to a new schema, and is a pretty common pattern for that purpose – doing an INSERT or UPDATE inside a iteration over some result set.

When doing this we get a message that seems to want to help:

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.


But I don’t plan on never using anything but mySQL, and even if I did I don’t like having another flaming hoop placed in my path, something else to remember.

I could fetchAll() records first into an array, but I usually prefer to not lay awake at night wondering if my scripts are going to eat all the memory.

So in short, for these reasons then MDB2 will have to be good enough. It has a few quirks, and the ability to move to exceptions over PEAR_Error would be nice, but there are workarounds for that (to be posted later).