Yii Framework Forum: CDataProvider / CActiveRecord problems with stored procedures - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

CDataProvider / CActiveRecord problems with stored procedures Rate Topic: -----

#1 User is offline   marcius 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 14-April 09

Posted 30 January 2010 - 08:17 AM

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

#2 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,901
  • Joined: 04-October 08
  • Location:DC, USA

Posted 30 January 2010 - 08:33 AM

Your error callstack doesn't seem to be related with active data provider.

Did you set 'emulatePrepare' in 'db' component?
0

#3 User is offline   marcius 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 14-April 09

Posted 30 January 2010 - 08:52 AM

View Postqiang, on 30 January 2010 - 08:33 AM, said:

Your error callstack doesn't seem to be related with active data provider.


The exception "silented" (maybe this behaviour could be made better) makes me think that the error could be related to the use of CDBCommand in the CSpDataProvider class, that leaves something not in a perfect state.

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

View Postqiang, on 30 January 2010 - 08:33 AM, said:

Did you set 'emulatePrepare' in 'db' component?


Yes.
0

#4 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,901
  • Joined: 04-October 08
  • Location:DC, USA

Posted 30 January 2010 - 08:58 AM

What DB operations did you do before line 34 in viewAccountMonth.php? From the error message, this is more like the previous DB operation doesn't finish reading all data.
0

#5 User is offline   marcius 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 14-April 09

Posted 30 January 2010 - 10:10 AM

View Postqiang, on 30 January 2010 - 08:58 AM, said:

What DB operations did you do before line 34 in viewAccountMonth.php? From the error message, this is more like the previous DB operation doesn't finish reading all data.


In line 34 there is a CHtml::listData(Account::model()->findAll(),'id','name').
Before there is a CGridView fed by the SpDataProvider.

This is the full viewAccountMonth.php code
<?php
$this->breadcrumbs=array(
	'Statistics'=>array('index'),
);
?>
<h1>View Statistics Account by Month</h1>

<ul class="actions">
    <li><?php echo CHtml::link('Account by Month',array('viewAccountMonth')); ?></li>
    <li><?php echo CHtml::link('Account by Year',array('viewAccountYear')); ?></li>
</ul><!-- actions -->

<?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',
    ),
)); ?>

<div class="form">

<?php echo CHtml::beginForm(); ?>

    <?php echo CHtml::errorSummary($searchModel); ?>

    <div class="row">
            <?php echo CHtml::activeLabelEx($searchModel,'account_id'); ?>
            <?php echo CHtml::activeDropDownList($searchModel,'account_id', 
// THE FOLLOWING IS LINE 34
                CHtml::listData(Account::model()->findAll(),'id','name'), 
                array('prompt'=>' ')
                ); ?>
            <?php echo CHtml::error($searchModel,'account_id'); ?>
    </div>

    <div class="row submit">
        <?php echo CHtml::submitButton('Search'); ?>
    </div>


<?php echo CHtml::endForm(); ?>

</div><!-- form -->




and this is the controller code

<?php

class StatisticsController extends Controller
{
	const PAGE_SIZE=10;

	/**
	 * @var CActiveRecord the currently loaded data model instance.
	 */
	private $_model;

	/**
	 * @return array action filters
	 */
	public function filters()
	{
		return array(
			'accessControl', // perform access control for CRUD operations
		);
	}

	/**
	 * Specifies the access control rules.
	 * This method is used by the 'accessControl' filter.
	 * @return array access control rules
	 */
	public function accessRules()
	{
		return array(
			array('allow',  // allow all users to perform 'index' and 'view' actions
				'actions'=>array('index','viewAccountMonth'),
				'users'=>array('@'),
			),
			array('deny',  // deny all users
				'users'=>array('*'),
			),
		);
	}

	/**
	 * Displays a particular model.
	 */
	public function actionViewAccountMonth()
	{
        $dataProvider=new CSpDataProvider('Stat', array(
        ));
        $searchModel=new StatisticsSearch;
        
	$this->render('viewAccountMonth',array(
            'dataProvider'=>$dataProvider,
            'searchModel'=>$searchModel,
		));
	}
    
    public function actionIndex()
    {
        $this->render('index');
    }
}


0

#6 User is offline   marcius 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 14-April 09

Posted 30 January 2010 - 10:15 AM

It happens the same if I set up a simpler scenario, using two CGridView widgets.

The new controller action:

	public function actionViewAccountMonth()
	{
        $dataProvider=new CSpDataProvider('Stat', array(
        ));
        $dataProvider2=new CActiveDataProvider('Subject');
        
		$this->render('viewAccountMonth',array(
            'dataProvider'=>$dataProvider,
            'dataProvider2'=>$dataProvider2,
		));
	}


The new view:

<?php
$this->breadcrumbs=array(
	'Statistics'=>array('index'),
);
?>
<h1>View Statistics Account by Month</h1>

<ul class="actions">
    <li><?php echo CHtml::link('Account by Month',array('viewAccountMonth')); ?></li>
    <li><?php echo CHtml::link('Account by Year',array('viewAccountYear')); ?></li>
</ul><!-- actions -->

<?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',
    ),
)); ?>

0

#7 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,901
  • Joined: 04-October 08
  • Location:DC, USA

Posted 30 January 2010 - 04:10 PM

Could you try the following at the beginning of the view?
Yii::app()->db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
0

#8 User is offline   marcius 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 14-April 09

Posted 31 January 2010 - 01:32 PM

View Postqiang, on 30 January 2010 - 04:10 PM, said:

Could you try the following at the beginning of the view?
Yii::app()->db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);


I added the line, but nothing changed.

Can you point me to a solution to use Zii widgets with DAO (or an arbitrary SQL query) and not with ActiveRecord?

Thanks
0

#9 User is offline   elaprendiz 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 08-January 11
  • Location:peru

Posted 18 January 2011 - 09:24 AM

really interesting but, do you got solves the problem with store procedure?. your help its would very usefull.... :D
0

#10 User is offline   bigbasher 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 59
  • Joined: 23-February 11

Posted 03 October 2011 - 02:58 AM

I am also battling with this. In an app with only a single db connection it was solved by simply doing
            yii::app()->db->setActive(false);
            yii::app()->db->setActive(true);

after the stored procedure connection.

Now (different app) I am trying to do this on a secondary db connection and it seems this solution does not work.

I have tried closecursor(), queryall() etc but nothing seems to solve the problem.

Any ideas?
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users