ID: 47145 Updated by: [email protected] Reported By: pcdinh at gmail dot com -Status: Open +Status: Bogus Bug Type: SQLite related Operating System: Windows XP PHP Version: 5.3.0alpha3 Assigned To: scottmac New Comment:
This comes from the C level API. http://sqlite.org/c3ref/reset.html If the most recent call to sqlite3_step(S) for the prepared statement S indicated an error, then sqlite3_reset(S) returns an appropriate error code. You can't call $stmt->reset() on a failed statement only $stmt->clear(); Previous Comments: ------------------------------------------------------------------------ [2009-01-19 09:46:43] pcdinh at gmail dot com Description: ------------ I try to use prepared statement to insert a batch of rows into a SQLite3 database. Also, I have created a constraint to make sure that I do not insert duplicate rows. When the constraint is violated, SQLite3 class does not allow me to close the database. The scenario is as follows: 1. I write a script to iterate through a directory and add found files into a SQLite3 empty database. The round 1 is OK because there is no violated constraint. 2. I run it again with the same directory. Now the database is not empty anymore. Lot of warnings are generated as below: Warning: SQLite3Stmt::execute() [sqlite3stmt.execute]: Unable to execute statement: constraint failed in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 261 Error: constraint failed Warning: SQLite3Stmt::reset() [sqlite3stmt.reset]: Unable to reset statement: column plugin_class is not unique in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 275 Warning: SQLite3Stmt::execute() [sqlite3stmt.execute]: Unable to execute statement: constraint failed in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 261 Error: constraint failed Warning: SQLite3Stmt::reset() [sqlite3stmt.reset]: Unable to reset statement: column plugin_class is not unique in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 275 Warning: SQLite3::close() [sqlite3.close]: Unable to close database: 5, Unable to close due to unfinalised statements in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 281 D:\wvbsrc\repos\pone\trunk\tests\Plugin/plugins/plugins.db Reproduce code: --------------- protected function _buildDatabase($path) { $files = new DirectoryIterator($path); // By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE. $db = new SQLite3($this->_databasePath); $sql = 'CREATE TABLE IF NOT EXISTS pone_plugins ( plugin_id INTEGER PRIMARY KEY AUTOINCREMENT, plugin_class TEXT, plugin_version INTEGER, plugin_title TEXT, plugin_desc TEXT, plugin_author TEXT, plugin_hooks TEXT, plugin_url TEXT, plugin_deps TEXT, plugin_status INTEGER )'; $db->exec($sql); $sql = 'CREATE UNIQUE INDEX IF NOT EXISTS pone_plugin_idx ON pone_plugins (plugin_class)'; $db->exec($sql); $sql = 'INSERT INTO pone_plugins ( plugin_class, plugin_version, plugin_title, plugin_desc, plugin_author, plugin_hooks, plugin_url, plugin_deps, plugin_status ) VALUES ( :class, :version, :title, :desc, :author, :hooks, :url, :deps, :status )'; $stmt = $db->prepare($sql); foreach ($files as $file) { if (!$file->isDot() && !$file->isDir()) { /* @var $file DirectoryIterator */ $path = $file->getPathname(); $dotPos = strpos($path, '.php'); if (false === $dotPos) { continue; } include_once $path; $class = str_replace('.php', '', $file->getFilename()); $plugin = new $class(); /* @var $plugin Pone_Pluggable */ $info = $plugin->getInfo(); /* @var $plugin Pone_PluginInfo */ /* @var $stmt SQLite3Stmt */ $stmt->bindValue(':class', $class, SQLITE3_TEXT); $stmt->bindValue(':version', (int) $info->version, SQLITE3_INTEGER); $stmt->bindValue(':title', $info->title, SQLITE3_TEXT); $stmt->bindValue(':desc', $info->desc, SQLITE3_TEXT); $stmt->bindValue(':author', $info->author, SQLITE3_TEXT); $stmt->bindValue(':hooks', serialize($info->hooks), SQLITE3_TEXT); $stmt->bindValue(':url', $info->url, SQLITE3_TEXT); $stmt->bindValue(':deps', serialize($info->depends), SQLITE3_TEXT); $stmt->bindValue(':status', 3, SQLITE3_INTEGER); $rs = $stmt->execute(); if (false != $rs) { /* @var $rs SQLite3Result */ $rs->finalize(); } else { echo 'Error: '. $db->lastErrorMsg(); } // Resets the prepared statement to its state prior to execution. // All bindings remain intact after reset. $stmt->reset(); $stmt->clear(); } } $stmt->close(); $db->close(); } Expected result: ---------------- I believe that the error happens to a single query (prepared statement) will be cleared out with $stmt->reset(); $stmt->clear(); Therefore, there will be no problem with $stmt->close(); $db->close(); No warning should be emited and database should be closed as normal. Actual result: -------------- Lot of warnings are generated as below: Warning: SQLite3Stmt::execute() [sqlite3stmt.execute]: Unable to execute statement: constraint failed in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 261 Error: constraint failed Warning: SQLite3Stmt::reset() [sqlite3stmt.reset]: Unable to reset statement: column plugin_class is not unique in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 275 Warning: SQLite3Stmt::execute() [sqlite3stmt.execute]: Unable to execute statement: constraint failed in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 261 Error: constraint failed Warning: SQLite3Stmt::reset() [sqlite3stmt.reset]: Unable to reset statement: column plugin_class is not unique in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 275 Warning: SQLite3::close() [sqlite3.close]: Unable to close database: 5, Unable to close due to unfinalised statements in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 281 D:\wvbsrc\repos\pone\trunk\tests\Plugin/plugins/plugins.db ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=47145&edit=1
