MySQL Commands Do Not Work Through PEAR MDB2

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jcobban
Forum Commoner
Posts: 41
Joined: Mon Mar 08, 2010 7:40 am

MySQL Commands Do Not Work Through PEAR MDB2

Post by jcobban »

I have a PHP script which contains the following code using PEAR MDB2:

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 . ';';
	
Where the two functions are defined as follows to facilitate migration between PEAR DB and PEAR MDB2:

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
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.

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
When I issue these specific commands manually through the MySQL client tool the output is:

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)
The two tables that this code works with have the following definitions:

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;
jcobban
Forum Commoner
Posts: 41
Joined: Mon Mar 08, 2010 7:40 am

Re: MySQL Commands Do Not Work Through PEAR MDB2

Post by jcobban »

I really need a resolution to this issue. I have tried changing the exact nature of the Insert statement, but that makes things worse!

Code: Select all

$insloc	= "INSERT INTO $tbl (FSPlaceId, Location, Used, SortedLocation, ShortName, Preposition, Notes) " .
			"VALUES ( " .
			$connection->quote($mainloc) . ", " .
			$connection->quote($mainloc) . ", " .
			"1, " .
			$connection->quote($mainloc) . ", " .
			$connection->quote($mainloc) . ", " .
			"'at', " .
			"'');\n"; 
	print "<p>$insloc</p>\n";
		    fwrite($file, $insloc);
		    $insres	= doExec($connection,
					 $update);
	print "<p>$insres Lines Inserted</p>\n";
	
results in:
INSERT INTO tblLR (FSPlaceId, Location, Used, SortedLocation, ShortName, Preposition, Notes) VALUES ( 'Lancer, SK, CA', 'Lancer, SK, CA', 1, 'Lancer, SK, CA', 'Lancer, SK, CA', 'at', '');

MDB2 Error: unknown error

Again if I cut and paste the command into the mysql client program it works perfectly.

Furthermore if I hack the code to use Pear::DB instead of Pear::MDB2 it works.
jcobban
Forum Commoner
Posts: 41
Joined: Mon Mar 08, 2010 7:40 am

Re: MySQL Commands Do Not Work Through PEAR MDB2

Post by jcobban »

jcobban wrote:I really need a resolution to this issue. I have tried changing the exact nature of the Insert statement, but that makes things worse!

Again if I cut and paste the command into the mysql client program it works perfectly.

Furthermore if I hack the code to use Pear::DB instead of Pear::MDB2 it works.
Actually I spoke to soon. Pear::DB does not give an error on the INSERT, and returns a count of 1 line updated, but the database is not updated. The following is from my log file:

Code: Select all

SELECT IDLR, Location FROM tblLR WHERE Location = '15 Harris St., Delaware Village, Delaware, Middlesex, ON, CA';
0 Rows Returned
INSERT INTO tblLR (FSPlaceId, Location, Used, SortedLocation, ShortName, Preposition, Notes) VALUES ( '15 Harris St., Delaware Village, Delaware, Middlesex, ON, CA', '15 Harris St., Delaware Village, Delaware, Middlesex, ON, CA', 1, '15 Harris St., Delaware Village, Delaware, Middlesex, ON, CA', '15 Harris St., Delaware Village, Delaware, Middlesex, ON, CA', 'at', '');
1 Lines Updated
SELECT IDLR, Location FROM tblLR WHERE Location = '15 Harris St., Delaware Village, Delaware, Middlesex, ON, CA';
0 Rows Returned
It would be highly desirable for me to be able to get this work. My only alternative is writing a program in Java.
Post Reply