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); }