如何连续执行多个程序? [英] How can I execute multiple procedures continuously?
问题描述
这是我的代码:
$query = "CALL user_top_categories_score(?, 'ALL', 0, 1)";
$sth = $this->dbh->prepare($query);
$sth->execute([$user_id]);
$category = $sth->fetchAll(PDO::FETCH_ASSOC);
$query = "CALL user_top_tags_score(?, 'ALL', 0, 3)";
$sth = $this->dbh->prepare($query);
$sth->execute([$user_id]);
$tags = $sth->fetchAll(PDO::FETCH_ASSOC);
它抛出此错误:
致命错误:未捕获的PDOException:SQLSTATE [HY000]:常规错误:2014当其他未缓冲的查询处于活动状态时，无法执行查询.考虑使用PDOStatement :: fetchAll().另外，如果您的代码只针对mysql运行，则可以通过设置PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY属性来启用查询缓冲.在C:\ xampp \ htdocs \ myweb \ others \ user.php:71中堆栈跟踪:#0 C:\ xampp \ htdocs \ myweb \ others \ user.php(71):PDO-> prepare('CALL user_top_t. .')#1 C:\ xampp \ htdocs \ myweb \ application \ other.php(24):用户->索引()#2 C:\ xampp \ htdocs \ myweb \ index.php(152):require_once(' C:\ xampp \ htdocs ...')#3 {main}在第71行的C:\ xampp \ htdocs \ myweb \ others \ user.php中抛出
Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. in C:\xampp\htdocs\myweb\others\user.php:71 Stack trace: #0 C:\xampp\htdocs\myweb\others\user.php(71): PDO->prepare('CALL user_top_t...') #1 C:\xampp\htdocs\myweb\application\other.php(24): user->index() #2 C:\xampp\htdocs\myweb\index.php(152): require_once('C:\xampp\htdocs...') #3 {main} thrown in C:\xampp\htdocs\myweb\others\user.php on line 71
我还根据此解决方案在fetchAll()之后使用了closeCursor().但是可悲的是它引发了一个新的错误:
Also I've used closeCursor() right after fetchAll(), based on this solution. But sadly it throws a new error:
警告:数据包乱序.预期收到1条消息.第72行的C:\ xampp \ htdocs \ myweb \ others \ user.php中的数据包大小= 7
Warning: Packets out of order. Expected 1 received 9. Packet size=7 in C:\xampp\htdocs\myweb\others\user.php on line 72
警告:PDO :: prepare():MySQL服务器已在第72行的C:\ xampp \ htdocs \ myweb \ others \ user.php中消失
Warning: PDO::prepare(): MySQL server has gone away in C:\xampp\htdocs\myweb\others\user.php on line 72
致命错误:未捕获的PDOException:SQLSTATE [HY000]:常规错误:2006 MySQL服务器已在C:\ xampp \ htdocs \ myweb \ others \ user.php:72堆栈中消失:#0 C:\ xampp \ htdocs \ myweb \ others \ user.php(72):PDO-> prepare('CALL user_top_t ...')#1 C:\ xampp \ htdocs \ myweb \ application \ other.php(24):user-> index ()#2 C:\ xampp \ htdocs \ myweb \ index.php(152):require_once('C:\ xampp \ htdocs ...')#3 {main}抛出C:\ xampp \ htdocs \ myweb \第72行的others \ user.php
Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in C:\xampp\htdocs\myweb\others\user.php:72 Stack trace: #0 C:\xampp\htdocs\myweb\others\user.php(72): PDO->prepare('CALL user_top_t...') #1 C:\xampp\htdocs\myweb\application\other.php(24): user->index() #2 C:\xampp\htdocs\myweb\index.php(152): require_once('C:\xampp\htdocs...') #3 {main} thrown in C:\xampp\htdocs\myweb\others\user.php on line 72
任何想法我该如何解决该问题?
Any idea how can I fix the problem?
注1:上面的每个查询都是单独工作的.我的意思是，当我调用单个过程时，它也可以正常工作.
Noted1: Each of queries above work separately. I mean, when I call a single procedure, it works as well.
注2::每个过程都返回一个结果集.我的意思是这些程序中有一个SELECT语句.
Noted2: Each procedure returns a result set. I mean there is a SELECT statement in those procedures.
推荐答案
您的过程中可能只有一个SELECT，但是API不知道这一点.它必须假定您可能从过程中返回了多个结果集，因此仅fetchAll()不会关闭游标.
You may have just one SELECT in your procedure, but the API doesn't know that. It has to assume you might have multiple result sets returned from your procedure, therefore fetchAll() alone doesn't close the cursor.
您必须继续调用nextRowset()，直到它返回错误的结果，表明所有结果已从此语句返回.
You have to keep calling nextRowset() until it has returned a false result, indicating that all results have been returned from this statement.
See also my answer to How can I use a stored procedure in a MySql database with Zend Framework? (I answered this in 2009!).
这篇关于如何连续执行多个程序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
