0 follower

CDbCommand

Package system.db
Inheritance class CDbCommand » CComponent
Since 1.0
Version $Id$
Source Code framework/db/CDbCommand.php
CDbCommand represents an SQL statement to execute against a database.

It is usually created by calling CDbConnection::createCommand. The SQL statement to be executed may be set via Text.

To execute a non-query SQL (such as insert, delete, update), call execute. To execute an SQL statement that returns result data set (such as SELECT), use query or its convenient versions queryRow, queryColumn, or queryScalar.

If an SQL statement returns results (such as a SELECT SQL), the results can be accessed via the returned CDbDataReader.

CDbCommand supports SQL statment preparation and parameter binding. Call bindParam to bind a PHP variable to a parameter in SQL. Call bindValue to bind a value to an SQL parameter. When binding a parameter, the SQL statement is automatically prepared. You may also call prepare to explicitly prepare an SQL statement.

Public Properties

Hide inherited properties

PropertyTypeDescriptionDefined By
connection CDbConnection the connection associated with this command CDbCommand
pdoStatement PDOStatement the underlying PDOStatement for this command It could be null if the statement is not prepared yet. CDbCommand
text string the SQL statement to be executed CDbCommand

Public Methods

Hide inherited methods

MethodDescriptionDefined By
__call() Calls the named method which is not a class method. CComponent
__construct() Constructor. CDbCommand
__get() Returns a property value, an event handler list or a behavior based on its name. CComponent
__isset() Checks if a property value is null. CComponent
__set() Sets value of a component property. CComponent
__sleep() Set the statement to null when serializing. CDbCommand
__unset() Sets a component property to be null. CComponent
asa() Returns the named behavior object. CComponent
attachBehavior() Attaches a behavior to this component. CComponent
attachBehaviors() Attaches a list of behaviors to the component. CComponent
attachEventHandler() Attaches an event handler to an event. CComponent
bindParam() Binds a parameter to the SQL statement to be executed. CDbCommand
bindValue() Binds a value to a parameter. CDbCommand
canGetProperty() Determines whether a property can be read. CComponent
canSetProperty() Determines whether a property can be set. CComponent
cancel() Cancels the execution of the SQL statement. CDbCommand
detachBehavior() Detaches a behavior from the component. CComponent
detachBehaviors() Detaches all behaviors from the component. CComponent
detachEventHandler() Detaches an existing event handler. CComponent
disableBehavior() Disables an attached behavior. CComponent
disableBehaviors() Disables all behaviors attached to this component. CComponent
enableBehavior() Enables an attached behavior. CComponent
enableBehaviors() Enables all behaviors attached to this component. CComponent
execute() Executes the SQL statement. CDbCommand
getConnection() Returns the connection associated with this command CDbCommand
getEventHandlers() Returns the list of attached event handlers for an event. CComponent
getPdoStatement() Returns the underlying PDOStatement for this command It could be null if the statement is not prepared yet. CDbCommand
getText() Returns the SQL statement to be executed CDbCommand
hasEvent() Determines whether an event is defined. CComponent
hasEventHandler() Checks whether the named event has attached handlers. CComponent
hasProperty() Determines whether a property is defined. CComponent
prepare() Prepares the SQL statement to be executed. CDbCommand
query() Executes the SQL statement and returns query result. CDbCommand
queryAll() Executes the SQL statement and returns all rows. CDbCommand
queryColumn() Executes the SQL statement and returns the first column of the result. CDbCommand
queryRow() Executes the SQL statement and returns the first row of the result. CDbCommand
queryScalar() Executes the SQL statement and returns the value of the first column in the first row of data. CDbCommand
raiseEvent() Raises an event. CComponent
setText() Specifies the SQL statement to be executed. CDbCommand

Property Details

connection property read-only

the connection associated with this command

pdoStatement property read-only
public PDOStatement getPdoStatement()

the underlying PDOStatement for this command It could be null if the statement is not prepared yet.

text property
public string getText()
public void setText(string $value)

the SQL statement to be executed

Method Details

__construct() method
public void __construct(CDbConnection $connection, string $text)
$connection CDbConnection the database connection
$text string the SQL statement to be executed
Source Code: framework/db/CDbCommand.php#48 (show)
public function __construct(CDbConnection $connection,$text)
{
    
$this->_connection=$connection;
    
$this->setText($text);
}

Constructor.

__sleep() method
public void __sleep()
Source Code: framework/db/CDbCommand.php#57 (show)
public function __sleep()
{
    
$this->_statement=null;
    return 
array_keys(get_object_vars($this));
}

Set the statement to null when serializing.

