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

Property	Type	Description	Defined 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

Method	Description	Defined 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

public CDbConnection getConnection()

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.

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.