ID: 47145
User updated by: pcdinh at gmail dot com
Reported By: pcdinh at gmail dot com
-Status: Feedback
+Status: Open
Bug Type: SQLite related
Operating System: Windows XP
PHP Version: 5.3.0alpha3
Assigned To: scottmac
New Comment:
Hi Scott,
Here is the test script:
<?php
// By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE.
$db = new SQLite3('./test.db');
$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);
$files = array(
array(
'class' => 'class1',
'version' => 1,
'title' => 'Sample 1',
'desc' => 'Sample description 1',
'url' => 'Sample url 1',
),
array(
'class' => 'class2',
'version' => 2,
'title' => 'Sample 2',
'desc' => 'Sample description 2',
'url' => 'Sample url 2',
),
);
foreach ($files as $file)
{
/* @var $stmt SQLite3Stmt */
$stmt->bindValue(':class', $file['class'], SQLITE3_TEXT);
/* @var $plugin Pone_PluginInfo */
$stmt->bindValue(':version', (int) $file['version'],
SQLITE3_INTEGER);
$stmt->bindValue(':title', $file['title'], SQLITE3_TEXT);
$stmt->bindValue(':desc', $file['desc'], SQLITE3_TEXT);
$stmt->bindValue(':author', $file['class'], SQLITE3_TEXT);
$stmt->bindValue(':hooks', $file['class'], SQLITE3_TEXT);
$stmt->bindValue(':url', $file['url'], SQLITE3_TEXT);
$stmt->bindValue(':deps', $file['class'], SQLITE3_TEXT);
$stmt->bindValue(':status', 3, SQLITE3_INTEGER);
$rs = $stmt->execute();
if (false !== $rs)
{
/* @var $rs SQLite3Result */
// Closes the result set
$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();
}
// Close statement
$stmt->close();
// Close database connection.
// Error: Unable to close due to unfinalised statements
$db->close();
?>
Previous Comments:
------------------------------------------------------------------------
[2009-01-19 11:28:56] [email protected]
Could you give me a reduced testcase that isn't working for you?
Ideally one that I can just paste into a file and test.
------------------------------------------------------------------------
[2009-01-19 10:08:43] pcdinh at gmail dot com
I have chosen to keep $stmt->clear() only already but the warnings are
so confusing and I can not close database as well.
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::execute() [sqlite3stmt.execute]: Unable to
execute statement: library routine called out of sequence in
D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line
261
Error: library routine called out of sequence
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
282
D:\wvbsrc\repos\pone\trunk\tests\Plugin/plugins/plugins.db
I have read your link http://sqlite.org/c3ref/reset.html and it offers
no clue to understand why database connection can not be closed
So I guess that SQLite3 does not allow me to execute a prepared
statement in a loop. Prepared statement is designed for that usecase,
isn't it?
------------------------------------------------------------------------
[2009-01-19 10:00:12] [email protected]
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();
------------------------------------------------------------------------
[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
