CDbConnection

$connection=new CDbConnection($dsn,$username,$password);
$connection->active=true;

$command=$connection->createCommand($sqlStatement);
$command->execute();   // a non-query SQL statement execution
// or execute an SQL query and fetch the result set
$reader=$command->query();

// each $row is an array representing a row of data
foreach($reader as $row) ...

$command=$connection->createCommand($sqlStatement);
$command->bindParam($name1,$value1);
$command->bindParam($name2,$value2);
$command->execute();

$transaction=$connection->beginTransaction();
try
{
   $connection->createCommand($sql1)->execute();
   $connection->createCommand($sql2)->execute();
   //.... other SQL executions
   $transaction->commit();
}
catch(Exception $e)
{
   $transaction->rollback();
}

array(
    'components'=>array(
        'db'=>array(
            'class'=>'CDbConnection',
            'connectionString'=>'sqlite:path/to/dbfile',
        ),
    ),
)

'db'=>array(
    'class'=>'CDbConnection',
    'driverName'=>'mysql',
    'connectionString'=>'odbc:Driver={MySQL};Server=127.0.0.1;Database=test',
    'username'=>'',
    'password'=>'',
),

Property Details

active property

public boolean getActive()
public void setActive(boolean $value)

Returns whether the DB connection is established.

attributes property (available since v1.1.7)

public array getAttributes()
public void setAttributes(array $values)

Returns the attributes that are previously explicitly set for the DB connection.

autoCommit property

public boolean getAutoCommit()
public void setAutoCommit(boolean $value)

Returns whether creating or updating a DB record will be automatically committed. Some DBMS (such as sqlite) may not support this feature.

autoConnect property

public boolean $autoConnect;

whether the database connection should be automatically established the component is being initialized. Defaults to true. Note, this property is only effective when the CDbConnection object is used as an application component.

charset property

public string $charset;

the charset used for database connection. The property is only used for MySQL, MariaDB and PostgreSQL databases. Defaults to null, meaning using default charset as specified by the database.

Note that if you're using GBK or BIG5 then it's highly recommended to update to PHP 5.3.6+ and to specify charset via DSN like 'mysql:dbname=mydatabase;host=127.0.0.1;charset=GBK;'.

clientVersion property read-only

public string getClientVersion()

Returns the version information of the DB driver.

columnCase property

public mixed getColumnCase()
public void setColumnCase(mixed $value)

Returns the case of the column names

commandBuilder property read-only

public CDbCommandBuilder getCommandBuilder()

Returns the SQL command builder for the current DB connection.

connectionStatus property read-only

public string getConnectionStatus()

Returns the status of the connection. Some DBMS (such as sqlite) may not support this feature.

connectionString property

public string $connectionString;

The Data Source Name, or DSN, contains the information required to connect to the database.

currentTransaction property read-only

public CDbTransaction getCurrentTransaction()

Returns the currently active transaction.

driverMap property (available since v1.1.6)

public array $driverMap;

mapping between PDO driver and schema class name. A schema class can be specified using path alias.

driverName property

public string getDriverName()
public void setDriverName(string $driverName)

Returns the name of the DB driver.

emulatePrepare property

public boolean $emulatePrepare;

whether to turn on prepare emulation. Defaults to false, meaning PDO will use the native prepare support if available. For some databases (such as MySQL), this may need to be set true so that PDO can emulate the prepare support to bypass the buggy native prepare support. Note, this property is only effective for PHP 5.1.3 or above. The default value is null, which will not change the ATTR_EMULATE_PREPARES value of PDO.

enableParamLogging property

public boolean $enableParamLogging;

whether to log the values that are bound to a prepare SQL statement. Defaults to false. During development, you may consider setting this property to true so that parameter values bound to SQL statements are logged for debugging purpose. You should be aware that logging parameter values could be expensive and have significant impact on the performance of your application.

enableProfiling property

public boolean $enableProfiling;

whether to enable profiling the SQL statements being executed. Defaults to false. This should be mainly enabled and used during development to find out the bottleneck of SQL executions.

initSQLs property (available since v1.1.1)

public array $initSQLs;

list of SQL statements that should be executed right after the DB connection is established.

lastInsertID property read-only

public string getLastInsertID(string $sequenceName='')

Returns the ID of the last inserted row or sequence value.

nullConversion property

public mixed getNullConversion()
public void setNullConversion(mixed $value)

