.
And then, you realise your code is still playing in the kindergarten - Grin with cat attached
Previous Entry Next Entry
And then, you realise your code is still playing in the kindergarten Jun. 19th, 2006 12:20 pm
Posted to PHP-London; copied here just in case someone can make sense of it... in fact anyone who's sharp on SQL / DB systems might be able to provide pointers...


Please excuse the mild confusion in the following as it's not entirely clear in my own head yet.

I have some code that I'm porting from mysql_thingummy() function calls to a PDO interface; unfortunately, this keeps blowing up with:

Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 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.'

Now, the documentation (and PHP bug responses) for PDO Really Ain't Great(TM) (sorry Wez, oh and PS *please* tag your blog), but having done a bit of reading around, I've got the impression that it's to do with buffered / unbuffered queries (well duh, that's what the message says) but more critically and informatively, it's to do with the fact that:

SQL can only run one query (per connection?) at once. Thus, my "trick" of opening one query, iterating through its result set and
doing further queries (using that data) while doing so only works on mysql_*() functions because they buffer by default, and in fact MySQL has in fact thrown out the previous query handle and just dumped/buffered everything to memory before it lets me do the lower-level queries.

PDO doesn't buffer the query results, so when I get to the second/internal query, it tells me "Nope, alrady got a query open, not touching that one!".

To try and explain that a bit:

Outer query:
WHILE ($res='Select jobid from joblist where ~something~') {
Inner query : select * from job where id = ~jobid from outerquery~
}

By preference, let's leave aside the "so do a JOIN" answer as 1) I can see it, 2) it's not the question I need answered right now and 3) the old code won't directly support it.

So, am I right in my interpretation that :
1) I can only run one query at once
2) that I've only been getting away with it in the past because mysql_*() buffers by default and hadn't been doing what I was assuming (ie, keeping the DB cursor open and getting the data on request)
and
3) unbuffered queries are the "non-grown-up" (in wez-speak) way of doing things so I should drag my code screaming through adolescence and not try and do the above iterative-pointer trick?

If so, please excuse me if I now scream briefly, and then get on with it.


Many thanks in advance,

Richard

From: babysimon
Date: June 19th, 2006 - 12:06 pm (Link)
1) and 2) sound right, although I know much more about PostgreSQL than MySQL. I would certainly not expect to be able to start a query on a connection while pulling results back from that connection.

I wonder if you can create more than one connection at once? In JDBC you would just get a second Connection object, a very quick look at the PDO docs makes it look like the PDO constructor returns the equivalent of a Connection so that might help. Of course, this will not be ACID as the two connections may get different views of the database but if you're using MySQL I doubt you're bothered.
From: wechsler
Date: June 19th, 2006 - 01:10 pm (Link)
PDO does (AFAIK) support multiple connections to one DB but our wrapper to it currently doesn't (for the obvious reason). Plus it's quite possible that the old buffered queries weren't as ACID as I thought - although AFAIK MySQL is now fully ACID-capable, and we're using the latest stable version.