A while back, I was experimenting with an alpha or beta version of MySQL’s “new” prepared-statement API.
MySQL has a convenience that, if you get disconnected from the server, it will attempt automatic reconnection for you on the fly. You can force a reconnection attempt with mysql_ping() and our code leverages this.
I have a test harness for database code, and I plugged in the new statement API to it and blam straight into a brick wall. The prepared statements don’t persist across connections. And if you have auto-reconnect on, there was no obvious way to tell that your statements had all just been blown away.
Infact, calling a prepared statement might trigger a reconnect, blow away all the statements, and then try to finish what it had started and cause an application crash. I put in a ticket and got a fix. I was happy with the turn around on that, but I ran into brick wall #2.
After walking thru the prepared statement code, I realized that they had a dilema. The prepared statement system doesn’t retain its construction data. In some random act of resource conservation, MySQL throws away the information used to construct prepared statements. So if it loses its connection to the server, it can’t reconstruct the statement and proceed.
I suggested that they retain the data, we’re talking about short strings here – especially when you consider that they contain data markers rather than actual data; or that they provided a way to register a callback or hook of some kind to say “hey, call me if the server dumps you, honey” so you could put all your preps into a function.
I found the negative response to this a little confusing, concluding that prepared statements was some crap they’d had to support because other databases did and whiney users had asked for it.
Although, what really solidified that opinion is when a few revisions later they went ahead and made auto-reconnect no-longer the default.
So MySQL’s prepared statements are, kinda, OK if what you are doing a tight loop on a query that would otherwise generate a silly amount of time sprintf()ing a query and then parsing it. But it really doesn’t provide the larger advantages of a real server-side prepared-statement system where an application can prepare all its statements at the get-go and henceforth just assume that the statement handles will be valid until the database handle isn’t.