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]; }