Doc #43942 [Com]: Unexpected SQLite SQLITE_SCHEMA error

Edit report at https://bugs.php.net/bug.php?id=43942&edit=1 ID: 43942 Comment by: chris+php dot net at aptivate dot org Reported by: leon at messiah dot co dot nz Summary: Unexpected SQLite SQLITE_SCHEMA error Status: Not a bug Type: Documentation Problem Package: Documentation problem Operating System: Linux (Ubuntu 7.10) PHP Version: 5.2.5 Block user comment: N Private report: N New Comment: Wait, what? How is this NOT a bug in PDO? Weird random database-specific behaviour is exactly what PDO is supposed to abstract over and hide from the user! Previous Comments: ------------------------------------------------------------------------ [2008-11-07 13:55:11] vrana@php.net PDO::query() internally uses prepared statements but the documentation doesn't document PHP internals. ------------------------------------------------------------------------ [2008-07-30 21:18:42] ezyang@php.net I'm tempted to reclassify this as a documentation problem. PDO::query() does use prepared statements internally (PDO::exec(), however, uses sqlite3_exec and should never throw the schema error). As the caller of sqlite, you're expected to check for this error and re-execute the query as necessary. However, since PDO is responsible for the entire life of the prepared query, this would seem to be something it should transparently handle for the user. Either way, you'll have to fix it yourself. ------------------------------------------------------------------------ [2008-01-26 03:31:24] leon at messiah dot co dot nz Description: ------------ Using PHP 5.2.5 (SQLite 3.4.2) the PHP code below gives the SQLite error SQLITE_SCHEMA (database schema has changed). According to the SQLite documentation: "In SQLite version 3, an SQLITE_SCHEMA error can only occur when using the sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() API to execute SQL, not when using the sqlite3_exec()." http://www.sqlite.org/faq.html#q15 In the following code I AM getting a SQLITE_SCHEMA error, but I am not using prepared queries. It would therefore seem that either the version of SQLite I am using breaks the above promise, or the PHP PDO interface is using prepared queries internally. Reproduce code: --------------- // Print variable utility function show($var) { print "<pre>\n"; print_r( $var ); print "</pre>\n\n"; } // Path of database file $path = '/tmp/pdo-bug.db'; // Create first connection object and create table $conn_1 = new PDO("sqlite:$path"); $conn_1->exec('CREATE TABLE test1 ( key, value );'); // Fetch list of tables using first connection $result = $conn_1->query("SELECT name FROM sqlite_master WHERE type='table';"); show($result->fetchAll()); // Create a second PDO connection object to same database file $conn_2 = new PDO("sqlite:$path"); // Create a second table using second connection $conn_2->exec('CREATE TABLE test2 ( key2, value2 );'); // Fetch list of tables using first connection // Print error information if query fails $result = $conn_1->query("SELECT name FROM sqlite_master;"); if( $result instanceof PDOStatement ) show( $result->fetchAll() ); else show( $conn_1->errorInfo() ); // Remove database file unlink($path); Expected result: ---------------- Array ( [0] => Array ( [name] => test1 [0] => test1 ) ) Array ( [0] => Array ( [name] => test1 [0] => test1 ) [1] => Array ( [name] => test2 [0] => test2 ) ) Actual result: -------------- Array ( [0] => Array ( [name] => test1 [0] => test1 ) ) Array ( [0] => HY000 [1] => 17 [2] => database schema has changed ) ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=43942&edit=1