Returns how the null and empty strings are converted.

password property

public string $password;

the password for establishing DB connection. Defaults to empty string.

pdoClass property (available since v1.1.8)

public string $pdoClass;

Custom PDO wrapper class.

pdoInstance property read-only

public PDO getPdoInstance()

Returns the PDO instance.

persistent property

public boolean getPersistent()
public void setPersistent(boolean $value)

Returns whether the connection is persistent or not. Some DBMS (such as sqlite) may not support this feature.

prefetch property read-only

public boolean getPrefetch()

Returns whether the connection performs data prefetching.

queryCacheID property (available since v1.1.7)

public string $queryCacheID;

the ID of the cache application component that is used for query caching. Defaults to 'cache' which refers to the primary cache application component. Set this property to false if you want to disable query caching.

queryCachingCount property (available since v1.1.7)

public integer $queryCachingCount;

the number of SQL statements that need to be cached next. If this is 0, then even if query caching is enabled, no query will be cached. Note that each time after executing a SQL statement (whether executed on DB server or fetched from query cache), this property will be reduced by 1 until 0.

queryCachingDependency property (available since v1.1.7)

public CCacheDependency|ICacheDependency $queryCachingDependency;

the dependency that will be used when saving query results into cache.

queryCachingDuration property (available since v1.1.7)

public integer $queryCachingDuration;

number of seconds that query results can remain valid in cache. Use 0 or negative value to indicate not caching query results (the default behavior).

In order to enable query caching, this property must be a positive integer and queryCacheID must point to a valid cache component ID.

The method cache() is provided as a convenient way of setting this property and queryCachingDependency on the fly.

schema property read-only

public CDbSchema getSchema()

Returns the database schema for the current connection

schemaCacheID property

public string $schemaCacheID;

the ID of the cache application component that is used to cache the table metadata. Defaults to 'cache' which refers to the primary cache application component. Set this property to false if you want to disable caching table metadata.

schemaCachingDuration property

public integer $schemaCachingDuration;

number of seconds that table metadata can remain valid in cache. Use 0 or negative value to indicate not caching schema. If greater than 0 and the primary cache is enabled, the table metadata will be cached.

schemaCachingExclude property

public array $schemaCachingExclude;

list of tables whose metadata should NOT be cached. Defaults to empty array.

serverInfo property read-only

public string getServerInfo()

Returns the information of DBMS server.

serverVersion property read-only

public string getServerVersion()

Returns the version information of DBMS server.

stats property read-only

public array getStats()

Returns the statistical results of SQL executions. The results returned include the number of SQL statements executed and the total time spent. In order to use this method, enableProfiling has to be set true.

tablePrefix property (available since v1.1.0)

public string $tablePrefix;

the default prefix for table names. Defaults to null, meaning no table prefix. By setting this property, any token like '{{tableName}}' in CDbCommand::text will be replaced by 'prefixTableName', where 'prefix' refers to this property value.

timeout property read-only

public integer getTimeout()

Returns the timeout settings for the connection.

username property

public string $username;

the username for establishing DB connection. Defaults to empty string.

Method Details

__construct() method

public void __construct(string $dsn='', string $username='', string $password='')

public function __construct($dsn='',$username='',$password='')
{
    $this->connectionString=$dsn;
    $this->username=$username;
    $this->password=$password;
}

Constructor. Note, the DB connection is not established when this connection instance is created. Set active property to true to establish the connection.

__sleep() method

public array __sleep()

public function __sleep()
{
    $this->close();
    return array_keys(get_object_vars($this));
}

Close the connection when serializing.

beginTransaction() method

public CDbTransaction beginTransaction()

public function beginTransaction()
{
    Yii::trace('Starting transaction','system.db.CDbConnection');
    $this->setActive(true);
    $this->_pdo->beginTransaction();
    return $this->_transaction=new CDbTransaction($this);
}

Starts a transaction.

cache() method (available since v1.1.7)

public static cache(integer $duration, CCacheDependency|ICacheDependency $dependency=NULL, integer $queryCount=1)

public function cache($duration, $dependency=null, $queryCount=1)
{
    $this->queryCachingDuration=$duration;
    $this->queryCachingDependency=$dependency;
    $this->queryCachingCount=$queryCount;
    return $this;
}

