MySQL Commands Do Not Work Through PEAR MDB2
Posted: Fri May 14, 2010 4:31 pm
I have a PHP script which contains the following code using PEAR MDB2:
Where the two functions are defined as follows to facilitate migration between PEAR DB and PEAR MDB2:
My PHP code creates a log file of every SQL statement issued, and the return codes from each, although for clarity I have removed the logging statements from the above examples. When I run this code the INSERT reports that 0 lines are inserted even though no error is reported, and although the SELECT returns 1 row, the value assigned to $idlr2 is zero. However if I cut and paste the individual commands from the log file into the MySQL client utility, the commands work as I expect them to. So I have no idea what I have to do to this code to get it to update the database in the way that I expect. The following is a portion of the log file.
When I issue these specific commands manually through the MySQL client tool the output is:
The two tables that this code works with have the following definitions:
Code: Select all
$insloc = "INSERT INTO tblLR SET " .
"location = " . $connection->quote($mainloc) . ", " .
"sortedlocation = " . $connection->quote($mainloc) . ", " .
"shortname = " . $connection->quote($mainloc) .
", used = 1;\n";
$insres = doExec($connection,
$update);
$res2 = doQuery($connection,
"SELECT LAST_INSERT_ID() AS idlr;");
$lr2 = $res2->fetchRow();
$idlr2 = $lr2['idlr'];
$update = 'UPDATE tblER SET Description=' .
$connection->quote($occupation) .
', IDLREvent=' . $idlr2 .
' WHERE IDLREvent=' . $idlr1 . ';';
Code: Select all
/**
* doQuery
*
* Function to issue a database query with common error recovery.
* It supports both Pear::DB and Pear::MDB2 depending upon the
* type of the connection.
*
* Input:
* $connection connection to database server
* $cmd SQL command to issue
*
* Returns result of SQL command.
**/
function doQuery($connection, $cmd)
{
$MDB2 = substr(get_class($connection), 0, 2) == "MD";
$result = $connection->query($cmd);
if (($MDB2 && PEAR::isError($result)) ||
(!$MDB2 && DB::isError($result)))
{ // error issuing query
// diagnostic printout
print("<p>\n");
print($cmd);
print("\n</p>\n<p class='message'>");
die($result->getMessage());
} // error issuing query
return $result;
} // doQuery
/**
* doExec
*
* Function to issue a database update with common error recovery.
* If invoked with a Pear::DB connection it invokes $connection->query.
* If invoked with a Pear::MDB2 connection it invokes $connection->exec.
*
* Input:
* $connection connection to database server
* $cmd SQL command to issue
*
* Returns: number of affected rows
**/
function doExec($connection, $cmd)
{
$MDB2 = substr(get_class($connection), 0, 2) == "MD";
if ($MDB2)
{ // MDB2 connection
$result = $connection->exec($cmd);
if (PEAR::isError($result))
{ // error issuing query
// diagnostic printout
print("<p>\n");
print($cmd);
print("\n</p>\n<p class='message'>");
die($result->getMessage());
} // error issuing query
return $result;
} // MDB2 connection
else
{ // DB connection
$result = $connection->query($cmd);
if (DB::isError($result))
{ // error issuing query
// diagnostic printout
print("<p>\n");
print($cmd);
print("\n</p>\n<p class='message'>");
die($result->getMessage());
} // error issuing query
return $result;
} // DB connection
} // doExec
Code: Select all
INSERT INTO tblLR SET location = 'Lot 23 Con 1 Ner, Adelaide, Middlesex, ON, CA', sortedlocation = 'Lot 23 Con 1 Ner, Adelaide, Middlesex, ON, CA', shortname = 'Lot 23 Con 1 Ner, Adelaide, Middlesex, ON, CA', used = 1;
0 Lines Updated
SELECT LAST_INSERT_ID() AS idlr;
1 Rows Returned
UPDATE tblER SET Description='Farm Laborer With Father', IDLREvent=0 WHERE IDLREvent=12434;
0 Lines Updated
Code: Select all
mysql> INSERT INTO tblLR SET location = 'Lot 23 Con 1 Ner, Adelaide, Middlesex, ON, CA', sortedlocation = 'Lot 23 Con 1 Ner, Adelaide, Middlesex, ON, CA', shortname = 'Lot 23 Con 1 Ner, Adelaide, Middlesex, ON, CA', used = 1;
Query OK, 1 row affected (3.19 sec)
mysql> SELECT LAST_INSERT_ID() AS idlr;
+-------+
| idlr |
+-------+
| 20943 |
+-------+
1 row in set (0.05 sec)
Code: Select all
CREATE TABLE `tblER` (
`IDER` int(10) NOT NULL auto_increment,
`IDIR` int(10) default NULL,
`IDET` int(10) default NULL,
`Order` smallint(5) default NULL,
`EventD` varchar(100) default NULL,
`EventSD` int(10) default NULL,
`IDLREvent` int(10) default NULL,
`Desc` longtext,
`GEDTag` varchar(30) default NULL,
`EventExclude` tinyint(3) unsigned default NULL COMMENT 'Don''t ever add Exclude or SrcExclude as fields. Some older files had these already.',
`IDType` tinyint(3) unsigned default NULL,
`IDAR` int(10) default NULL,
`Description` varchar(255) default NULL,
`SentenceOverride` varchar(255) default NULL,
`qsTag` tinyint(3) unsigned default NULL,
`RGExclude` tinyint(3) unsigned default NULL,
PRIMARY KEY (`IDER`),
KEY `IDET` (`IDET`),
KEY `IDIREventSD` (`IDType`,`IDIR`,`EventSD`),
KEY `IDIROrder` (`IDType`,`IDIR`,`Order`),
KEY `qsTag` (`qsTag`)
) ENGINE=MyISAM AUTO_INCREMENT=31245 DEFAULT CHARSET=latin1;
CREATE TABLE `tblLR` (
`IDLR` int(10) NOT NULL auto_increment,
`FSPlaceID` varchar(255) default NULL,
`Preposition` varchar(120) default NULL,
`Location` varchar(255) default NULL,
`SortedLocation` varchar(255) default NULL,
`ShortName` varchar(255) default NULL,
`Tag1` tinyint(3) unsigned default NULL,
`Used` tinyint(3) unsigned default NULL,
`Notes` longtext,
`Verified` tinyint(3) unsigned default NULL,
`Latitude` double(53,0) default NULL,
`Longitude` double(53,0) default NULL,
`FSResolved` tinyint(3) unsigned default NULL,
`VEResolved` tinyint(3) unsigned default NULL,
`qsTag` tinyint(3) unsigned default NULL,
PRIMARY KEY (`IDLR`),
KEY `Latitude` (`Latitude`),
KEY `Location` (`Location`),
KEY `Longitude` (`Longitude`),
KEY `qsTag` (`qsTag`),
KEY `SortedLocation` (`SortedLocation`)
) ENGINE=MyISAM AUTO_INCREMENT=20944 DEFAULT CHARSET=latin1;