Why I think PDO sucks
April 2, 2008
Every so often I try to use PDO under PHP5, and every time I run in to basic functionality problems with no ability to find out what’s going on under the hood. A recent example:
$dbh = new PDO(’mysql:host=localhost;dbname=db’, “user”,”pass”);
$stmt = $dbh->prepare(”SELECT * FROM table where service_type = ? and name = ? “);
if ($stmt->execute(array(2,$short_name))) {
….
The ‘if’ line is giving me a FATAL_ERROR “accessing execute() method on a non-object”.
So the ‘prepare’ statement didn’t work. But why not? Throwing a try/catch around the prepare statement caught nothing – it just happily went on to try to execute the sql against $stmt even though it wasn’t an object. How do you debug this stuff? There’s no docs I can find on debugging this sort of behaviour, and given that PDO is a few years old, I shouldn’t have to. BTW, I know it’s out of date – this is 5.1.6, so I am going to try to update today.
Another thing – how do you see what the final SQL statement sent to the DB would be? Again – never seen any docs on this. Go ahead – embarrass me and show me where this is documented so I look like a complete jerk.
If PDO was as good as it should be, I don’t think there’d be such a movement underway to rewrite a PDO2, which appears to be the case.
Also, in the docs, there’s *mention* of “if prepare doesn’t work you’ll get back FALSE”, but *no* examples show ‘proper’ behaviour of checking if your $stmt is an object or FALSE. Some examples do show try/catch, IIRC, but that doesn’t seem to work (again, at least in 5.1.6).
Prove me wrong, someone, please.
Posted in 
Entrepreneur, developer, writer, podcaster, teacher, ex-bassist.
April 2nd, 2008 at 1:01 pm
OK – just been told that things work better in 5.2, so the official title should be changed to ‘Why I think PDO sucked’.
April 2nd, 2008 at 1:23 pm
You do know that aside from connect errors, by default PDO stays silent in all error cases. If you want exceptions to be thrown, you have to set the error handling accordingly as stated in the PDO docs:
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
That being said, it would be nice to get some basic stuff like the last executed statement. This is especially important since POD forces prepared statements which causes some parsing and rewriting to be triggered. So yeah debugging could be improved in PDO.
April 2nd, 2008 at 1:28 pm
Lukas – thanks for the reply. This was written in the heat of the moment, and I did get things working, but I have struggled with PDO on and off for awhile. I’ve had people tell me that you can in fact see the full SQL statement, but I’ve never seen how to do that.
It would help if the docs would show full examples of how to use PDO instead of snippets – like try/catch around prepare(), for example, with the ‘exception’ setting you just gave above. PHP docs were great for all the PHP4 series stuff, but the same level of usefulness hasn’t been there for PHP5 ‘advanced’ things the past few years. Just an observation/opinion, of course.
Thanks again.
April 2nd, 2008 at 4:11 pm
Looks like you’ve solved this, but…
Just a shot in the dark, but I’ve run into various cases where SELECT * didn’t work, but SELECT field1, field2, field3 worked fine.
I personally don’t use PDO, but have been curious about it.
–df
April 2nd, 2008 at 6:36 pm
ext/mysqli (which we use 100%) has a very similar behavior when dealing with prepared statement errors. You should see some of the gobbledygook error messages you get back out if you’ve got your own statement subclass…
The far and away “best thing to do”, IMO at least, it to write your own wrapper class (either as a facade or straight subclass) of either mysqli or PDO which does hard core error handling and logging and always use that. Otherwise you’re going to end up with wall-to-wall error handling & logging code throughout your source if you’re doing anything more complicated than “hello world.” Conservatively, I have ~10x lines of code in error handling/logging than I do in direct mysqli calls in my wrapper class just to give you an idea.
The answer to your question “how do you see what the final SQL statement sent to the DB would be?” is the argument that you pass to the prepare method is sent exactly that way to the database. The bound variables are passed in a subsequent communication to the database.
As a follow-on point, if you want to get advantage from the MySQL query cache, you need to be completely consistent with casing, (http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html), or you’ll get less to no value from it. (I noticed your “and” was lower case, but all other SQL keywords were upper case.)
You’ll be much happier with 5.2.x than 5.1…
Andrew
April 3rd, 2008 at 6:00 am
Form what I’ve seen, PDO is actually just yet another way to use MySQL databases. Yes, it does support MSSQL and ODBC on paper, and no, it doesn’t work in real life – we have had completely different behaviour on php 5.2.3 and 5.2.5, it kept crashing Apache, query proparing wasn’t implemented for MSSQL and so on. So makes me wonder – if it does have all these problems, even thoguh it looks good on paper, is it any good at all deeper inside? I’m not touching PDO again for some time until it matures.
April 3rd, 2008 at 6:01 am
There’s a really good PDO book (and so far this is the only one on the topic AFAIK)
http://www.amazon.co.uk/Learning-Data-Objects-Dennis-Popel/dp/1847192661/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1207224117&sr=8-1
April 3rd, 2008 at 6:10 am
Sorry, can’t prove you wrong.
In addition to your pains, I have had a number of bugs where, if a PDOStatement was alive at the end of the script, then PHP would segfault. And I’d have no clue *where* the problem statement was in scope. It took me a while to even figure out that PDO was the problem.
Worse, subclassing PDOStatement seems to add more segfaults to the mix, so I don’t do it. If I get a chance, I’m going to convert my PDO wrapper class to use MySQLi under the hood. Worst extension ever.
And if you ever find out how to enforce connection limits with PDO, you should blog about it.
April 3rd, 2008 at 6:11 am
Here’s a list of things that didn’t work or worked problematically with PDO used for MSSQL or ODBC:
- multiple resultsets
- prepared statements (with procedures)
April 3rd, 2008 at 7:32 am
You are just an asshole that can’t really program
April 3rd, 2008 at 9:29 am
PDO does suck. I hate the inconsistencies between drivers. It doesn’t provide any real API abstraction between databases, since, for example, Oracle has a whole bunch of quirks (like not supporting ? placeholders). And blob handling is inconsistent. And the limitations in the base PDO class almost require you to subclass it.
Plus it segfaults when you try to use PDO streams within user-defined streams in PHP. That ticket has been open for years now. And there are a whole bunch of open PDO tickets that have been open for years now.
That said, I’m glad to see it build into the language. I just wish it was actually being maintained or improved … It gets added to the list (with DateTime) of why maybe the PHP internals developers should not bother adding new OO extensions to the engine. And the list of reasons to switch to Python
Hans
April 3rd, 2008 at 2:59 pm
Hi all I’m developing a CMS called Onpub. Lately I’ve been wondering if I should stick with PDO for data access or switch to mysqli. Can any of you give me sober advice on which library is the best to use for the long run?
If I make a change I would probably want to stick with it pretty much for the foreseeable future. I’m leaning towards switching to mysqli just because it’s been around for longer and seems to get more development love from the PHP community.
All the tumult surrounding PDO on the blogs lately hasn’t done wonders for PDO’s image, at least in my eyes. Also, the fact that mysqlnd is integrated with mysqli and not yet PDO, is another strike against PDO.
Is PDO and PDO_MYSQL specifically, going to stabilize sometime in the near future, or is it becoming a lost cause? Also, what’s the latest on the PDO v2 debate? Is that still happening?
I believe someone high up in the PHP community needs to publicly address (and soon) PDO’s status so PHP developers know where they stand when choosing what mysql library to use for their apps!
April 4th, 2008 at 6:32 am
Holy cow – I didn’t expect this much agreement. I always felt like a heretic by not embracing PDO. Occasionally I got the feeling that I was ‘missing’ something, or not ‘getting it’, because I wasn’t using PDO for all my daily work. As it stands, it seems I’m not alone, but for a variety of reasons.
With all that said, I’m not completely rejecting PDO, and will probably give it a few more attempts given that I now have 5.2.5 installed and PDO is doing what I needed it to before. BUT, I’ll likely stick to previously used/tested/known DB abstraction layers for any mission-critical apps I write, unless I’ve got time for a *lot* of testing (which isn’t usually the case).
April 25th, 2008 at 9:28 am
PDO does suck indeed. All I know is lately whenever I come to do something slightly outside ordinary functionality using PDO or the Zend Library it slows my development time right down as I search through and try to get up to speed with these so called ‘tools’.
A web development tool, for me, improves my productivity and makes coding what I want to achieve a joy. It is simple to pick up and start using, it is flexible, robust and strong.
I don’t really consider these as tools, the learning curve is high, compounded by the shocking examples in the documentation. Its fine though Zend, you don’t need to make these tools easy to use – I just won’t use ‘em. One last thing: Zend Validate – WTF. Way to make something that should be reasonably straight forward instead convoluted, uninteresting and difficult.
April 25th, 2008 at 9:35 am
Why doesn’t the Zend Framework use PDO? The DB libraries go through amazing amounts of hoops to do db-escaping, when PDO has it built-in. Does Zend know something about PDO the rest of us don’t?
July 31st, 2008 at 5:50 pm
PDO sucks. Even the API design is stupid. PDOStatement, for example, combines different responsibilites in a single class and there is no way testing preconditions of an operation before calling the operation, e.g. a non-existing hasParameter(’:test’) before bindValue(’:test’, …); So one has to try and fail… like the API-Design…
August 8th, 2008 at 5:28 am
You guys are scaring me considering I just dropped MDB2 for PDO. It took about 5 hours to subclass PDO to add good exception handling, add convenience functions, add an in-transaction check around the begin/commit/rollback, and some MDB2 emulation methods for my own sake. With that said, all unit tests pass and nothing crazy like segfaults here (I effin hope).
The choice was performance driven — MDB2 consumes way more CPU time per proc and with a one second polling ajax function, it’s a performance hit I don’t want to take. I contemplated going straight to mysqli, but I just found it annoying as hell to work with. Why must I specify the type of every thing I pass in when I run the prepared statement? That sort of manual labor, even if I write code to figure it out dynamically, should be handled in the C code, not left for me to deal with.
And the basic mysql functions lack prepared statements.
Essentials for PDO I find are turning on exception based errors, setting ASSOC for all fetches (overload prepare method and you can set the fetch type before you return the statement object). Also I use emulated prepares because I want the mysql query cache to work and I don’t really need prepares for more than just sql injection prevention. It sure beats dealing with escaping.
If things are so broken with PDO, why isn’t anybody griping in the php.net comments? Php.net has gotten considerably better about documenting php5 OOP style coding, but it’s definitely not as friendly and clear as the old days. There’s no denying that.
August 8th, 2008 at 5:35 am
Incidentally, this heavy polling ajax app was a rewrite from ruby on rails. The rails code was written the rails way and went through a fair amount of profiling and optimizing. When I rewrote for php, I did things the best way I know how for php, still MVC but levaraging all the native goodies of php (i.e. no bloated frameworks like symfony). So this is about the best real world comparison I can imagine. What took 15ms of cputime for rails took php 10ms. When I switched from MDB2 to PDO, this dropped to 5-6ms. Not the most scientifically documented, but it was obvious enough to the client that he’d be saving 3x the hardware costs to run this app in php. Added bonus for the sysadmin side of me, I can turn the memory sucking mongrels off (they’ve been inexplicably memleaking ever since the latest ruby upgrade on FC8).
August 14th, 2008 at 8:48 am
I’ve been knocking my head around for DAYS about why PDO can’t seem to deal with multiple clobs in a single query. I have a query with two rows, each of which has a clob, it returns the first row fine, and then seg faults on the second row.
There is NO documentation about how to return more than one row of clobs. Hasn’t anyone tried to return more than one row of clobs? How is this not built in?