Edit report at https://bugs.php.net/bug.php?id=64531&edit=1
ID: 64531
Updated by: cmb@php.net
Reported by: phplists at stanvassilev dot com
Summary: SQLite3Result::fetchArray runs the query again.
Status: Verified
Type: Bug
Package: SQLite related
Operating System: Windows XP SP3 32bit
PHP Version: 5.4.13
Block user comment: N
Private report: N
New Comment:
While the fix for SQLite3::query() is trivial, a general fix for
SQLite3Stmt::execute() is impossible, since it is allowed to
::execute() the same statement multiple times, which might trigger
sqlite3_reset()s at unexpected times (see, for instance, bug
#73530).
Previous Comments:
------------------------------------------------------------------------
[2018-01-31 16:37:12] thomas dot loch at fusion-core dot net
I've just encountered this issue on Linux and Version 5.6.30 while using a similar generic
wrapper around prepare/bind*/execute(). Is there still intent to fix this after five years?
------------------------------------------------------------------------
[2016-06-27 15:43:10] cmb@php.net
Confirmed: <https://3v4l.org/MMGio>. And yes, this is a
bug, and
not a documentation problem (nonetheless, DDL and DML statements
should use ::exec()).
------------------------------------------------------------------------
[2015-07-18 13:49:57] dupa at dupa dot com
Same problem here, duplicate inserts with "query" method.
------------------------------------------------------------------------
[2014-12-27 11:21:36] yuri dot kanivetsky at gmail dot com
I've run into it trying to do:
function sq($query, $params = array()) {
if ($params) {
$stmt = sdb()->prepare($query);
if ( ! $stmt) {
die("sqlite: " . sdb()->lastErrorMsg());
}
foreach ($params as $param) {
$r = call_user_func_array(array($stmt, 'bindValue'), $param);
if ( ! $r) {
die("sqlite: " . sdb()->lastErrorMsg());
}
}
$res = $stmt->execute();
if ( ! $res) {
die("sqlite: " . sdb()->lastErrorMsg());
}
} else {
$res = sdb()->query($query);
if ( ! $res) {
die("sqlite: " . sdb()->lastErrorMsg());
}
}
$r = array();
while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
$r[] = $row;
}
return $r;
}
Made it work by wrapping the last part in if ($res->numColumns()) {:
...
}
if ($res->numColumns()) {
$r = array();
while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
$r[] = $row;
}
return $r;
}
}
------------------------------------------------------------------------
[2013-03-27 17:23:59] phplists at stanvassilev dot com
I've been discussing this in #PHP.PECL, with auroraeos and others and basically
the problem is caused by the OOP interface in the binding.
The binding introduces extra logic so query() in PHP can return a resultset
object or false etc.
In order to know what to return, query() calls sqlite3_step() which fetches the
first row of the result.
So far so good. Here's the problem:
The binding then *resets* the query so the first call to fetchArray() returns
row1 again. This is not library behavior, it's binding behavior that's
additional logic.
This causes row1 to be computed twice, and queries to run twice etc.
The solution for solving this without introducing BC breaks and interface
breaks, is for the binding to store the row it fetched during query() with the
resultset, as outlined in my previous comment, and return it on first call to
fetchArray, without calling step.
On subsequent calls to fetchArray(), step is called to fetch the other rows.
Either way you look at it, the binding added behavior that isn't in the original
library, and this is causing performance issues and side effects. The
responsible thing is to keep the PHP OOP interface compatible, but fix the
performance issues and side effects.
And what I described is how to do it...
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
https://bugs.php.net/bug.php?id=64531
--
Edit this bug report at https://bugs.php.net/bug.php?id=64531&edit=1