|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2006-11-20 23:56 UTC] php dot bugreport dot tarpit at spamgourmet dot com
Description:
------------
When retrieving error information with PDO::errorInfo() after an SQL-insert causing a constraint violation results are inconsistent depending on the form of the statement: PDO::exec() or PDOStatement::execute().
Reproduce code:
---------------
SQL to create database followed by sample PHP:
CREATE TABLE TEST ( Key Integer Primary Key, Text Char(20) Not Null );
CREATE UNIQUE INDEX iTEST On TEST ( Text );
<?php
$dbh_test=New PDO("sqlite:C:/Data/PHP5TEST.DB");
$dbh_test->SetAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
$dbh_test->Exec("Insert Into TEST (Key, Text) Values (1, 'Test1')");
$pdo_error=$dbh_test->ErrorInfo();
Print "\n$pdo_error[0] $pdo_error[1] $pdo_error[2] :: 1, 'Test1'\n";
$sql="Insert Into TEST (Key, Text) Values (:key, :text)";
$sq_insert=$dbh_test->Prepare($sql);
$sq_insert->BindParam(':key', $key); $sq_insert->BindParam(':text', $text);
$key=1; $text="Test1"; $sq_insert->Execute();
$pdo_error=$sq_insert->ErrorInfo();
Print "\n$pdo_error[0] $pdo_error[1] $pdo_error[2] :: $key, $text\n";
Exit(0);
?>
Expected result:
----------------
23000 19 PRIMARY KEY must be unique :: 1, 'Test1'
23000 19 PRIMARY KEY must be unique :: 1, 'Test1'
Actual result:
--------------
PHP 5.1.6:
23000 19 PRIMARY KEY must be unique :: 1, 'Test1'
HY000 1 PRIMARY KEY must be unique :: 1, Test1
PHP 5.2.0:
23000 19 PRIMARY KEY must be unique :: 1, 'Test1'
HY000 1 SQL logic error or missing database :: 1, Test1
5CVS-2006-11-18:
23000 19 PRIMARY KEY must be unique :: 1, 'Test1'
HY000 1 SQL logic error or missing database :: 1, Test1
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||
Copyright © 2001-2025 The PHP GroupAll rights reserved. |
Last updated: Tue Mar 04 06:01:27 2025 UTC |
I tried the following code using SQLite 3.3.7: #include <stdio.h> #include <process.h> #include <string.h> #include <sqlite3.h> int main() { sqlite3 *db; sqlite3_stmt *stm; int rc; char *sqli, *sqlp, *zErr; const char *tail; rc = sqlite3_open("bugtest.db", &db); if(rc) { fprintf(stdout, "Can't open database: %s\n", sqlite3_errmsg(db)); exit(255); } sqli = "Insert Into TEST (Key, Text) Values (1, 'Test1')"; sqlp = "Insert Into TEST (Key, Text) Values (?, ?)"; rc = sqlite3_exec(db, sqli, NULL, NULL, &zErr); if(rc) { fprintf(stdout, "exec(): %i - %s\n", rc, zErr); } rc = sqlite3_prepare(db, sqlp, strlen(sqlp), &stm, &tail); if(rc) { fprintf(stdout, "prepare(): %i - %s\n", rc, zErr); } rc = sqlite3_bind_int(stm, 1, 1); if(rc) { fprintf(stdout, "bind_int(): %i - %s\n", rc, zErr); } rc = sqlite3_bind_text(stm, 2, "Test1", 5, SQLITE_STATIC); if(rc) { fprintf(stdout, "bind_text(): %i - %s\n", rc, zErr); } rc = sqlite3_step(stm); if(rc) { fprintf(stdout, "step(): %i - %s\n", rc, zErr); } sqlite3_finalize(stm); sqlite3_close(db); return 0; } With result: exec(): 19 - PRIMARY KEY must be unique step(): 1 - PRIMARY KEY must be unique Yes, sqlite3_step() does return the wrong errorcode - that is a bug in SQLite. But it still returns the correct message, which appears to get lost somewhere.....There are things you should NOT do after eight hours debugging COBOL+CICS+DB2+MQ..... Obviously compacting a C-program for a bug-report is one of these! Sorry, no other excuse for this stupid mistake. So here is the original code (with the addition of sqlite3_reset()): #include <stdio.h> #include <process.h> #include <string.h> #include <sqlite3.h> int main(int argc, char **argv) { sqlite3 *db; sqlite3_stmt *stm; int rc; char *sqli; char *sqlp; char *zErr; const char *tail; fprintf(stdout, "'%s' using SQLite %s (compiled) - %s (DLL)\n\n", argv[0], SQLITE_VERSION, sqlite3_libversion() ); rc = sqlite3_open("bugtest.db", &db); if (rc) { fprintf(stdout, "Can't open database: %s\n", sqlite3_errmsg(db)); exit(255); } sqli = "Insert Into TEST (Key, Text) Values (1, 'Test1')"; sqlp = "Insert Into TEST (Key, Text) Values (?, ?)"; rc = sqlite3_exec(db, sqli, NULL, NULL, &zErr); if (rc) { fprintf(stdout, "sqlite3_exec(): %i - %s\n", rc, sqlite3_errmsg(db)); } rc = sqlite3_prepare(db, sqlp, strlen(sqlp), &stm, &tail); if (rc) { fprintf(stdout, "sqlite3_prepare(): %i - %s\n", rc, sqlite3_errmsg(db)); } rc = sqlite3_bind_int(stm, 1, 1); if (rc) { fprintf(stdout, "sqlite3_bind_int(): %i - %s\n", rc, sqlite3_errmsg(db)); } rc = sqlite3_bind_text(stm, 2, "Test1", 5, SQLITE_STATIC); if (rc) { fprintf(stdout, "sqlite3_bind_text(): %i - %s\n", rc, sqlite3_errmsg(db)); } rc = sqlite3_step(stm); if (rc) { fprintf(stdout, "sqlite3_step(): %i - %s\n", rc, sqlite3_errmsg(db)); } rc = sqlite3_reset(stm); if (rc) { fprintf(stdout, "sqlite3_reset(): %i - %s\n", rc, sqlite3_errmsg(db)); } sqlite3_finalize(stm); sqlite3_close(db); return 0; } It produced the following output: 'D:\BCC55\BUGTEST.EXE' using SQLite 3.3.7 (compiled) - 3.3.7 (DLL) sqlite3_exec(): 19 - PRIMARY KEY must be unique sqlite3_step(): 1 - SQL logic error or missing database sqlite3_reset(): 19 - PRIMARY KEY must be unique As you commented correctly, the error code is fetched with sqlite3_errmsg(). After a failed sqlite3_step() the error code returned by sqlite3_errmsg() is wrong; only after a sqlite3_reset() the correct error code can be fetched with sqlite3_errmsg(). Tonight I found this link explaining this behaviour: http://www.sqlite.org/cvstrac/tktview?tn=1965. Calling sqlite3_reset() after a failed sqlite3_step() should then fix this problem.