PDO... is it really worth the hype?

I've been playing a bit with PDO That's the new database .... well I'd guess you'd call it api abstraction extension(s) that're supposedly coming with php 5.1. The idea behind it is a good one, to change database api calls so they're all the same, making db abstraction a heck of a lot easier. This is a great idea, but the implementation still leaves something to be desired.

First of all, there aren't very many drivers available - currently mysql, firebird, mssql, oci, postgresql, and odbc are supported. Notice one missing? yup, so did I. I use mysql 4.1 on a daily basis, and mysql isn't going to cut it for me. I'm a weird bat, I LIKE to develop on windows so I don't have the option of simply recompiling with the right mysql library. Secondly, pdo very heavily pushes the bound parameter idea. Quite frankly, to me, this looks like bad juju. There's just too many ways to lose track of what is bound where. Actually the binding in doesn't really bother me that much - makes autoescaping easily and loops of queries work quickly. But binding out is bad news mister. Here, I'll show you what I mean.

[php]
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO_PARAM_INT);
$sth->bindParam(':colour', $colour, PDO_PARAM_STR, 12);
$sth->execute();
[/php]

There's a block straight from the manual. Ok, makes sense, we tell it calories is an int and colour is a string and execute that query, no big deal. To me it looks like a lot of code unless you're doing a loop, but that's just me.

[php]
$sql = 'SELECT name, colour, calories FROM fruit';
try {
$stmt = $dbh->prepare($sql);
$stmt->execute();

/* Bind by column number with an explicit data type & length */
$stmt->bindColumn(1, $name, PDO_PARAM_STR, 64);

/* Bind by column number with default data type & length */
$stmt->bindColumn(2, $colour);

/* Bind by column name with default data type & length */
$stmt->bindColumn('CALORIES', $cals);

while ($row = $stmt->;fetch(PDO_FETCH_BOUND)) {
$data = $name . "t" . $colour . "t" . $cals . "n";
print $data;
}
}
catch(PDOException $e) {
print $e->getMessage();
}
[/php]

This example, also from the documentation, scares me witless. That's a good way to lose track of what you're assigning where and where things are coming from. Looks to me like an excellent way to make bugs.

Except for my problem with binding output to variables it's not too bad...but I do have a BIG gripe with the way they do results. I'm a lazy lazy person at heart, and I like foreach. In english, right now I always put my result resources into objects so I can iterate over them. For some reason they didn't provide iterator functionality for the results, instead you have to use fetch to get any results. I'll show the difference between the two ways below:

[php]
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

while ($row = $stmt->fetch(PDO_FETCH_ASSOC)) {
print_r($row);
}
[/php]

But I want to be able to iterate the result like so :

[php]
$sth->query("SELECT name, colour FROM fruit");

foreach($sth as $row)
{
print_r($row);
}
[/php]

The biggest thing is it wouldn't take that much to provide the functionality, but I know no C so.... don't give me the song and dance about "write it yourself"...I could but it would probably break more than it fixed :)

Bottom line, PDO is a good start toward a common api but it leans a bit too much towards bound parameters/columns and simple things are made way too hard. On the other hand it could go a long way towards easing the size of current database abstraction layers in userspace. So I guess I'll reserve opinion until PDO finds some api stability and some of the fancier features for databases are implemented.

Edit:

I've been perusing the source code and the iterator stuff IS in there...if someone would just write a dang mysqli driver I could test some of this stuff out - and I'd love to play with the lob as a stream stuff... Maybe I will end up being a convert :)

Comments

Be the first to write a comment!

Post a Reply