Versions
Look up a class, method, property or event

CDbConnection

Package system.db
Inheritance class CDbConnection » CApplicationComponent » CComponent
Implements IApplicationComponent
Since 1.0
Version $Id: CDbConnection.php 1464 2009-10-17 10:48:19Z qiang.xue $
CDbConnection represents a connection to a database.

CDbConnection works together with CDbCommand, CDbDataReader and CDbTransaction to provide data access to various DBMS in a common set of APIs. They are a thin wrapper of the PDO PHP extension.

To establish a connection, set active to true after specifying connectionString, username and password.

The following example shows how to create a CDbConnection instance and establish the actual connection:
$connection=new CDbConnection($dsn,$username,$password);
$connection->active=true;


After the DB connection is established, one can execute an SQL statement like the following:
$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) ...


One can do prepared SQL execution and bind parameters to the prepared SQL:
$command=$connection->createCommand($sqlStatement);
$command->bindParam($name1,$value1);
$command->bindParam($name2,$value2);
$command->execute();


To use transaction, do like the following:
$transaction=$connection->beginTransaction();
try
{
   $connection->createCommand($sql1)->execute();
   $connection->createCommand($sql2)->execute();
   //.... other SQL executions
   $transaction->commit();
}
catch(Exception $e)
{
   $transaction->rollBack();
}


CDbConnection also provides a set of methods to support setting and querying of certain DBMS attributes, such as nullConversion.

Since CDbConnection implements the interface IApplicationComponent, it can be used as an application component and be configured in application configuration, like the following,
array(
    'components'=>array(
        'db'=>array(
            'class'=>'CDbConnection',
            'connectionString'=>'sqlite:path/to/dbfile',
        ),
    ),
)

Public Properties

Hide inherited properties

