Edit report at https://bugs.php.net/bug.php?id=42322&edit=1
ID: 42322
Comment by: rosen at developer dot bg
Reported by: norbert at linuxnetworks dot de
Summary: pdo_mysql fetch() throws exception on INSERT
statements
Status: Open
Type: Feature/Change Request
Package: PDO related
Operating System: Linux Debian Testing
PHP Version: 5.2.3
Block user comment: N
Private report: N
New Comment:
The above test was performed on PHP 5.3.6 (cli)
uname -a: Linux gateway 2.6.9-78.0.22.ELsmp #1 SMP Thu Apr 30 19:17:40 EDT 2009
x86_64 x86_64 x86_64 GNU/Linux
Previous Comments:
------------------------------------------------------------------------
[2011-08-22 14:00:07] rosen at developer dot bg
I think that the issue here might be that PHP misinterprets what the mysql
client library returns. See this: http://bugs.mysql.com/bug.php?id=706 - as far
as I understand, the C function mysql_errno() is not supposed to return errors
relating to a fetch operation - which means that if mysql_query failed and then
mysql_fetch_row fails due to being at the end of the result set, mysql_errno
will still return the error from mysql_query. This would trick PDO into
thinking that it was the mysql_fetch_row that failed, thowing a PDOException
that refers to a completely different error (the one previously caused by
mysql_query).
Example code:
-------------
<?php
$dbh = new PDO('mysql:host=localhost', 'username', 'password');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$res = $dbh->query('SELECT 1'); // result has only one row
foreach ($res as $row) {
// perform another query that fails with an error, but catch the
exception
try {
$dbh->query('blah blah');
} catch (PDOException $e) {
echo "Exception caught.\n";
}
}
Expected output:
----------------
Exception caught.
Actual output:
--------------
Exception caught.
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]:
Syntax error or access violation: 1064 You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right
syntax to use near 'blah blah' at line 1' in ~/bug.php:9
Stack trace:
#0 ~/bug.php(9): unknown()
#1 {main}
thrown in ~/bug.php on line 9
------------------------------------------------------------------------
[2008-01-07 17:24:33] u...@php.net
How DB2 Express and Oracle Express deal with the example code...
DB2 Express -
Warning: PDOStatement::fetch(): SQLSTATE[24000]: Invalid cursor state: -99999
[IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000
(SQLFetchScroll[4294867297] at
/home/nixnutz/php5/ext/pdo_ibm/ibm_statement.c:867) in
/home/nixnutz/php5/ext/pdo_ibm/tests/bug_42322.php on line 19
Oracle Express -
Warning: PDOStatement::fetch(): SQLSTATE[HY000]: General error: 24374
OCIStmtFetch: ORA-24374: Definition nicht erfolgt vor Abruf oder Ausführen und
Abruf
(/home/nixnutz/php5/ext/pdo_oci/oci_statement.c:467) in
/home/nixnutz/php5/ext/pdo_oci/tests/bug_42322.php
------------------------------------------------------------------------
[2008-01-07 12:39:54] u...@php.net
Changing category to PDO.
I don't think this is a bug.
Its hard to say what is "correct" when it comes to PDO. PDO shows many
inconsistencies when comparing different drivers. With MySQL there is no result
set to fetch after executing an INSERT statement.
Fetch is one place where the "PDO specification" states explicitly that drivers
differ in their behaviour:
"The results of this fetch are driver dependent and the data is usually stored
in the driver_data member of the pdo_stmt_t object. The ori and offset
parameters are only meaningful if the statement represents a scrollable cursor.
This function returns 1 for success or 0 in the event of failure."
http://www.php.net/manual/en/internals2.pdo.implementing.php
As no standard behaviour has been defined, how can either MySQL or any other
driver be wrong? I guess this issue should be called a Feature request but not
a bug.
SQLite -> no exception
MySQL -> exception
Postgres -> exception
Ulf
------------------------------------------------------------------------
[2007-08-16 19:21:33] norbert at linuxnetworks dot de
Description:
------------
If using the MySQL PDO driver in PDO::ERRMODE_EXCEPTION mode, calling fetch()
after executing an INSERT/UPDATE/DELETE statement throws an exception. Instead,
it should return FALSE in this case like for an empty result set. Please
compare to the SQLite PDO driver which works correctly.
Reproduce code:
---------------
<?php
$db = new PDO( $dsn, $username, $password );
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
try
{
$db->exec("CREATE TABLE IF NOT EXISTS TestTable (id INT)");
$sql = 'INSERT INTO TestTable VALUES ( NULL )';
$query = $db->prepare( $sql );
$query->execute();
while( ( $row = $query->fetch( PDO::FETCH_ASSOC ) ) !== FALSE ) {}
$query->closeCursor();
}
catch( PDOException $pe ) {
echo 'Got PDOException: ' . $pe->getMessage();
}
?>
Expected result:
----------------
$query->fetch() should return FALSE, as it is not an error to call fetch() for
an INSERT statement, because it might be unknown what type of statement will be
executed (like in an SQL shell where users can enter their custom queries).
Actual result:
--------------
PDOException
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=42322&edit=1