bindParam() method
public CDbCommand bindParam(mixed $name, mixed &$value, int $dataType=NULL, int $length=NULL)
$name mixed 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.
$value mixed Name of the PHP variable to bind to the SQL statement parameter
$dataType int SQL data type of the parameter. If null, the type is determined by the PHP type of the value.
$length int length of the data type
{return} CDbCommand the current command being executed (this is available since version 1.0.8)
Source Code: framework/db/CDbCommand.php#144 (show)
public function bindParam($name, &$value$dataType=null$length=null)
{
    
$this->prepare();
    if(
$dataType===null)
        
$this->_statement->bindParam($name,$value,$this->_connection->getPdoType(gettype($value)));
    else if(
$length===null)
        
$this->_statement->bindParam($name,$value,$dataType);
    else
        
$this->_statement->bindParam($name,$value,$dataType,$length);
    if(
$this->_connection->enableParamLogging)
        
$this->_params[$name]='['.gettype($value).']';
    return 
$this;
}

Binds a parameter to the SQL statement to be executed.

bindValue() method
public CDbCommand bindValue(mixed $name, mixed $value, int $dataType=NULL)
$name mixed 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.
$value mixed The value to bind to the parameter
$dataType int SQL data type of the parameter. If null, the type is determined by the PHP type of the value.
{return} CDbCommand the current command being executed (this is available since version 1.0.8)
Source Code: framework/db/CDbCommand.php#169 (show)
public function bindValue($name$value$dataType=null)
{
    
$this->prepare();
    if(
$dataType===null)
        
$this->_statement->bindValue($name,$value,$this->_connection->getPdoType(gettype($value)));
    else
        
$this->_statement->bindValue($name,$value,$dataType);
    if(
$this->_connection->enableParamLogging)
        
$this->_params[$name]=var_export($value,true);
    return 
$this;
}

Binds a value to a parameter.

cancel() method
public void cancel()
Source Code: framework/db/CDbCommand.php#127 (show)
public function cancel()
{
    
$this->_statement=null;
}

Cancels the execution of the SQL statement.

execute() method
public integer execute(array $params=array ( ))
$params array input parameters (name=>value) for the SQL execution. This is an alternative to bindParam and bindValue. If you have multiple input parameters, passing them in this way can improve the performance. Note that you pass parameters in this way, you cannot bind parameters or values using bindParam or bindValue, and vice versa. binding methods and the input parameters this way can improve the performance. This parameter has been available since version 1.0.10.
{return} integer number of rows affected by the execution.
Source Code: framework/db/CDbCommand.php#194 (show)
public function execute($params=array())
{
    if(
$this->_connection->enableParamLogging && ($pars=array_merge($this->_params,$params))!==array())
    {
        foreach(
$pars as $name=>$value)
            
$pars[$name]=$name.'='.$value;
        
$par='. Bind with parameter ' .implode(', ',$pars);
    }
    else
        
$par='';
    
Yii::trace('Executing SQL: '.$this->getText().$par,'system.db.CDbCommand');
    try
    {
        if(
$this->_connection->enableProfiling)
            
Yii::beginProfile('system.db.CDbCommand.execute('.$this->getText().')','system.db.CDbCommand.execute');

        
$this->prepare();
        
$this->_statement->execute($params===array() ? null $params);
        
$n=$this->_statement->rowCount();

        if(
$this->_connection->enableProfiling)
            
Yii::endProfile('system.db.CDbCommand.execute('.$this->getText().')','system.db.CDbCommand.execute');

        return 
$n;
    }
    catch(
Exception $e)
    {
        if(
$this->_connection->enableProfiling)
            
Yii::endProfile('system.db.CDbCommand.execute('.$this->getText().')','system.db.CDbCommand.execute');
        
Yii::log('Error in executing SQL: '.$this->getText().$par,CLogger::LEVEL_ERROR,'system.db.CDbCommand');
        throw new 
CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}'.$this->text,
            array(
'{error}'=>$e->getMessage())));
    }
}

Executes the SQL statement. This method is meant only for executing non-query SQL statement. No result set will be returned.

getConnection() method
public CDbConnection getConnection()
{return} CDbConnection the connection associated with this command
Source Code: framework/db/CDbCommand.php#85 (show)
public function getConnection()
{
    return 
$this->_connection;
}

getPdoStatement() method
public PDOStatement getPdoStatement()
{return} PDOStatement the underlying PDOStatement for this command It could be null if the statement is not prepared yet.
Source Code: framework/db/CDbCommand.php#94 (show)
public function getPdoStatement()
{
    return 
$this->_statement;
}

getText() method
public string getText()
{return} string the SQL statement to be executed
Source Code: framework/db/CDbCommand.php#66 (show)
public function getText()
{
    return 
$this->_text;
}

prepare() method
public void prepare()
Source Code: framework/db/CDbCommand.php#106 (show)
public function prepare()
{
    if(
$this->_statement==null)
    {
        try
        {
            
$this->_statement=$this->getConnection()->getPdoInstance()->prepare($this->getText());
            
$this->_params=array();
        }
        catch(
Exception $e)
        {
            
Yii::log('Error in preparing SQL: '.$this->getText(),CLogger::LEVEL_ERROR,'system.db.CDbCommand');
            throw new 
CDbException(Yii::t('yii','CDbCommand failed to prepare the SQL statement: {error}',
                array(
'{error}'=>$e->getMessage())));
        }
    }
}

