TDbConnection represents a connection to a database.
TDbConnection works together with {@link TDbCommand}, {@link TDbDataReader}
and {@link TDbTransaction} to provide data access to various DBMS
in a common set of APIs. They are a thin wrapper of the {@link http://www.php.net/manual/en/ref.pdo.php PDO}
PHP extension.
To establish a connection, set {@link setActive Active} to true after
specifying {@link setConnectionString ConnectionString}, {@link setUsername Username}
and {@link setPassword Password}.
Since 3.1.2, the connection charset can be set (for MySQL and PostgreSQL databases only)
using the {@link setCharset Charset} property. The value of this property is database dependant.
e.g. for mysql, you can use 'latin1' for cp1252 West European, 'utf8' for unicode, ...
The following example shows how to create a TDbConnection instance and establish
the actual connection:
$connection=new TDbConnection($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->bindParameter($name1,$value1);
$command->bindParameter($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();
}
TDbConnection provides a set of methods to support setting and querying
of certain DBMS attributes, such as {@link getNullConversion NullConversion}.
protected function getMysqlSqlMapManager()
{
static $conn;
static $sqlMapManager;
if (Prado::getApplication() === null) {
Prado::setApplication(new TApplication(dirname(__FILE__) . '/app'));
}
if ($conn === null) {
$conn = new TDbConnection('mysql:host=localhost;dbname=prado_unitest', 'prado_unitest', 'prado_unitest');
}
$conn->setActive(true);
if ($sqlMapManager === null) {
$sqlMapManager = new TSqlMapManager($conn);
$sqlMapManager->configureXml(dirname(__FILE__) . '/DynamicParameterTestMap.xml');
}
return $sqlMapManager;
}
/**
* Creates the DB connection.
* @param string the module ID for TDataSourceConfig
* @return TDbConnection the created DB connection
* @throws TConfigurationException if module ID is invalid or empty
*/
protected function createDbConnection()
{
if ($this->_connID !== '') {
$config = $this->getApplication()->getModule($this->_connID);
if ($config instanceof TDataSourceConfig) {
return $config->getDbConnection();
} else {
throw new TConfigurationException('dblogroute_connectionid_invalid', $this->_connID);
}
} else {
$db = new TDbConnection();
// default to SQLite3 database
$dbFile = $this->getApplication()->getRuntimePath() . '/sqlite3.log';
$db->setConnectionString('sqlite:' . $dbFile);
return $db;
}
}
/**
* Creates the DB connection.
* @param string the module ID for TDataSourceConfig
* @return TDbConnection the created DB connection
* @throws TConfigurationException if module ID is invalid or empty
*/
protected function createDbConnection()
{
if ($this->_connID !== '') {
$config = $this->getApplication()->getModule($this->_connID);
if ($config instanceof TDataSourceConfig) {
return $config->getDbConnection();
} else {
throw new TConfigurationException('dbcache_connectionid_invalid', $this->_connID);
}
} else {
$db = new TDbConnection();
if ($this->_connectionString !== '') {
$db->setConnectionString($this->_connectionString);
if ($this->_username !== '') {
$db->setUsername($this->_username);
}
if ($this->_password !== '') {
$db->setPassword($this->_password);
}
} else {
// default to SQLite3 database
$dbFile = $this->getApplication()->getRuntimePath() . '/sqlite3.cache';
$db->setConnectionString('sqlite:' . $dbFile);
}
return $db;
}
}
/**
* Returns table information for table in the database connection.
* @param TDbConnection database connection
* @param string table name
* @return TDbTableInfo table details.
*/
public function getTableInfo(TDbConnection $connection, $tableName)
{
$connStr = $connection->getConnectionString();
$key = $connStr . $tableName;
if (!isset($this->_tables[$key])) {
//call this first to ensure that unserializing the cache
//will find the correct driver dependent classes.
if (!isset($this->_meta[$connStr])) {
$this->_meta[$connStr] = TDbMetaData::getInstance($connection);
}
$tableInfo = null;
if (($cache = $this->getManager()->getCache()) !== null) {
$tableInfo = $cache->get($key);
}
if (empty($tableInfo)) {
$tableInfo = $this->_meta[$connStr]->getTableInfo($tableName);
if ($cache !== null) {
$cache->set($key, $tableInfo);
}
}
$this->_tables[$key] = $tableInfo;
}
return $this->_tables[$key];
}