Hi Richard
I think the main problem here is that you are used to a common technique
that is implemented in most of the "non-PDO" database extensions. The
technique is the auto storing of the entire query result in memory by the
*_query() function. The resource returned by functions like mysql_query()
and msssql_query() is a pointor to a "PHP extension" managed memory block
that contains all of the query result data. However, PDO::query()
returns a resource that points to a "DBMS API" managed statement handle.
When you call mysql_fetch(), the extension is just giving you result
data that was previously fetched from the DBMS and stored in the memory
block. In POD, the fetch() function has to retrieve the data from the
DBMS before it gives it to you. This is why you are not able to get a
"pre fetch" row count with PDO. The use of row buffering is also why you
are able to perform nested queries, as illustrated in your example. Most
DBMS APIs do not allow you to maintain more than one statement handle
with an "open" result set. So, this is not the fault of PDO.
While you and others may prefer the advantages of row buffering, it is a
very inefficient method for the retrieval of large result sets. I believe
the decision of the PDO team is that it was not worth the price of
performance and implementation. Especially since abstraction layers like
PEAR DB and ADODb have the ability to perform row buffering. Which means
is would be done twice!
-- bob
On Mon, 28 Feb 2005, Richard Thomas wrote:
> Thanks, while I suspect it would be rare for someone to mix execute and
> querys what you do see more often would be code like this
>
> $result = $db1->query('SELECT * FROM help_category');
> while($row = $result->fetch()) {
> $result2 = $db1->query('SOME QUERY BUILT ON $row DATA');
> }
>
> This is very common and will not work at all with PDO instead you have to
>
> $result =$db1->query('SELECT * FROM help_category');
> $rows = $result->fetchall();
> foreach($rows as $value) {
> $result = $db1->query('SOME QUERY BUILT ON $row DATA');
>
> }
>
>
> Nothing wrong with that at all now that a fetchall exists it makes total
> sense to do it this way but being fetchall is "new" I can see a lot of
> people trying method 1 and getting frustrated with it not working.
>
> Richard Thomas - CEO
> Cyberlot Technologies Group Inc.
> 507.398.4124 - Voice
>
>
>
> Dan Scott wrote:
>
> >On Mon, 28 Feb 2005 09:27:07 -0600, Richard Thomas <lists@xxxxxxxxxxxx>
> >wrote:
> >
> >
> >>I am sorry I feel like a idiot here ;) Because in my example I changed
> >>from $stm to $result the value of $stm was still open hence the cursor
> >>was still open
> >>
> >>The following does work. This should be put in the docs as clearly as
> >>possible to avoid confusion by anyone else.
> >>
> >>
> >
> >Richard: I will update the docs to include a good warning about the
> >use of rowCount() with SELECT statements, and to cover this particular
> >gotcha. Thanks for the suggestion!
> >
> >
> >
> >><>
> >>$stm = $db1->prepare('SELECT * FROM help_category LIMIT 1');
> >>$stm->execute();
> >>print_r($stm->fetch(PDO_FETCH_ASSOC));
> >>
> >>$stm = $db1->prepare('SELECT * FROM help_category LIMIT 1');
> >>$stm->execute();
> >>print_r($stm->fetch(PDO_FETCH_ASSOC));
> >>
> >>$stm = $db1->query('SELECT * FROM help_category LIMIT 1');
> >>print_r($stm->fetch(PDO_FETCH_ASSOC));
> >>
> >>$stm = $db1->query('SELECT * FROM help_category LIMIT 1');
> >>print_r($stm->fetch(PDO_FETCH_ASSOC));
> >>
> >>Thank you, sorry it took me so long to understand what was going on.
> >>
> >>Richard Thomas - CEO
> >>Cyberlot Technologies Group Inc.
> >>507.398.4124 - Voice
> >>
> >>Wez Furlong wrote:
> >>
> >>
> >>
> >>>It works because you are re-assigning $stm with a new value; that
> >>>causes the old $stm to be deleted prior to creating the new one; in
> >>>other words, it doesn't have to be NULL that you assign to
> >>>close/delete the statement object.
> >>>
> >>>--Wez.
> >>>
> >>>On Mon, 28 Feb 2005 08:23:00 -0600, Richard Thomas <lists@xxxxxxxxxxxx>
> >>>wrote:
> >>>
> >>>
> >>>
> >>>
> >>>>There is something diffrent however between a PDO::query and a
> >>>>PDO::prepare and execute, Because 2 prepare/executes in a row work
> >>>>without having to declare anything NULL, Run the code there is no
> >>>>confusion on my part in how this works, Well there was confusion in that
> >>>>I assumed that since 2 prepare/executes do work without declaring the
> >>>>stm null the fact that 2 querys didn't had to be a bug in my mind.
> >>>>
> >>>>$dsn = 'mysql:dbname=mysql;host=localhost';
> >>>>$db1 = new PDO($dsn, 'root', '');
> >>>>$db1->setAttribute(PDO_ATTR_ERRMODE, PDO_ERRMODE_EXCEPTION);
> >>>>
> >>>>
> >>>>$stm = $db1->prepare('SELECT * FROM help_category LIMIT 1');
> >>>>$stm->execute();
> >>>>print_r($stm->fetch(PDO_FETCH_ASSOC));
> >>>>
> >>>>$stm = $db1->prepare('SELECT * FROM help_category LIMIT 1');
> >>>>$stm->execute();
> >>>>print_r($stm->fetch(PDO_FETCH_ASSOC));
> >>>>
> >>>>Shouldn't work but does?
> >>>>
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>--
> >>PECL development discussion Mailing List (http://pecl.php.net/)
> >>To unsubscribe, visit: http://www.php.net/unsub.php
> >>
> >>
> >>
> >>
> >
> >
> >
>
> --
> PECL development discussion Mailing List (http://pecl.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
|
