I wrote this sample class to investigate adding stored procedures (or manual SQL query) support to Zii widgets.
It works fine when in the view this is the only DataProvider, but when I add a second one (see the php source below) I get an error I can’t understand.
===== Class source
<?php
class CSpDataProvider extends CDataProvider
{
public $modelClass;
public $keyAttribute;
private $_criteria;
public function __construct($modelClass,$config=array())
{
$this->modelClass=$modelClass;
$this->setId($modelClass);
foreach($config as $key=>$value)
$this->$key=$value;
}
public function getCriteria()
{
if($this->_criteria===null)
$this->_criteria=new CDbCriteria;
return $this->_criteria;
}
public function setCriteria($value)
{
$this->_criteria=$value instanceof CDbCriteria ? $value : new CDbCriteria($value);
}
public function getSort()
{
return false;
}
protected function fetchData()
{
$sql = "CALL sp_pivot('accounts.name', 'date_format(transactions.date, \"%Y_%m\")', 'transactions.amount', 'transactions join accounts on transactions.account_id = accounts.id', '1=1')";
$command=Yii::app()->db->createCommand($sql);
return $command->queryAll();
}
protected function fetchKeys()
{
$keys=array('name', '01', '02', '03', '04');
return $keys;
}
protected function calculateTotalItemCount()
{
return 0;
}
}
======== View source (the order of the widgets is important, if I exchange them the error disappears)
<?php $this->widget('zii.widgets.grid.CGridView', array(
'dataProvider'=>$dataProvider,
'columns'=>array(
'name:text:Account',
'2009_10:number:2009_10',
'2009_11:number:2009_11',
'2009_12:number:2009_12',
'2010_01:number:2010_01',
),
)); ?>
<?php $this->widget('zii.widgets.grid.CGridView', array(
'dataProvider'=>$dataProvider2,
'columns'=>array(
'id',
'name',
),
)); ?>
======== The error
The error I get is this, but the table is existing and working in other parts of the same application.
CDbException
The table "accounts" for active record class "Account" cannot be found in the database.
Stack Trace
#0 D:\Dev\www\yii-1.1.0.r1700\framework\db\ar\CActiveRecord.php(328): CActiveRecordMetaData->__construct(Object(Account))
#1 D:\Dev\www\yspesenido\protected\models\Account.php(22): CActiveRecord::model('Account')
#2 D:\Dev\www\yspesenido\protected\views\statistics\viewAccountMonth.php(34): Account::model()
#3 D:\Dev\www\yii-1.1.0.r1700\framework\web\CBaseController.php(119): require('D:\Dev\www\yspe...')
#4 D:\Dev\www\yii-1.1.0.r1700\framework\web\CBaseController.php(88): CBaseController->renderInternal('D:\Dev\www\yspe...', Array, true)
#5 D:\Dev\www\yii-1.1.0.r1700\framework\web\CController.php(732): CBaseController->renderFile('D:\Dev\www\yspe...', Array, true)
#6 D:\Dev\www\yii-1.1.0.r1700\framework\web\CController.php(671): CController->renderPartial('viewAccountMont...', Array, true)
#7 D:\Dev\www\yspesenido\protected\controllers\StatisticsController.php(54): CController->render('viewAccountMont...', Array)
#8 D:\Dev\www\yii-1.1.0.r1700\framework\web\actions\CInlineAction.php(32): StatisticsController->actionViewAccountMonth()
#9 D:\Dev\www\yii-1.1.0.r1700\framework\web\CController.php(300): CInlineAction->run()
#10 D:\Dev\www\yii-1.1.0.r1700\framework\web\filters\CFilterChain.php(129): CController->runAction(Object(CInlineAction))
#11 D:\Dev\www\yii-1.1.0.r1700\framework\web\filters\CFilter.php(41): CFilterChain->run()
#12 D:\Dev\www\yii-1.1.0.r1700\framework\web\CController.php(983): CFilter->filter(Object(CFilterChain))
#13 D:\Dev\www\yii-1.1.0.r1700\framework\web\filters\CInlineFilter.php(59): CController->filterAccessControl(Object(CFilterChain))
#14 D:\Dev\www\yii-1.1.0.r1700\framework\web\filters\CFilterChain.php(126): CInlineFilter->filter(Object(CFilterChain))
#15 D:\Dev\www\yii-1.1.0.r1700\framework\web\CController.php(283): CFilterChain->run()
#16 D:\Dev\www\yii-1.1.0.r1700\framework\web\CController.php(257): CController->runActionWithFilters(Object(CInlineAction), Array)
#17 D:\Dev\www\yii-1.1.0.r1700\framework\web\CWebApplication.php(320): CController->run('viewAccountMont...')
#18 D:\Dev\www\yii-1.1.0.r1700\framework\web\CWebApplication.php(120): CWebApplication->runController('statistics/view...')
#19 D:\Dev\www\yii-1.1.0.r1700\framework\base\CApplication.php(135): CWebApplication->processRequest()
#20 D:\Dev\www\yspesenido\index.php(20): CApplication->run()
#21 {main}
But I acually noticed that the exception is "silented" by this piece of code:
protected function findColumns($table)
{
$sql='SHOW COLUMNS FROM '.$table->rawName;
try
{
$columns=$this->getDbConnection()->createCommand($sql)->queryAll();
}
catch(Exception $e)
{
return false;
}
foreach($columns as $column)
{
Inspecting the $e variable I can see that the actual exception is this:
“CDbCommand ha riportato un errore nell’esecuzione della query SQL: 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.”
Please give me some tip on how to solve the problem.