Sets the parameters about query caching. This method can be used to enable or disable query caching. By setting the $duration parameter to be 0, the query caching will be disabled. Otherwise, query results of the new SQL statements executed next will be saved in cache and remain valid for the specified duration. If the same query is executed again, the result may be fetched from cache directly without actually executing the SQL statement.

close() method

protected void close()

protected function close()
{
    Yii::trace('Closing DB connection','system.db.CDbConnection');
    $this->_pdo=null;
    $this->_active=false;
    $this->_schema=null;
}

Closes the currently active DB connection. It does nothing if the connection is already closed.

createCommand() method

public CDbCommand createCommand(mixed $query=NULL)

public function createCommand($query=null)
{
    $this->setActive(true);
    return new CDbCommand($this,$query);
}

Creates a command for execution.

createPdoInstance() method

protected PDO createPdoInstance()

protected function createPdoInstance()
{
    $pdoClass=$this->pdoClass;
    if(($driver=$this->getDriverName())!==null)
    {
        if($driver==='mssql' || $driver==='dblib')
            $pdoClass='CMssqlPdoAdapter';
        elseif($driver==='sqlsrv')
            $pdoClass='CMssqlSqlsrvPdoAdapter';
    }

    if(!class_exists($pdoClass))
        throw new CDbException(Yii::t('yii','CDbConnection is unable to find PDO class "{className}". Make sure PDO is installed correctly.',
            array('{className}'=>$pdoClass)));

    @$instance=new $pdoClass($this->connectionString,$this->username,$this->password,$this->_attributes);

    if(!$instance)
        throw new CDbException(Yii::t('yii','CDbConnection failed to open the DB connection.'));

    return $instance;
}

Creates the PDO instance. When some functionalities are missing in the pdo driver, we may use an adapter class to provide them.

getActive() method

public boolean getActive()

public function getActive()
{
    return $this->_active;
}

Returns whether the DB connection is established.

getAttribute() method

public mixed getAttribute(integer $name)

public function getAttribute($name)
{
    $this->setActive(true);
    return $this->_pdo->getAttribute($name);
}

Obtains a specific DB connection attribute information.

getAttributes() method (available since v1.1.7)

public array getAttributes()

public function getAttributes()
{
    return $this->_attributes;
}

Returns the attributes that are previously explicitly set for the DB connection.

getAutoCommit() method

public boolean getAutoCommit()

public function getAutoCommit()
{
    return $this->getAttribute(PDO::ATTR_AUTOCOMMIT);
}

Returns whether creating or updating a DB record will be automatically committed. Some DBMS (such as sqlite) may not support this feature.

getAvailableDrivers() method

public static array getAvailableDrivers()

public static function getAvailableDrivers()
{
    return PDO::getAvailableDrivers();
}

Returns a list of available PDO drivers.

getClientVersion() method

public string getClientVersion()

public function getClientVersion()
{
    return $this->getAttribute(PDO::ATTR_CLIENT_VERSION);
}

Returns the version information of the DB driver.

getColumnCase() method

public mixed getColumnCase()

public function getColumnCase()
{
    return $this->getAttribute(PDO::ATTR_CASE);
}

Returns the case of the column names

getCommandBuilder() method

public CDbCommandBuilder getCommandBuilder()

public function getCommandBuilder()
{
    return $this->getSchema()->getCommandBuilder();
}

Returns the SQL command builder for the current DB connection.

getConnectionStatus() method

public string getConnectionStatus()

public function getConnectionStatus()
{
    return $this->getAttribute(PDO::ATTR_CONNECTION_STATUS);
}

Returns the status of the connection. Some DBMS (such as sqlite) may not support this feature.

getCurrentTransaction() method

public CDbTransaction getCurrentTransaction()

public function getCurrentTransaction()
{
    if($this->_transaction!==null)
    {
        if($this->_transaction->getActive())
            return $this->_transaction;
    }
    return null;
}

Returns the currently active transaction.

getDriverName() method

public string getDriverName()

public function getDriverName()
{
    if($this->_driverName!==null)
        return $this->_driverName;
    elseif(($pos=strpos($this->connectionString,':'))!==false)
        return $this->_driverName=strtolower(substr($this->connectionString,0,$pos));
    //return $this->getAttribute(PDO::ATTR_DRIVER_NAME);
}

Returns the name of the DB driver.

getLastInsertID() method

public string getLastInsertID(string $sequenceName='')

