Issue With Bindparam

Yii 1.1 General Discussion
1

Hi

Pretty new at Yii, and loving it :)

I’ve been working on a module for translations but have gotten into a little snag.

My module uses an SQLite db. There are two db connections defined in the main configuraiton (main.php), one for MySQL (db) and one for SQLite (i18n_db).

For every model/controll this setup works. However I’ve created a view in my SQLite db and trying to do the following. I have created an model for the view, but not using it.

public function actionListAllPrLanguage($languageId)

{

    // SQL statement for finding all translations for a given language


$sqlStm = 'SELECT * FROM Translations t WHERE t.LanguageId =:langId';


	


// SQL statement to count number of records


$sqlStmCount = 'SELECT COUNT(*) FROM Translations t WHERE t.LanguageId =:langId';


	


// Get connection


$connection = Yii::app()->i18n_db;


	


// Create command for SELECT and COUNT


$cmdSel   = $connection->createCommand($sqlStm);


$cmdCount = $connection->createCommand($sqlStmCount);


	


// Bind parameter


$cmdSel->bindParam(':langId', $languageId, PDO::PARAM_STR);


$cmdCount->bindParam(':langId', $languageId, PDO::PARAM_STR);


	


$dataprovider = new CSqlDataProvider($cmdSel->text, array(


			'db' => $connection,


			'keyField' => 'Id',


			'totalItemCount' => $connection->createCommand($cmdCount->text)->queryScalar(),


			'sort' => array(


			'attributes' => array('Message'), 


			'defaultOrder' => array('Message' => false),),


			'pagination' => array('pageSize' => 30,),


		));


	


$this->render('listPrLanguage', array('dataProvider' => $dataprovider));

}

Which also works fine except that the bindParam methods wont bind my variable.

The sql statement works if I hardcode the languageId.

I’ve been looking at this code for a few hours now and I cant seem to find the issue.

Does anyone know what I’m doing wrong?

2

Hi Ride, welcome to the forum.

As for the main query, CSqlDataProvider will createCommand and bindParam for you.

And the following will create another CDbCommand which is not yet bound a parameter.




'totalItemCount' => $connection->createCommand($cmdCount->text)->queryScalar(),

So, I hope the following will work.




public function actionListAllPrLanguage($languageId)

{

	// SQL statement for finding all translations for a given language

	$sqlStm = 'SELECT * FROM Translations t WHERE t.LanguageId =:langId';

		

	// SQL statement to count number of records

	$sqlStmCount = 'SELECT COUNT(*) FROM Translations t WHERE t.LanguageId =:langId';

		

	// Get connection

	$connection = Yii::app()->i18n_db;

		

	// Create command for SELECT and COUNT

	// $cmdSel   = $connection->createCommand($sqlStm);

	$cmdCount = $connection->createCommand($sqlStmCount);

		

	// Bind parameter

	// $cmdSel->bindParam(':langId', $languageId, PDO::PARAM_STR);

	$cmdCount->bindParam(':langId', $languageId, PDO::PARAM_STR);

		

	$dataprovider = new CSqlDataProvider($sqlStm, array(

				'params' => array(':langId' => $languageId),

				'db' => $connection,

				'keyField' => 'Id',

				'totalItemCount' => $cmdCount->queryScalar(),

				'sort' => array(

				'attributes' => array('Message'), 

				'defaultOrder' => array('Message' => false),),

				'pagination' => array('pageSize' => 30,),

			));

		

	$this->render('listPrLanguage', array('dataProvider' => $dataprovider));

}
3

THANKS!!!

This clarified heaps!!