Yii Framework Forum: How to use CWebLogRoute to display SQL queries with params replaced into SQL - Yii Framework Forum

Jump to content

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

How to use CWebLogRoute to display SQL queries with params replaced into SQL Rate Topic: -----

#1 User is offline   Pentium10 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 174
  • Joined: 10-December 10

Posted 16 March 2011 - 08:36 AM

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

#2 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 16 March 2011 - 08:51 AM

That's not possible, you should replace manually before running the query.
0

#3 User is offline   Yii Việt Nam 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 24-July 14
  • Location:Việt Nam

Posted 24 July 2014 - 06:15 AM

View PostPentium10, on 16 March 2011 - 08:36 AM, said:

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.


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