public function getLastInsertID($sequenceName='')
{
    $this->setActive(true);
    return $this->_pdo->lastInsertId($sequenceName);
}

Returns the ID of the last inserted row or sequence value.

getNullConversion() method

public mixed getNullConversion()

public function getNullConversion()
{
    return $this->getAttribute(PDO::ATTR_ORACLE_NULLS);
}

Returns how the null and empty strings are converted.

getPdoInstance() method

public PDO getPdoInstance()

public function getPdoInstance()
{
    return $this->_pdo;
}

Returns the PDO instance.

getPdoType() method

public integer getPdoType(string $type)

public function getPdoType($type)
{
    static $map=array
    (
        'boolean'=>PDO::PARAM_BOOL,
        'integer'=>PDO::PARAM_INT,
        'string'=>PDO::PARAM_STR,
        'resource'=>PDO::PARAM_LOB,
        'NULL'=>PDO::PARAM_NULL,
    );
    return isset($map[$type]) ? $map[$type] : PDO::PARAM_STR;
}

Determines the PDO type for the specified PHP type.

getPersistent() method

public boolean getPersistent()

public function getPersistent()
{
    return $this->getAttribute(PDO::ATTR_PERSISTENT);
}

Returns whether the connection is persistent or not. Some DBMS (such as sqlite) may not support this feature.

getPrefetch() method

public boolean getPrefetch()

public function getPrefetch()
{
    return $this->getAttribute(PDO::ATTR_PREFETCH);
}

Returns whether the connection performs data prefetching.

getSchema() method

public CDbSchema getSchema()

public function getSchema()
{
    if($this->_schema!==null)
        return $this->_schema;
    else
    {
        $driver=$this->getDriverName();
        if(isset($this->driverMap[$driver]))
            return $this->_schema=Yii::createComponent($this->driverMap[$driver], $this);
        else
            throw new CDbException(Yii::t('yii','CDbConnection does not support reading schema for {driver} database.',
                array('{driver}'=>$driver)));
    }
}

Returns the database schema for the current connection

getServerInfo() method

public string getServerInfo()

public function getServerInfo()
{
    return $this->getAttribute(PDO::ATTR_SERVER_INFO);
}

Returns the information of DBMS server.

getServerVersion() method

public string getServerVersion()

public function getServerVersion()
{
    return $this->getAttribute(PDO::ATTR_SERVER_VERSION);
}

Returns the version information of DBMS server.

getStats() method

public array getStats()

public function getStats()
{
    $logger=Yii::getLogger();
    $timings=$logger->getProfilingResults(null,'system.db.CDbCommand.query');
    $count=count($timings);
    $time=array_sum($timings);
    $timings=$logger->getProfilingResults(null,'system.db.CDbCommand.execute');
    $count+=count($timings);
    $time+=array_sum($timings);
    return array($count,$time);
}

Returns the statistical results of SQL executions. The results returned include the number of SQL statements executed and the total time spent. In order to use this method, enableProfiling has to be set true.

getTimeout() method

public integer getTimeout()

public function getTimeout()
{
    return $this->getAttribute(PDO::ATTR_TIMEOUT);
}

Returns the timeout settings for the connection.

init() method

public void init()

public function init()
{
    parent::init();
    if($this->autoConnect)
        $this->setActive(true);
}

Initializes the component. This method is required by IApplicationComponent and is invoked by application when the CDbConnection is used as an application component. If you override this method, make sure to call the parent implementation so that the component can be marked as initialized.

initConnection() method

protected void initConnection(PDO $pdo)

protected function initConnection($pdo)
{
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    if($this->emulatePrepare!==null && constant('PDO::ATTR_EMULATE_PREPARES'))
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,$this->emulatePrepare);
    if($this->charset!==null)
    {
        $driver=strtolower($pdo->getAttribute(PDO::ATTR_DRIVER_NAME));
        if(in_array($driver,array('pgsql','mysql','mysqli')))
            $pdo->exec('SET NAMES '.$pdo->quote($this->charset));
    }
    if($this->initSQLs!==null)
    {
        foreach($this->initSQLs as $sql)
            $pdo->exec($sql);
    }
}

Initializes the open db connection. This method is invoked right after the db connection is established. The default implementation is to set the charset for MySQL, MariaDB and PostgreSQL database connections.

open() method

protected void open()

