PDO prepared statements

Roger_Ramjet

Since prepared statements only endure for the life of the (database client) session and since we try not to run queries inside loops, one has to ask 'What's their use?'

Would a statement endure if one were using persistent connections?

I can find no information on this but assume that they would for the specific connection because transaction blocks can. But how would one determine if the 'arbitrary' connection being used had already prepared a specific statement and reuse it?

MarkR

You should not set persistent properties on persistent connections- doing so will lead to nondeterministic behaviour and other issues.

One example of this might be using temporary tables - e.g. don't use temporary tables with persistent connections otherwise a previous (failed) request might have left a stale temporary table there and cause problems.

Essentially, if using persistent connections, you must ensure that the connection is guaranteed (even in unexpected error conditions) to be left in the same state as it was found.

There are several reasons for using prepared statements - repeatedly running a similar query is one of them- but you must not leave prepared statements active after the end of a request or problems will ensue.

Mark

Roger_Ramjet

Mark, I know about the problems of persistent connections and objects enduring after errors - viz my mention of transaction blocks.

What I was wondering was whether this behaviour could be usefully exploited with prepared statements - have one there to be called from user session to user session and avoid the overhead of preparing it again. The main problem with using PDO to do this is the lack of a statement name, how does one refer to it?

As to repeatedly running the same query in 1 script/session - not in my apps I don't. If such a situation arose then I would know I had the query or app architecture wrong. (like the folks who post with simple queries in a loop when a join is what is needed)

We have highlighted one thing here - the need to deallocate it at the end of a script using persistent connections, or just reinforced all the reasons against ever using them in the first place.

MarkR

As far as I am aware, there is no way of reusing prepared statements during a connection session (as the PDOStatement object is disposed at the end of the page). In any case, if there was, you would have to be extremely careful to ensure that they were always left in a usable state (e.g. whenever you connect, you always prepare ALL the prepared statements and leave them prepared indefinitely).

Repeatedly running the same query is sometimes useful: What about if you need to do several INSERTs, UPDATEs or DELETEs in the same operation (NB: I know that if you're using a recent client library, you can use batching)?

I realise that looping to emulate a join is inefficient.

There are other reasons for using prepared (or at least parameterised) queries: It makes working with some types of data simpler (e.g. blobs).

Mark