Yii - 创建临时表并在下一个查询中使用它会产生一般错误:2014 无法执行查询，而其他无缓冲查询处于活动状态

2022-01-04 00:00:00 mysql yii

我正在创建临时表以在第一次查询中保存一些日期.在第二个查询中，我尝试加入这些日期……然后出现以下错误:

I am creating temporary table to hold some dates in first query. And in second query I try to join with those dates... and than i get following error:

SQLSTATE[HY000]:一般错误:2014 无法执行查询，而其他无缓冲查询处于活动状态.考虑使用 PDOStatement::fetchAll().或者，如果您的代码只针对 mysql 运行，您可以通过设置 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 属性来启用查询缓冲.

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

第一次查询:

    $query = "DROP TABLE if exists TempDatesTable;";
    $query .= "CREATE TEMPORARY TABLE TempDatesTable ( days char(20) ) TYPE=HEAP  DEFAULT CHARSET=utf8;";

    foreach ($allDatesInsideInterval as $date) {
        $query .= "INSERT INTO TempDatesTable VALUES( '$date' );";
    }

    Yii::app()->db->createCommand($query)->execute();

第二次查询

$command = Yii::app()->db->createCommand()
                    ->select('allDays.days as periodDay, numberOfSentRequests, numberOfReceivedRequests, numOfLogins, numOfProfilesViewed')
                    ->from("(" . $commandDates->getText() . ") allDays")
                    ->leftJoin("(" . $commandProfileViewed->getText() . ") accessLog", 'allDays.days = accessLog.days')....

当我尝试运行第二个查询时:

When I try to run second query:

return new CSqlDataProvider($command->getText(), array(
        'totalItemCount' => count($allDatesInsideInterval),
        'pagination' => array(
            'pageSize' => self::PAGE_SIZE
        ),
        ...

我已经看到我需要做 fetchAll();和 closeCursor();...但是如何在 Yii 中做到这一点?有什么想法吗?

I have seen that I need to do fetchAll(); and closeCursor(); ... but how to do it in Yii? Any ideas?

推荐答案

在执行和/或获取查询数据后，尝试:

After you execute and/or fetch your data for a query, try:

$command = false;

参见:http://www.yiiframework.com/doc/guide/1.1/en/database.dao

相关文章