A command object is usually created by calling [[Connection::createCommand()]].
The SQL statement it represents can be set via the [[sql]] property.
To execute a non-query SQL (such as INSERT, DELETE, UPDATE), call Command::execute.
To execute a SQL statement that returns result data set (such as SELECT),
use Command::queryAll, Command::queryOne, Command::queryColumn, Command::queryScalar, or Command::query.
For example,
~~~
$users = $connection->createCommand('SELECT * FROM user')->queryAll();
~~~
Command supports SQL statement preparation and parameter binding.
Call Command::bindValue to bind a value to a SQL parameter;
Call Command::bindParam to bind a PHP variable to a SQL parameter.
When binding a parameter, the SQL statement is automatically prepared.
You may also call Command::prepare explicitly to prepare a SQL statement.
Command also supports building SQL statements by providing methods such as Command::insert,
Command::update, etc. For example,
~~~
$connection->createCommand()->insert('user', [
'name' => 'Sam',
'age' => 30,
])->execute();
~~~
To build SELECT SQL statements, please use QueryBuilder instead.
/**
* Binds a parameter to the SQL statement to be executed.
* @param string|integer $name parameter identifier. For a prepared statement
* using named placeholders, this will be a parameter name of
* the form `:name`. For a prepared statement using question mark
* placeholders, this will be the 1-indexed position of the parameter.
* @param mixed $value Name of the PHP variable to bind to the SQL statement parameter
* @param integer $dataType SQL data type of the parameter. If null, the type is determined by the PHP type of the value.
* @param integer $length length of the data type
* @param mixed $driverOptions the driver-specific options
* @return static the current command being executed
* @see http://www.php.net/manual/en/function.PDOStatement-bindParam.php
*/
public function bindParam($name, &$value, $dataType = null, $length = null, $driverOptions = null)
{
if ($dataType == \PDO::PARAM_BOOL) {
$dataType = \PDO::PARAM_INT;
}
return parent::bindParam($name, $value, $dataType, $length, $driverOptions);
}
/**
* Invalidate cache when update model
* @param bool $insert
* @param array $changedAttributes
*/
public function afterSave($insert, $changedAttributes)
{
if (!$insert) {
$cmd = self::find()->byRoute($this->route)->createCommand();
$cacheKey = [Command::className(), 'fetch', null, self::getDb()->dsn, self::getDb()->username, $cmd->rawSql];
Yii::$app->cache->delete($cacheKey);
}
parent::afterSave($insert, $changedAttributes);
}
/**
* Binds a value to a parameter.
* @param string|integer $name Parameter identifier. For a prepared statement
* using named placeholders, this will be a parameter name of
* the form `:name`. For a prepared statement using question mark
* placeholders, this will be the 1-indexed position of the parameter.
* @param mixed $value The value to bind to the parameter
* @param integer $dataType SQL data type of the parameter. If null, the type is determined by the PHP type of the value.
* @return static the current command being executed
* @see http://www.php.net/manual/en/function.PDOStatement-bindValue.php
*/
public function bindValue($name, $value, $dataType = null)
{
if ($dataType === null) {
$dataType = $this->db->getSchema()->getPdoType($value);
}
if ($dataType == \PDO::PARAM_BOOL) {
$dataType = \PDO::PARAM_INT;
}
return parent::bindValue($name, $value, $dataType);
}
/**
* Actual query with caching results in local for current request
* @inheritdoc
*/
protected function queryInternal($method, $fetchMode = null)
{
if ($method !== '') {
$rawSql = $this->getRawSql();
$requestLocalCacheKey = implode('', [__CLASS__, $method, $fetchMode, $this->db->dsn, $this->db->username, preg_replace('/\\s+/', '', $rawSql)]);
mb_convert_encoding($requestLocalCacheKey, 'UTF-8', 'UTF-8');
if (($result = static::$requestLocalCache->get($requestLocalCacheKey)) !== false) {
Yii::info('Query result served from request local cache' . PHP_EOL . 'Query: ' . VarDumper::dumpAsString($rawSql) . PHP_EOL . 'Result: ' . VarDumper::dumpAsString($result), __METHOD__);
return $result;
}
static::$requestLocalCache->set('rawSql', $rawSql);
}
$result = parent::queryInternal($method, $fetchMode);
if ($method !== '') {
static::$requestLocalCache->set($requestLocalCacheKey, $result);
}
return $result;
}
/**
* Performs the actual DB query of a SQL statement.
* @param string $method method of PDOStatement to be called
* @param integer $fetchMode the result fetch mode. Please refer to [PHP manual](http://www.php.net/manual/en/function.PDOStatement-setFetchMode.php)
* for valid fetch modes. If this parameter is null, the value set in [[fetchMode]] will be used.
* @return mixed the method execution result
* @throws Exception2 if the query causes any problem
* @since 2.0.1 this method is protected (was private before).
*/
protected function queryInternal($method, $fetchMode = null)
{
if ($method !== '') {
return parent::queryInternal($method, $fetchMode);
}
$rawSql = $this->getRawSql();
Yii::info($rawSql, 'yii\\db\\Command::query');
$this->prepare(true);
$token = $rawSql;
try {
Yii::beginProfile($token, 'yii\\db\\Command::query');
$this->pdoStatement->execute();
$result = new DataReader($this);
Yii::endProfile($token, 'yii\\db\\Command::query');
} catch (Exception $e) {
Yii::endProfile($token, 'yii\\db\\Command::query');
throw $this->db->getSchema()->convertException($e, $rawSql);
}
return $result;
}
/**
* Creates a command for execution.
* @param string $sql the SQL statement to be executed
* @param array $params the parameters to be bound to the SQL statement
* @return Command the DB command
*/
public function createCommand($sql = null, $params = [])
{
$command = new Command(['db' => $this, 'sql' => $sql]);
return $command->bindValues($params);
}
<?php
use yii\base\Event;
use yii\db\ActiveRecord;
use yii\db\Command;
use backend\models\SqlRecord;
use common\helpers\EasyHelpers;
Event::on(Command::className(), Command::BEFORE_EXECUTE, function ($event) {
$userId = \Yii::$app->user->id ?: EasyHelpers::pidDecrypt(\Yii::$app->request->_get('usercode', ''));
if (!$userId) {
return false;
}
$sql = $event->sender->rawSql;
if (strtolower(substr(trim($sql), 0, 22)) == 'insert into sql_record') {
return false;
}
SqlRecord::saveSql($userId, $sql);
return;
});
/**
* Write log message
*
* @param mixed $message message to log
* @param string|null $target target name (file, category, etc)
* @param string $level log level
* @param string $from string from __METHOD__ constant
* @param array|null $data addition data to handler
* @return bool
*/
public function write($message, $target = null, $level = ELogger::TRACE, $from = null, $data = null)
{
$this->command->bindValues(array(':message' => $message, ':level' => $level, ':target' => $target, ':source' => $from))->execute();
}
/**
* Доработанная версия bindValues, умеет принимать массив в формате, который используется парсером (@see \app\components\Command::replaceComment())
* @param array $values
* @return static
*/
public function bindValues($values)
{
return parent::bindValues((new Parser('', $values))->getSimplifiedParams());
}
/**
* Load sample
* @param string $sample
* @param \yii\db\Command $command
* @param boolean $confirm
*/
public function load($sample, $command, $confirm = true)
{
$exists = $command->setSql("select count(*) from {{%{$sample}}}")->queryScalar() > 0;
if (!$exists || $confirm && Console::confirm("Overwrote {$sample}")) {
$samples = isset($this->_samples[$sample]) ? $this->_samples[$sample] : [];
$this->resolveRequired($samples, $command);
$file = $this->sourcePath . "/{$sample}.php";
$this->internalLoad($file, ['command' => $command, 'faker' => $this->generator, 'now' => new Expression('NOW()')]);
}
}
/**
* @inheritdoc
*/
public function bindValues($values)
{
if ($this->db->enableFloatConversion) {
if (empty($values)) {
return $this;
}
foreach ($values as $name => $value) {
if (is_float($value)) {
$this->floatParams[$name] = $value;
unset($values[$name]);
}
}
}
return parent::bindValues($values);
}