CDbConnection
包 | system.db |
---|---|
继承 | class CDbConnection » CApplicationComponent » CComponent |
实现 | IApplicationComponent |
可用自 | 1.0 |
版本 | $Id$ |
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', ), ), )
公共属性
属性 | 类型 | 描述 | 被定义在 |
---|---|---|---|
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 |
公共方法
方法 | 描述 | 被定义在 |
---|---|---|
__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 |
受保护的方法
方法 | 描述 | 被定义在 |
---|---|---|
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 |
属性详情
whether the DB connection is established
whether creating or updating a DB record will be automatically committed. Some DBMS (such as sqlite) may not support this feature.
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.
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.
the version information of the DB driver
the case of the column names
Returns the SQL command builder for the current DB connection.
the status of the connection Some DBMS (such as sqlite) may not support this feature.
The Data Source Name, or DSN, contains the information required to connect to the database.
the currently active transaction. Null if no active transaction.
name of the DB driver
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.
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.
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.
Returns the ID of the last inserted row or sequence value.
how the null and empty strings are converted
the password for establishing DB connection. Defaults to empty string.
the PDO instance, null if the connection is not established yet
whether the connection is persistent or not Some DBMS (such as sqlite) may not support this feature.
whether the connection performs data prefetching
the database schema for the current connection
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.
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.
list of tables whose metadata should NOT be cached. Defaults to empty array.
the information of DBMS server
the version information of DBMS server
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 settings for the connection
the username for establishing DB connection. Defaults to empty string.
方法详情
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.
public void __sleep()
|
Close the connection when serializing.
public CDbTransaction beginTransaction()
| ||
{return} | CDbTransaction | the transaction initiated |
Starts a transaction.
protected void close()
|
Closes the currently active DB connection. It does nothing if the connection is already closed.
public CDbCommand createCommand(string $sql)
| ||
$sql | string | SQL statement associated with the new command. |
{return} | CDbCommand | the DB command |
Creates a command for execution.
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.
public boolean getActive()
| ||
{return} | boolean | whether the DB connection is established |
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.
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. |
public static array getAvailableDrivers()
| ||
{return} | array | list of available PDO drivers |
public string getClientVersion()
| ||
{return} | string | the version information of the DB driver |
public mixed getColumnCase()
| ||
{return} | mixed | the case of the column names |
public CDbCommandBuilder getCommandBuilder()
| ||
{return} | CDbCommandBuilder | the command builder |
Returns the SQL command builder for the current DB connection.
public string getConnectionStatus()
| ||
{return} | string | the status of the connection Some DBMS (such as sqlite) may not support this feature. |
public CDbTransaction getCurrentTransaction()
| ||
{return} | CDbTransaction | the currently active transaction. Null if no active transaction. |
public string getDriverName()
| ||
{return} | string | name of the DB driver |
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.
public mixed getNullConversion()
| ||
{return} | mixed | how the null and empty strings are converted |
public PDO getPdoInstance()
| ||
{return} | PDO | the PDO instance, null if the connection is not established yet |
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.
public boolean getPersistent()
| ||
{return} | boolean | whether the connection is persistent or not Some DBMS (such as sqlite) may not support this feature. |
public boolean getPrefetch()
| ||
{return} | boolean | whether the connection performs data prefetching |
public CDbSchema getSchema()
| ||
{return} | CDbSchema | the database schema for the current connection |
public string getServerInfo()
| ||
{return} | string | the information of DBMS server |
public string getServerVersion()
| ||
{return} | string | the version information of DBMS server |
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.
public int getTimeout()
| ||
{return} | int | timeout settings for the connection |
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.
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.
protected void open()
|
Opens DB connection if it is currently not
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.
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.
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.
public void setActive(boolean $value)
| ||
$value | boolean | whether to open or close DB connection |
Open or close the DB connection.
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.
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. |
public void setColumnCase(mixed $value)
| ||
$value | mixed | the case of the column names |
public void setNullConversion(mixed $value)
| ||
$value | mixed | how the null and empty strings are converted |
public void setPersistent(boolean $value)
| ||
$value | boolean | whether the connection is persistent or not Some DBMS (such as sqlite) may not support this feature. |