Description: ------------ I am having a problem on a few machines where execute() returns 0 when successfully executing a prepared statement whose 3rd argument was passed as MDB2_PREPARE_MANIP. I can see the data changing in the database, but always get 0 as number of rows returned. However, when directly working with PostgreSQL via PHP, or using MDB2 exec(), I get the proper number of affected rows. This is true on the following machine configurations: |My local dev machine at work---------------------- |-FreeBSD 6.1-RELEASE-p3 |-Apache 2.0.59 |-PHP 4.4.3 |---PEAR 1.4.11 |-----MDB2 2.2.2 |-------MDB2-Driver-pgsql 1.2.2 |-PostgreSQL 7.4.13_1 |--------------------------------------------------- |Same machine as above after upgrading PostgreSQL--- |-FreeBSD 6.1-RELEASE-p3 |-Apache 2.0.59 |-PHP 4.4.3 |---PEAR 1.4.11 |-----MDB2 2.2.2 |-------MDB2-Driver-pgsql 1.2.2 |-PostgreSQL 8.1.4_1 |--------------------------------------------------- |Remote dev machine at work------------------------- |-RedHat Linux Enterprise 4 |-Apache 2.0.52 |-PHP 4.3.9 |--Pear 1.4.11 |---MDB2 2.2.2 |----MDB2_Driver_pgsql 1.2.2 |-PostgreSQL 7.4.13 |--------------------------------------------------- |Co-worker's dev machine at work-------------------- |-FreeBSD 6.1 |-Apache 1.3.37 |-PHP 4.4.3 |--Pear 1.4.6 |---MDB2 2.2.2 |----MDB2_Driver_pgsql 1.2.2 |-PostgreSQL 7.4.13 |--------------------------------------------------- However, on some other machine configurations, I see it working just fine: |Friend's home Windows server----------------------- |-Windows XP Pro sp2 |-Apache 2.0.55 |-PHP 5.1.2 |---PEAR 1.4.11 |-----MDB2 2.2.2 |-------MDB2-Driver-pgsql 1.2.2 |-PostgreSQL 8.1.4_1 |--------------------------------------------------- |Same friend's MAC at home-------------------------- |-Apple OS X 10.4.7 ~ FreeBSD 5 |-Apache 1.3.33 |-PHP 4.4.4 |---PEAR 1.4.11 |-----MDB2 2.2.2 |-------MDB2-Driver-pgsql 1.2.2 |-PostgreSQL Server on the above windows box |--------------------------------------------------- |My home dev server--------------------------------- |-FreeBSD 6.1-RELEASE-p1 |-Apache 1.3.37 |-PHP 5.1.4 |---PEAR 1.4.11 |-----MDB2 2.2.2 |-------MDB2-Driver-pgsql 1.2.2 |-PostgreSQL 8.1.4_1 |--------------------------------------------------- I worked with IRC user lsmith on #pear who suggested I try to add this line: $mdb2->setOption('emulate_prepared',true); to my test code. This corrected the affectd rows from being misreported, though obviously doesn't answer the question as to why this setting is required in some places but not in others. lsmith asked me to open this bug report, so here it is. :) Test script: --------------- <?php //GET the PEAR file for MDB2 require 'MDB2.php'; //SET UP ERROR HANDLING function handle_pear_error ($error_obj) { die('<pre><b>PEAR-Error</b><br />'. $error_obj->getMessage().': '.$error_obj->getUserinfo(). '</pre>'); } PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, 'handle_pear_error'); //CONNECTION INFO $dsn = array( 'phptype' => 'pgsql', 'username' => 'postgres', 'password' => 'MyPassword', 'hostspec' => 'localhost', 'database' => 'testdb', ); //MAKE THE CONNECTION, INSTANCIATE THE OBJECT $mdb2 =& MDB2::factory($dsn); /* ADDED BY SUGGESTION OF LSMITH, COMMENTING OUT HERE BECAUSE THIS HIDES THE ACTUAL REPORTED PROBLEM //SETTING OPTION TO FIX execute() $mdb2->setOption('emulate_prepared',true); */ //QUERY PREPARE/EXECUTE STUFF $query = "INSERT INTO testtable (data1) VALUES(?)"; $stmt = $mdb2->prepare($query,array('text'),MDB2_PREPARE_MANIP); $data = array('hello world'); $aRows = $stmt->execute($data); //WHAT DID WE COME UP WITH? if(!PEAR::isError($aRows)){ echo "<p>".$aRows." rows affected</p>\n"; } //DID DB ROW COUNT INCREASE? $result = $mdb2->query("SELECT * FROM testtable"); echo "<p>".$result->numRows()." rows now in DB</p>\n"; //Try exec() $aRows = $mdb2->exec("INSERT INTO testtable (data1) VALUES('hello world')"); if(!PEAR::isError($aRows)){ echo "<p>".$aRows." rows affected</p>\n"; } //DID DB ROW COUNT INCREASE? $result = $mdb2->query("SELECT * FROM testtable"); echo "<p>".$result->numRows()." rows now in DB</p>\n"; ?> Expected result: ---------------- <p>1 rows affected</p> <p>21 rows now in DB</p> <p>1 rows affected</p> <p>22 rows now in DB</p> Actual result: -------------- <p>0 rows affected</p> <p>21 rows now in DB</p> <p>1 rows affected</p> <p>22 rows now in DB</p>