protected function open()
{
    if($this->_pdo===null)
    {
        if(empty($this->connectionString))
            throw new CDbException('CDbConnection.connectionString cannot be empty.');
        try
        {
            Yii::trace('Opening DB connection','system.db.CDbConnection');
            $this->_pdo=$this->createPdoInstance();
            $this->initConnection($this->_pdo);
            $this->_active=true;
        }
        catch(PDOException $e)
        {
            if(YII_DEBUG)
            {
                throw new CDbException('CDbConnection failed to open the DB connection: '.
                    $e->getMessage(),(int)$e->getCode(),$e->errorInfo);
            }
            else
            {
                Yii::log($e->getMessage(),CLogger::LEVEL_ERROR,'exception.CDbException');
                throw new CDbException('CDbConnection failed to open the DB connection.',(int)$e->getCode(),$e->errorInfo);
            }
        }
    }
}

Opens DB connection if it is currently not

quoteColumnName() method

public string quoteColumnName(string $name)

public function quoteColumnName($name)
{
    return $this->getSchema()->quoteColumnName($name);
}

Quotes a column name for use in a query. If the column name contains prefix, the prefix will also be properly quoted.

quoteTableName() method

public string quoteTableName(string $name)

public function quoteTableName($name)
{
    return $this->getSchema()->quoteTableName($name);
}

Quotes a table name for use in a query. If the table name contains schema prefix, the prefix will also be properly quoted.

quoteValue() method

public string quoteValue(string $str)

public function quoteValue($str)
{
    if(is_int($str) || is_float($str))
        return $str;

    $this->setActive(true);
    if(($value=$this->_pdo->quote($str))!==false)
        return $value;
    else  // the driver doesn't support quote (e.g. oci)
        return "'" . addcslashes(str_replace("'", "''", $str), "\000\n\r\\\032") . "'";
}

Quotes a string value for use in a query.

quoteValueWithType() method (available since v1.1.18)

public string quoteValueWithType(mixed $value, integer $type)

public function quoteValueWithType($value, $type)
{
    $this->setActive(true);
    if(($quoted=$this->_pdo->quote($value, $type))!==false)
        return $quoted;
    else  // the driver doesn't support quote (e.g. oci)
        return "'" . addcslashes(str_replace("'", "''", $value), "\000\n\r\\\032") . "'";
}

Quotes a value for use in a query using a given type.

setActive() method

public void setActive(boolean $value)

public function setActive($value)
{
    if($value!=$this->_active)
    {
        if($value)
            $this->open();
        else
            $this->close();
    }
}

Open or close the DB connection.

setAttribute() method

public void setAttribute(integer $name, mixed $value)

public function setAttribute($name,$value)
{
    if($this->_pdo instanceof PDO)
        $this->_pdo->setAttribute($name,$value);
    else
        $this->_attributes[$name]=$value;
}

Sets an attribute on the database connection.

setAttributes() method (available since v1.1.7)

public void setAttributes(array $values)

public function setAttributes($values)
{
    foreach($values as $name=>$value)
        $this->_attributes[$name]=$value;
}

Sets a set of attributes on the database connection.

setAutoCommit() method

public void setAutoCommit(boolean $value)

public function setAutoCommit($value)
{
    $this->setAttribute(PDO::ATTR_AUTOCOMMIT,$value);
}

Sets whether creating or updating a DB record will be automatically committed. Some DBMS (such as sqlite) may not support this feature.

setColumnCase() method

public void setColumnCase(mixed $value)

public function setColumnCase($value)
{
    $this->setAttribute(PDO::ATTR_CASE,$value);
}

Sets the case of the column names.

setDriverName() method (available since v1.1.16)

public void setDriverName(string $driverName)

public function setDriverName($driverName)
{
    $this->_driverName=strtolower($driverName);
}

Changes the name of the DB driver. Overrides value extracted from the connectionString, which is behavior by default.

setNullConversion() method

public void setNullConversion(mixed $value)

public function setNullConversion($value)
{
    $this->setAttribute(PDO::ATTR_ORACLE_NULLS,$value);
}

Sets how the null and empty strings are converted.

setPersistent() method

public void setPersistent(boolean $value)

public function setPersistent($value)
{
    return $this->setAttribute(PDO::ATTR_PERSISTENT,$value);
}

Sets whether the connection is persistent or not. Some DBMS (such as sqlite) may not support this feature.