PropertyTypeDescriptionDefined By
active boolean whether the DB connection is established CDbConnection
autoCommit boolean whether creating or updating a DB record will be automatically committed. CDbConnection
autoConnect boolean whether the database connection should be automatically established the component is being initialized. CDbConnection
behaviors array the behaviors that should be attached to this component. CApplicationComponent
charset string the charset used for database connection. CDbConnection
clientVersion string the version information of the DB driver CDbConnection
columnCase mixed the case of the column names CDbConnection
commandBuilder CDbCommandBuilder Returns the SQL command builder for the current DB connection. CDbConnection
connectionStatus string the status of the connection Some DBMS (such as sqlite) may not support this feature. CDbConnection
connectionString string The Data Source Name, or DSN, contains the information required to connect to the database. CDbConnection
currentTransaction CDbTransaction the currently active transaction. CDbConnection
driverName string name of the DB driver CDbConnection
emulatePrepare boolean whether to turn on prepare emulation. CDbConnection
enableParamLogging boolean whether to log the values that are bound to a prepare SQL statement. CDbConnection
enableProfiling boolean whether to enable profiling the SQL statements being executed. CDbConnection
isInitialized boolean whether this application component has been initialized (i. CApplicationComponent
lastInsertID string Returns the ID of the last inserted row or sequence value. CDbConnection
nullConversion mixed how the null and empty strings are converted CDbConnection
password string the password for establishing DB connection. CDbConnection
pdoInstance PDO the PDO instance, null if the connection is not established yet CDbConnection
persistent boolean whether the connection is persistent or not Some DBMS (such as sqlite) may not support this feature. CDbConnection
prefetch boolean whether the connection performs data prefetching CDbConnection
schema CDbSchema the database schema for the current connection CDbConnection
schemaCacheID string the ID of the cache application component that is used to cache the table metadata. CDbConnection
schemaCachingDuration integer number of seconds that table metadata can remain valid in cache. CDbConnection
schemaCachingExclude array list of tables whose metadata should NOT be cached. CDbConnection
serverInfo string the information of DBMS server CDbConnection
serverVersion string the version information of DBMS server CDbConnection
stats array Returns the statistical results of SQL executions. CDbConnection
timeout int timeout settings for the connection CDbConnection
username string the username for establishing DB connection. CDbConnection

Public Methods

Hide inherited methods

MethodDescriptionDefined By
__call() Calls the named method which is not a class method. CComponent
__construct() Constructor. CDbConnection
__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() Close the connection when serializing. CDbConnection
__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
beginTransaction() Starts a transaction. CDbConnection
canGetProperty() Determines whether a property can be read. CComponent
canSetProperty() Determines whether a property can be set. CComponent
createCommand() Creates a command for execution. CDbConnection
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
getActive() CDbConnection
getAttribute() Obtains a specific DB connection attribute information. CDbConnection
getAutoCommit() CDbConnection
getAvailableDrivers() CDbConnection
getClientVersion() CDbConnection
getColumnCase() CDbConnection
getCommandBuilder() Returns the SQL command builder for the current DB connection. CDbConnection
getConnectionStatus() CDbConnection
getCurrentTransaction() CDbConnection
getDriverName() CDbConnection
getEventHandlers() Returns the list of attached event handlers for an event. CComponent
getIsInitialized() CApplicationComponent
getLastInsertID() Returns the ID of the last inserted row or sequence value. CDbConnection
getNullConversion() CDbConnection
getPdoInstance() CDbConnection
getPdoType() Determines the PDO type for the specified PHP type. CDbConnection
getPersistent() CDbConnection
getPrefetch() CDbConnection
getSchema() CDbConnection
getServerInfo() CDbConnection
getServerVersion() CDbConnection
getStats() Returns the statistical results of SQL executions. CDbConnection
getTimeout() CDbConnection
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
init() Initializes the component. CDbConnection
quoteColumnName() Quotes a column name for use in a query. CDbConnection
quoteTableName() Quotes a table name for use in a query. CDbConnection
quoteValue() Quotes a string value for use in a query. CDbConnection
raiseEvent() Raises an event. CComponent
setActive() Open or close the DB connection. CDbConnection
setAttribute() Sets an attribute on the database connection. CDbConnection
setAutoCommit() CDbConnection
setColumnCase() CDbConnection
setNullConversion() CDbConnection
setPersistent() CDbConnection

Protected Methods

Hide inherited methods

MethodDescriptionDefined By
close() Closes the currently active DB connection. CDbConnection
createPdoInstance() Creates the PDO instance. CDbConnection
initConnection() Initializes the open db connection. CDbConnection
open() Opens DB connection if it is currently not CDbConnection

Property Details

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

whether the DB connection is established

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

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 and PostgreSQL databases. Defaults to null, meaning using default charset as specified by the database.

clientVersion property read-only
public string getClientVersion()

the version information of the DB driver

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

the case of the column names

commandBuilder property read-only (available since v1.0.4)

Returns the SQL command builder for the current DB connection.

connectionStatus property read-only
public string getConnectionStatus()

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

the currently active transaction. Null if no active transaction.

driverName property read-only
public string getDriverName()

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.

enableParamLogging property (available since v1.0.5)
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 (available since v1.0.6)
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.

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)

how the null and empty strings are converted

password property
public string $password;

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

pdoInstance property read-only
public PDO getPdoInstance()

the PDO instance, null if the connection is not established yet

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

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

prefetch property read-only
public boolean getPrefetch()

whether the connection performs data prefetching

schema property read-only

the database schema for the current connection

schemaCacheID property (available since v1.0.10)
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()

the information of DBMS server

serverVersion property read-only
public string getServerVersion()

the version information of DBMS server

stats property read-only (available since v1.0.6)
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.

timeout property read-only
public int getTimeout()

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='')
$dsn string The Data Source Name, or DSN, contains the information required to connect to the database.
$username string The user name for the DSN string.
$password string The password for the DSN string.

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 void __sleep()

Close the connection when serializing.

beginTransaction() method
public CDbTransaction beginTransaction()
{return} CDbTransaction the transaction initiated

Starts a transaction.

close() method
protected void close()

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

