php 调存储过程没有java和.NET方便,希望下一个版本能好点。
我们来看一下面的例子
数据库代码
CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(50),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 AUTO_INCREMENT=1 ;
insert into `test` (title) values ('好人');
--存储过程
DELIMITER $$;
DROP PROCEDURE IF EXISTS `sp_test`$$
CREATE PROCEDURE `sp_test` ()
BEGIN
select * from test;
END$$
DELIMITER ;$$
php 调用代码
- $db=new mysqli("localhost","root","root","manage");
- if (mysqli_connect_errno())
- {
- printf("Connect failed: %s/n", mysqli_connect_error());
- exit();
- }
- $db->query("set names 'gb2312'");
- $rs = array();
- if ($result = $db->query("call sp_test()"))
- {
- while ($row = $result->fetch_array(MYSQLI_ASSOC))
- {
- array_push($rs,$row);
- }
- $result->free();
- }
- var_dump($rs);
运行上面的代码你会很高兴成功了!!!,可是没有想到后面出了个大问题,来看看下面吧
- $db=new mysqli("localhost","root","root","manage");
- if (mysqli_connect_errno())
- {
- printf("Connect failed: %s/n", mysqli_connect_error());
- exit();
- }
- $db->query("set names 'gb2312'");
- $rs = array();
- if ($result = $db->query("call sp_test()"))
- {
- while ($row = $result->fetch_array(MYSQLI_ASSOC))
- {
- array_push($rs,$row);
- }
- $result->free();
- }
- var_dump($rs);
- $rss = array();
- if ($result = $db->query("select * from test"))
- {
- while ($row = $result->fetch_array(MYSQLI_ASSOC))
- {
- array_push($rss,$row);
- }
- $result->free();
- }
- var_dump($rss);
运行上面的代码问题就来了,$rss数组是空的(在有的情况下还是出现“Call to a member function fetch_array() on a non-object错误”),为什么会样呢? 我们把代码改改看
- $db=new mysqli("localhost","root","root","manage");
- if (mysqli_connect_errno())
- {
- printf("Connect failed: %s/n", mysqli_connect_error());
- exit();
- }
- $db->query("set names 'gb2312'");
- $rss = array();
- if ($result = $db->query("select * from test"))
- {
- while ($row = $result->fetch_array(MYSQLI_ASSOC))
- {
- array_push($rss,$row);
- }
- $result->free();
- }
- var_dump($rss);
- $rs = array();
- if ($result = $db->query("call sp_test()"))
- {
- while ($row = $result->fetch_array(MYSQLI_ASSOC))
- {
- array_push($rs,$row);
- }
- $result->free();
- }
- var_dump($rs);
运行上面的我们发现没有问题,这为什么呢?在同一个链接执行存储过程后不能执行其它的sql语句,我们再把代码改改看
- $db=new mysqli("localhost","root","root","manage");
- if (mysqli_connect_errno())
- {
- printf("Connect failed: %s/n", mysqli_connect_error());
- exit();
- }
- $db->query("set names 'gb2312'");
- $rs = array();
- $result = $db->real_query("call sp_test()");
- do
- {
- if($result = $db->store_result())
- {
- while ($row = $result->fetch_array(MYSQLI_ASSOC))
- {
- array_push($rs,$row);
- }
- $result->free();
- }
- }while($db->next_result());
- var_dump($rs);
- $rss = array();
- if ($result = $db->query("select * from test"))
- {
- while ($row = $result->fetch_array(MYSQLI_ASSOC))
- {
- array_push($rss,$row);
- }
- $result->free();
- }
- var_dump($rss);
运行上面的我们发现没有问题,什么原因呢?大家查一下PHP手册吧,看看store_result、next_result就明白。
提示
这是运用mysqli链接数据库所以会出现
Call to a member function fetch_array() on a non-object
如果用mysql_pconnect链接数据库就会出现
Commands out of sync; you can't run this command now
这是因为当存储过程调用时,其指针没有释放,导致占用了资源。只有清空就可以了。