php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #39564 PDO::errorInfo() returns inconsistent information
Submitted: 2006-11-20 23:56 UTC Modified: 2006-11-27 12:14 UTC
From: php dot bugreport dot tarpit at spamgourmet dot com Assigned:
Status: Closed Package: PDO related
PHP Version: 5.2.0 OS: Windows 98SE and XP/SP1
Private report: No CVE-ID:
 [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



Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-11-21 09:29 UTC] tony2001@php.net
SQLite inconsistencies should be reported to SQLite developers.
 [2006-11-21 22:04 UTC] php dot bugreport dot tarpit at spamgourmet dot com
Original category was "SQLite related" - because that is the only part of PDO I use. Since the error is obviously not in SQLite (which reports the correct errors), but in PDO (which reports different errors depending on the use of PDO::exec() or PDOStatement::execute() functions), category "PDO related" would be better.
 [2006-11-21 22:10 UTC] tony2001@php.net
These two methods use different SQLite functions to execute queries, which report different errors. And that is the reason I can hardly imagine why it's PDO fault.
 [2006-11-23 23:11 UTC] php dot bugreport dot tarpit at spamgourmet dot com
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.....
 [2006-11-23 23:33 UTC] tony2001@php.net
First of all, you're using zErr returned by sqlite3_exec(), of course it's right.
Second, sqlite3_step() doesn't return error code (as sqlite3_exec() does), it returns SQLITE_ERROR instead and the error code is fetched using sqlite3_errcode(db).
 [2006-11-24 23:05 UTC] php dot bugreport dot tarpit at spamgourmet dot com
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.
 [2006-11-27 12:14 UTC] tony2001@php.net
This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.


 
PHP Copyright © 2001-2014 The PHP Group
All rights reserved.
Last updated: Wed Apr 23 07:02:14 2014 UTC