When I have ‘enableParamLogging’ => true, I get this output
SELECT `readdate`FROM `usage_bill` `t`WHERE account = :acc and readdate<:endORDER BY `readdate` DESC LIMIT 1. Bound with :acc=02-0010-00,:end=2010-12-14
it’s really hard to run this query in the DB.How can I get those values replaced in the log window.
That’s not possible, you should replace manually before running the query.
Hi Pentium10, i have a solution for your request. My solution is override CDbCommand and CDbConnection.
MyCDbConnection.php
<?php
class MyDbConnection extends CDbConnection {
public function createCommand($query=null)
{
$this->setActive(true);
return new MyCDbCommand($this,$query);
}
}
MyCDbCommand.php
<?php
class MyCDbCommand extends CDbCommand {
public $myParam=array();
public function addLog() {
$params = array_merge($this->params,$this->myParam);
$sql = $this->getText();
if(!empty($params)) {
foreach ($params as $key => $value) {
$sql = str_replace($key, $value, $sql);
}
}
$sql = preg_replace("/\r\n|\n\r|\r|\n/",' ',$sql);
Yii::log($sql, 'sqltrace', 'system.db.CDbCommand');
//return $this;
}
public function bindValues($values)
{
foreach($values as $name=>$value)
{
$this->myParam[$name]=$value;
}
return parent::bindValues($values);
}
public function bindValue($name, $value, $dataType=null)
{
$this->myParam[$name]=$value;
return parent::bindValue($name, $value, $dataType);
}
public function query($params=array())
{
$this->addLog();
return parent::query($params);
}
public function queryAll($fetchAssociative=true,$params=array())
{
$this->addLog();
return parent::queryAll($fetchAssociative,$params);
}
public function queryColumn($params=array())
{
$this->addLog();
return parent::queryColumn($params);
}
public function queryRow($fetchAssociative=true,$params=array())
{
$this->addLog();
return parent::queryRow($fetchAssociative,$params);
}
public function queryScalar($params=array())
{
$this->addLog();
return parent::queryScalar($params);
}
}
In main.php config, you must change some param, like code follow:
'db'=>array(
'class' => 'MyDbConnection',
'connectionString' => 'mysql:host=localhost;dbname=customsqllog',
'emulatePrepare' => true,
'username' => 'root',
'password' => '',
'charset' => 'utf8',
//'enableProfiling'=>true,
//'enableParamLogging'=>true
),
'errorHandler'=>array(
// use 'site/error' action to display errors
'errorAction'=>'site/error',
),
'log'=>array(
'class'=>'CLogRouter',
'routes'=>array(
array(
'class'=>'CFileLogRoute',
'levels'=>'sqltrace',
'logFile' => 'dbaccess_'.date('Ymd').'.log',
'maxFileSize' => 1,
'maxLogFiles' => 10,
'categories'=>'system.db.CDbCommand',
),
And test result, example in SiteController, i add some query in index action. Example:
//Example 1
$model = Example::model()->findByPk(1);
//Example 2
$list1 = Example::model()->findAll(array(
'condition' => 'is_public=:is_public',
'params' => array(
':is_public' => 1,
),
));
//Example 3
$list2 = Example::model()->getDbConnection()->createCommand()
->setFetchMode(PDO::FETCH_OBJ)
->select('*')
->from('tbl_example')
->where('is_public = :is_public', array(':is_public' => 1))
->queryRow();
//Example 4
$list3 = Example::model()->getDbConnection()->createCommand()
->setFetchMode(PDO::FETCH_OBJ)
->select('*')
->from('tbl_example')
->where('is_public = :is_public', array(':is_public' => 1))
->queryAll();
//Example 5
$lastId = Example::model()->getDbConnection()->createCommand()
->select('IFNULL(MAX(id),0) as max')
->from('tbl_example')
->where('is_public = :is_public', array(':is_public' => 1))
->queryScalar();
And you can see in log file like result:
2014/07/24 17:58:17 [sqltrace] [system.db.CDbCommand] SHOW FULL COLUMNS FROM `tbl_example`
in E:\Projects\My\yiitest\apps\customsqllog\protected\components\MyCDbCommand.php (14)
in E:\Projects\My\yiitest\apps\customsqllog\protected\components\MyCDbCommand.php (41)
in E:\Projects\My\yiitest\apps\customsqllog\protected\controllers\SiteController.php (34)
2014/07/24 17:58:17 [sqltrace] [system.db.CDbCommand] SHOW CREATE TABLE `tbl_example`
in E:\Projects\My\yiitest\apps\customsqllog\protected\components\MyCDbCommand.php (14)
in E:\Projects\My\yiitest\apps\customsqllog\protected\components\MyCDbCommand.php (53)
in E:\Projects\My\yiitest\apps\customsqllog\protected\controllers\SiteController.php (34)
2014/07/24 17:58:17 [sqltrace] [system.db.CDbCommand] SELECT * FROM `tbl_example` `t` WHERE `t`.`id`=1 LIMIT 1
in E:\Projects\My\yiitest\apps\customsqllog\protected\components\MyCDbCommand.php (14)
in E:\Projects\My\yiitest\apps\customsqllog\protected\components\MyCDbCommand.php (53)
in E:\Projects\My\yiitest\apps\customsqllog\protected\controllers\SiteController.php (34)
2014/07/24 17:58:17 [sqltrace] [system.db.CDbCommand] SELECT * FROM `tbl_example` `t` WHERE is_public=1
in E:\Projects\My\yiitest\apps\customsqllog\protected\components\MyCDbCommand.php (14)
in E:\Projects\My\yiitest\apps\customsqllog\protected\components\MyCDbCommand.php (41)
in E:\Projects\My\yiitest\apps\customsqllog\protected\controllers\SiteController.php (42)
2014/07/24 17:58:17 [sqltrace] [system.db.CDbCommand] SELECT * FROM `tbl_example` WHERE is_public = 1
in E:\Projects\My\yiitest\apps\customsqllog\protected\components\MyCDbCommand.php (14)
in E:\Projects\My\yiitest\apps\customsqllog\protected\components\MyCDbCommand.php (53)
in E:\Projects\My\yiitest\apps\customsqllog\protected\controllers\SiteController.php (50)
2014/07/24 17:58:17 [sqltrace] [system.db.CDbCommand] SELECT * FROM `tbl_example` WHERE is_public = 1
in E:\Projects\My\yiitest\apps\customsqllog\protected\components\MyCDbCommand.php (14)
in E:\Projects\My\yiitest\apps\customsqllog\protected\components\MyCDbCommand.php (41)
in E:\Projects\My\yiitest\apps\customsqllog\protected\controllers\SiteController.php (58)
2014/07/24 17:58:17 [sqltrace] [system.db.CDbCommand] SELECT IFNULL(MAX(id),0) as max FROM `tbl_example` WHERE is_public = 1
in E:\Projects\My\yiitest\apps\customsqllog\protected\components\MyCDbCommand.php (14)
in E:\Projects\My\yiitest\apps\customsqllog\protected\components\MyCDbCommand.php (59)
in E:\Projects\My\yiitest\apps\customsqllog\protected\controllers\SiteController.php (65)
Hope this will help you. All example you can get at url: github.com/yiivietnam/yii-framework/tree/master/example/customsqllog