createCommand() method
public CDbCommand createCommand(string $sql)
$sql string SQL statement associated with the new command.
{return} CDbCommand the DB command

Creates a command for execution.

createPdoInstance() method (available since v1.0.4)
protected PDO createPdoInstance()
{return} PDO the pdo instance

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

getActive() method
public boolean getActive()
{return} boolean whether the DB connection is established

getAttribute() method
public mixed getAttribute(int $name)
$name int the attribute to be queried
{return} mixed the corresponding attribute information

Obtains a specific DB connection attribute information.

getAutoCommit() method
public boolean getAutoCommit()
{return} boolean 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()
{return} array list of available PDO drivers

getClientVersion() method
public string getClientVersion()
{return} string the version information of the DB driver

getColumnCase() method
public mixed getColumnCase()
{return} mixed the case of the column names

getCommandBuilder() method (available since v1.0.4)
public CDbCommandBuilder getCommandBuilder()
{return} CDbCommandBuilder the command builder

Returns the SQL command builder for the current DB connection.

getConnectionStatus() method
public string getConnectionStatus()
{return} string the status of the connection Some DBMS (such as sqlite) may not support this feature.

getCurrentTransaction() method
public CDbTransaction getCurrentTransaction()
{return} CDbTransaction the currently active transaction. Null if no active transaction.

getDriverName() method
public string getDriverName()
{return} string name of the DB driver

getLastInsertID() method
public string getLastInsertID(string $sequenceName='')
$sequenceName string name of the sequence object (required by some DBMS)
{return} string the row ID of the last row inserted, or the last value retrieved from the sequence object

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

getNullConversion() method
public mixed getNullConversion()
{return} mixed how the null and empty strings are converted

getPdoInstance() method
public PDO getPdoInstance()
{return} PDO the PDO instance, null if the connection is not established yet

getPdoType() method
public integer getPdoType(string $type)
$type string The PHP type (obtained by gettype() call).
{return} integer the corresponding PDO type

Determines the PDO type for the specified PHP type.

getPersistent() method
public boolean getPersistent()
{return} boolean whether the connection is persistent or not Some DBMS (such as sqlite) may not support this feature.

getPrefetch() method
public boolean getPrefetch()
{return} boolean whether the connection performs data prefetching

getSchema() method
public CDbSchema getSchema()
{return} CDbSchema the database schema for the current connection

getServerInfo() method
public string getServerInfo()
{return} string the information of DBMS server

getServerVersion() method
public string getServerVersion()
{return} string the version information of DBMS server

getStats() method (available since v1.0.6)
public array getStats()
{return} array the first element indicates the number of SQL statements executed, and the second element the total time spent in SQL execution.

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 int getTimeout()
{return} int timeout settings for the connection

init() method
public void init()

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)
$pdo PDO the PDO instance

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 and PostgreSQL database connections.

open() method
protected void open()

Opens DB connection if it is currently not

quoteColumnName() method
public string quoteColumnName(string $name)
$name string column name
{return} string the properly quoted column name

Quotes a column name for use in a query.

quoteTableName() method
public string quoteTableName(string $name)
$name string table name
{return} string the properly quoted table name

Quotes a table name for use in a query.

quoteValue() method
public string quoteValue(string $str)
$str string string to be quoted
{return} string the properly quoted string

Quotes a string value for use in a query.

setActive() method
public void setActive(boolean $value)
$value boolean whether to open or close DB connection

Open or close the DB connection.

setAttribute() method
public void setAttribute(int $name, mixed $value)
$name int the attribute to be set
$value mixed the attribute value

Sets an attribute on the database connection.

setAutoCommit() method
public void setAutoCommit(boolean $value)
$value boolean 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)
$value mixed the case of the column names

setNullConversion() method
public void setNullConversion(mixed $value)
$value mixed how the null and empty strings are converted

setPersistent() method
public void setPersistent(boolean $value)
$value boolean whether the connection is persistent or not Some DBMS (such as sqlite) may not support this feature.

Be the first person to leave a comment

Please to leave your comment.