|
|
Posted by Jim Carlock on 11/12/05 07:00
I'm just going over this PHP stuff. I see one connection
item being used to access two database files? If this is
correct, try using two variables:
$db1
$db2
and setting each up to make their own appropriate connection.
Hope this helps.
--
Jim Carlock
Post replies to the newsgroup, thanks.
"James" <jprice@netspace.net.au> wrote:
Hey everyone, I have a really obscure but impassable problem with a
reasonably simple piece of php/PEAR DB/MySQL code
When calling several stored procs the first call succeeds, but
subsequent calls fail no matter which SP is called first it always
works, and the rest fail... with:
>>> [nativecode=2013 ** Lost connection to MySQL server during query]
I have a MySQL 5.05 database, I have PHP 4.3 and 5.0 (this error occurs
using both on several test servers) and I have PEAR::DB 1.80.
The problem I'm having is, I can't seem to call two stored procedures in
a row. The first call works perfectly, but the second always receives
the error [nativecode=2013 ** Lost connection to MySQL server during query]
This happens during the line: 379 of "pear/db/mysqli.php"
Which is in the function "simpleQuery($query)".....
if (!@mysqli_select_db($this->connection, $this->_db)) {
return $this->mysqliRaiseError(DB_ERROR_NODBSELECTED);
}
To reproduce ( and I can't seem to ever NOT reproduce this one...)
create two tables (with data) and two stored procs as follows:
===================================================================
CREATE TABLE `host_headers` (
`host` varchar(100) NOT NULL,
`site_id` int(11) NOT NULL,
PRIMARY KEY (`host`),
KEY `site_id` (`site_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `templates` (
`template_id` bigint(20) NOT NULL auto_increment,
`site_id` int(11) NOT NULL,
`template_name` varchar(28) NOT NULL,
`enabled` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`template_id`),
KEY `template_id` (`template_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `host_headers` (host, site_id)
VALUES ('localhost, 1);
INSERT INTO `templates` (site_id, template_name, enabled)
VALUES (1, 'Default', 1);
DELIMITER \\
CREATE PROCEDURE `sp_get_site_from_hostname`(IN pHostname varchar(255))
BEGIN
SELECT site_id FROM host_headers WHERE host like pHostname;
END\\
CREATE PROCEDURE `sp_get_template` (IN pSiteID int)
BEGIN
SELECT template_name FROM templates WHERE site_id = pSiteID;
END\\
DELIMITER ;
===================================================================
Then run the following PHP script. (required PHP, MySQL, PEAR DB)
===================================================================
<?php
include_once('DB.php'); //PEAR::DB
//REPLACE WITH YOUR SETTINGS BELOW
$dsn = "mysqli:username:password@localhost/database";
$options = array(
'debug' => 2,
'portability' => DB_PORTABILITY_ALL
);
$db =& DB::connect($dsn, $options);
//FIRST STORED PROC CALL SUCCEEDS
$hostname = 'localhost';
$sql = "call sp_get_site_from_hostname('$hostname')";
$rs =& $db->query($sql);
if ($row =& $rs->fetchrow(DB_FETCHMODE_ASSOC)) {
$siteid = $row['site_id'];
echo "Site ID: $siteid<br/>";
}
$rs->free();
//SECOND AND SUBSEQUENT STORED PROCS FAIL
//WITH THE ERROR [2013]
$sql = "call sp_get_template($siteid)";
$rs =& $db->query($sql); // <<< causes error //
if ($row =& $rs->fetchrow(DB_FETCHMODE_ASSOC)) {
$template = $row['template_name'];
echo "Template: $template<br/>":
}
$rs->free();
$db->disconnect();
?>
========================================================
Any suggestions are greatly appreciated...
Regards,
James.
Navigation:
[Reply to this message]
|