I completely rebuilt the first box listed in the list of boxes having the problem. It is still having the problem. Its new specs are as follows: |My local dev machine at work---------------------- |-CentOS 4.1 |-Apache 2.0.52 |-PHP 4.3.9 |---PEAR 1.4.11 |-----MDB2 2.2.2 |-------MDB2-Driver-pgsql 1.2.2 |-PostgreSQL server 7.4.13-2.RHEL4.1 |-PostgreSQL libs 7.4.13-2.RHEL4.1 |---------------------------------------------------

Hello Lorenzo, I have modified the test script to add your debug suggestions and smooth out the output. It now looks like so: ------------------------------------- <html> <head> <title>Testing PEAR MDB2 Bug #8657</title> <style type="text/css"> b{color: #f00;} </style> </head> <body> <?php /* Uses following DB schema: CREATE DATABASE testdb; CREATE TABLE testtable ( data1 text ) WITH OIDS; */ //CONNECTION INFO $dsn = array( 'phptype' => 'pgsql', 'username' => 'MyPostgresUser', 'password' => 'MyPostgresPassword', 'hostspec' => 'localhost', 'database' => 'testdb', ); //GET the PEAR file for MDB2 require('MDB2.php'); //SET UP ERROR HANDLING function handle_pear_error ($error_obj){ die('<h1>PEAR-Error</h1><pre>'.$error_obj->getMessage().' : '.$error_obj->getUserinfo()."</pre>\n"); } PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, 'handle_pear_error'); //MAKE THE CONNECTION, INSTANCIATE THE OBJECT $mdb2 =& MDB2::factory($dsn); //BEEF UP DEBUGGING $mdb2->setOption('debug',2); //ADDED BY SUGGESTION OF LSMITH //UNCOMMENT FOLLOWING LINE TO USE EMULATED PREPARES AND FIX THE RETURN ISSUE IN execute()--THIS ONLY FIXES A SYMPTOM, THOUGH, NOT THE PROBLEM //$mdb2->setOption('emulate_prepared',true); //COUNT NUMBER OF ROWS BEFORE DOING INSERT $result = $mdb2->query('SELECT * FROM testtable'); echo '<p><b>'.$result->numRows()."</b> rows in DB before any MANIP queries</p>\n"; //QUERY PREPARE/EXECUTE STUFF $query = 'INSERT INTO testtable (data1) VALUES(?)'; $stmt = $mdb2->prepare($query,array('text'),MDB2_PREPARE_MANIP); $data = array('hello world'); $aRows = $stmt->execute($data); //WHAT DID WE COME UP WITH? if(!PEAR::isError($aRows)){ echo '<p>Ran execute(), it returned <b>'.$aRows."</b> as number of rows affected</p>\n"; } //DID DB ROW COUNT INCREASE? $result = $mdb2->query("SELECT * FROM testtable"); echo '<p><b>'.$result->numRows()."</b> rows in DB after running execute()</p>\n"; //Try exec() $aRows = $mdb2->exec("INSERT INTO testtable (data1) VALUES('hello world')"); if(!PEAR::isError($aRows)){ echo '<p>Ran exec(), it returned <b>'.$aRows."</b> as number of rows affected</p>\n"; } //DID DB ROW COUNT INCREASE? $result = $mdb2->query("SELECT * FROM testtable"); echo '<p><b>'.$result->numRows()."</b> rows in DB after running exec()</p>\n"; //DUMP DEBUG INFO echo '<h1>MDB2 Debug Output:</h1><pre>';var_dump($mdb2->getDebugOutput());echo"</pre>\n"; ?> </body> </html> ------------------------------------- I also ran this on three different machines. Two of them continued failing, the other worked fine. One of the two failed machines is the same as one that failed before, with some software updates. The other that failed is totally new hardware built from the ground up. The failed machines start working fine as soon as I turn on emulated prepares. The one that passed without prepare emulation was on hardware that passed before but has been rebuilt from the ground up on a new OS and everything. Before I list the machine specs, here is what the failed output looks like: ------------------------------------- <html> <head> <title>Testing PEAR MDB2 Bug #8657</title> <style type="text/css"> b{color: #f00;} </style> </head> <body> <p><b>12</b> rows in DB before any MANIP queries</p> <p>Ran execute(), it returned <b>0</b> as number of rows affected</p> <p><b>13</b> rows in DB after running execute()</p> <p>Ran exec(), it returned <b>1</b> as number of rows affected</p> <p><b>14</b> rows in DB after running exec()</p> <h1>MDB2 Debug Output:</h1><pre>string(554) "query(1): SELECT * FROM testtable prepare(1): INSERT INTO testtable (data1) VALUES(?) query(1): PREPARE mdb2_statement_pgsql_b95679ce955266cd7767f664a05b0c20 (text) AS INSERT INTO testtable (data1) VALUES($1) execute(1): PREPARE mdb2_statement_pgsql_b95679ce955266cd7767f664a05b0c20 (text) AS INSERT INTO testtable (data1) VALUES($1) query(1): EXECUTE mdb2_statement_pgsql_b95679ce955266cd7767f664a05b0c20 ('hello world') query(1): SELECT * FROM testtable query(1): INSERT INTO testtable (data1) VALUES('hello world') query(1): SELECT * FROM testtable " </pre> </body> </html> ------------------------------------- The machine specs are as follows: |FAILED: Remote dev machine at work-Same Hardware as prev tests, some software updates |-RedHat Linux Enterprise 4 |-Apache 2.0.52 |-PHP 4.3.9 |--Pear 1.4.11 |---MDB2 2.3.0 |----MDB2_Driver_pgsql 1.3.0 |-PostgreSQL 7.4.13 |--------------------------------------------------- |FAILED: Local dev machine at work-brand new hardware and software since last tests |-CentOS 4.4 |-Apache 2.0.52 |-PHP 4.3.9 |--Pear 1.4.11 |---MDB2 2.3.0 |----MDB2_Driver_pgsql 1.3.0 |-PostgreSQL 7.4.13 |--------------------------------------------------- |PASSED: machine at home-same hardware, all new software build |-Fedora Core 6 |-Apache 2.2.3 |-PHP 5.1.6 |--Pear 1.4.11 |---MDB2 2.3.0 |----MDB2_Driver_pgsql 1.3.0 |-PostgreSQL 8.1.4 |--------------------------------------------------- Note that while the two that failed are on PostgreSQL 7.4, it has passed on 7.4 before (see original bug report). Thanks, Jim

I have been doing some testing on this "bug", and can confirm that of the two versions of PostgreSQL I have installed (7.4 and 8.1), it only seems to be an issue with PostgreSQL 7.4. This appears to be entirely separate from the MDB2_Driver_pgsql class; the PHP pg_affected_rows built in function simply does not return the affected rows when using a prepared statement via the SQL "PREPARE" statement in PostgreSQL 7.4. HTH.

Thanks all for the extra analysis and comments. Looking into the possible PHP problem.