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.
Since: 2.0
Author: Qiang Xue (qiang.xue@gmail.com)
Inheritance: extends yii\base\Component
Ejemplo n.º 1
0
 /**
  * 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);
 }
Ejemplo n.º 2
0
 /**
  * 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);
 }
Ejemplo n.º 3
0
 /**
  * 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);
 }
Ejemplo n.º 4
0
 /**
  * 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;
 }
Ejemplo n.º 5
0
 /**
  * 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;
 }
Ejemplo n.º 6
0
 /**
  * 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);
 }
Ejemplo n.º 7
0
<?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;
});
Ejemplo n.º 8
0
 /**
  * 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();
 }
Ejemplo n.º 9
0
 /**
  * Доработанная версия bindValues, умеет принимать массив в формате, который используется парсером (@see \app\components\Command::replaceComment())
  * @param array $values
  * @return static
  */
 public function bindValues($values)
 {
     return parent::bindValues((new Parser('', $values))->getSimplifiedParams());
 }
Ejemplo n.º 10
0
 /**
  * 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()')]);
     }
 }
Ejemplo n.º 11
0
 /**
  * @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);
 }