Prepares the SQL statement to be executed. For complex SQL statement that is to be executed multiple times, this may improve performance. For SQL statement with binding parameters, this method is invoked automatically.

query() method
public CDbDataReader query(array $params=array ( ))
$params array input parameters (name=>value) for the SQL execution. This is an alternative to bindParam and bindValue. If you have multiple input parameters, passing them in this way can improve the performance. Note that you pass parameters in this way, you cannot bind parameters or values using bindParam or bindValue, and vice versa. binding methods and the input parameters this way can improve the performance. This parameter has been available since version 1.0.10.
{return} CDbDataReader the reader object for fetching the query result
Source Code: framework/db/CDbCommand.php#241 (show)
public function query($params=array())
{
    return 
$this->queryInternal('',0,$params);
}

Executes the SQL statement and returns query result. This method is for executing an SQL query that returns result set.

queryAll() method
public array queryAll(boolean $fetchAssociative=true, array $params=array ( ))
$fetchAssociative boolean whether each row should be returned as an associated array with column names as the keys or the array keys are column indexes (0-based).
$params array input parameters (name=>value) for the SQL execution. This is an alternative to bindParam and bindValue. If you have multiple input parameters, passing them in this way can improve the performance. Note that you pass parameters in this way, you cannot bind parameters or values using bindParam or bindValue, and vice versa. binding methods and the input parameters this way can improve the performance. This parameter has been available since version 1.0.10.
{return} array all rows of the query result. Each array element is an array representing a row. An empty array is returned if the query results in nothing.
Source Code: framework/db/CDbCommand.php#260 (show)
public function queryAll($fetchAssociative=true,$params=array())
{
    return 
$this->queryInternal('fetchAll',$fetchAssociative PDO::FETCH_ASSOC PDO::FETCH_NUM$params);
}

Executes the SQL statement and returns all rows.

queryColumn() method
public array queryColumn(array $params=array ( ))
$params array input parameters (name=>value) for the SQL execution. This is an alternative to bindParam and bindValue. If you have multiple input parameters, passing them in this way can improve the performance. Note that you pass parameters in this way, you cannot bind parameters or values using bindParam or bindValue, and vice versa. binding methods and the input parameters this way can improve the performance. This parameter has been available since version 1.0.10.
{return} array the first column of the query result. Empty array if no result.
Source Code: framework/db/CDbCommand.php#319 (show)
public function queryColumn($params=array())
{
    return 
$this->queryInternal('fetchAll',PDO::FETCH_COLUMN,$params);
}

Executes the SQL statement and returns the first column of the result. This is a convenient method of query when only the first column of data is needed. Note, the column returned will contain the first element in each row of result.

queryRow() method
public array queryRow(boolean $fetchAssociative=true, array $params=array ( ))
$fetchAssociative boolean whether the row should be returned as an associated array with column names as the keys or the array keys are column indexes (0-based).
$params array input parameters (name=>value) for the SQL execution. This is an alternative to bindParam and bindValue. If you have multiple input parameters, passing them in this way can improve the performance. Note that you pass parameters in this way, you cannot bind parameters or values using bindParam or bindValue, and vice versa. binding methods and the input parameters this way can improve the performance. This parameter has been available since version 1.0.10.
{return} array the first row of the query result, false if no result.
Source Code: framework/db/CDbCommand.php#279 (show)
public function queryRow($fetchAssociative=true,$params=array())
{
    return 
$this->queryInternal('fetch',$fetchAssociative PDO::FETCH_ASSOC PDO::FETCH_NUM$params);
}

Executes the SQL statement and returns the first row of the result. This is a convenient method of query when only the first row of data is needed.

queryScalar() method
public mixed queryScalar(array $params=array ( ))
$params array input parameters (name=>value) for the SQL execution. This is an alternative to bindParam and bindValue. If you have multiple input parameters, passing them in this way can improve the performance. Note that you pass parameters in this way, you cannot bind parameters or values using bindParam or bindValue, and vice versa. binding methods and the input parameters this way can improve the performance. This parameter has been available since version 1.0.10.
{return} mixed the value of the first column in the first row of the query result. False is returned if there is no value.
Source Code: framework/db/CDbCommand.php#297 (show)
public function queryScalar($params=array())
{
    
$result=$this->queryInternal('fetchColumn',0,$params);
    if(
is_resource($result) && get_resource_type($result)==='stream')
        return 
stream_get_contents($result);
    else
        return 
$result;
}

Executes the SQL statement and returns the value of the first column in the first row of data. This is a convenient method of query when only a single scalar value is needed (e.g. obtaining the count of the records).

setText() method
public void setText(string $value)
$value string the SQL statement to be executed
Source Code: framework/db/CDbCommand.php#76 (show)
public function setText($value)
{
    
$this->_text=$value;
    
$this->cancel();
}

Specifies the SQL statement to be executed. Any previous execution will be terminated or cancel.