Data Access Objects (DAO)

Data Access Objects (DAO) separates a data resource's client interface from its data access mechanisms. It adapts a specific data resource's access API to a generic client interface. As a result, data access mechanisms can be changed independently of the code that uses the data.

Since version 3.1, PRADO starts to provide a DAO that is a thin wrap around PHP Data Objects (PDO). Although PDO has a nice feature set and good APIs, we choose to implement the PRADO DAO on top of PDO because the PRADO DAO classes are component classes and are thus configurable in a PRADO application. Users can use these DAO classes in a more PRADO-preferred way.

The PRADO DAO mainly consists of the following four classes (in contrast to PDO which uses only two classes, PDO and PDOStatement):

TDbConnection - represents a connection to a database.
TDbCommand - represents an SQL statement to execute against a database.
TDbDataReader - represents a forward-only stream of rows from a query result set.
TDbTransaction - represents a DB transaction.

In the following, we introduce the usage of PRADO DAO in different scenarios.

Establishing Database Connection

To establish a database connection, one creates a TDbConnection instance and activate it. A data source name (DSN) is needed to specify the information required to connect to the database. The database username and password may need to be supplied to establish the connection.

$connection=new TDbConnection($dsn,$username,$password); // call setAttribute() to pass in additional connection parameters // $connection->Persistent=true; // use persistent connection $connection->Active=true; // connection is established .... $connection->Active=false; // connection is closed

Complete specification of DSN may be found in the PDO documentation. Below is a list of commonly used DSN formats:

MySQL - mysql:host=localhost;dbname=test
SQLite - sqlite:/path/to/dbfile
ODBC - odbc:SAMPLE

In case any error occurs when establishing the connection (such as bad DSN or username/password), a TDbException will be raised.

Executing SQL Statements

Once a database connection is established, SQL statements can be executed through TDbCommand. One creates a TDbCommand by calling TDbConnection.createCommand() with the specified SQL statement:

$command=$connection->createCommand($sqlStatement); // if needed, the SQL statement may be updated as follows: $command->Text=$newSqlStatement;

An SQL statement is executed via TDbCommand in one of the following two ways:

execute() - performs a non-query SQL statement, such as INSERT, UPDATE and DELETE. If successful, it returns the number of rows that are affected by the execution.
query() - performs an SQL statement that returns rows of data, such as SELECT. If successful, it returns a TDbDataReader instance from which one can fetch the resulting rows of data.

$affectedRowCount=$command->execute(); // execute the non-query SQL $dataReader=$command->query(); // execute a query SQL $row=$command->queryRow(); // execute a query SQL and return the first row of result $value=$command->queryScalar(); // execute a query SQL and return the first column value

In case an error occurs during the execution of SQL statements, a TDbException will be raised.

Fetching Query Results

After TDbCommand.query() generates the TDbDataReader instance, one can retrieve rows of resulting data by calling TDbDataReader.read() repeatedly. One can also use TDbDataReader in PHP's foreach language construct to retrieve row by row.

// calling read() repeatedly until it returns false while(($row=$dataReader->read())!==false) { ... } // using foreach to traverse through every row of data foreach($dataReader as $row) { ... } // retrieving all rows at once in a single array $rows=$dataReader->readAll();

Using Transactions

When an application executes a few queries, each reading and/or writing information in the database, it is important to be sure that the database is not left with only some of the queries carried out. A transaction, represented as a TDbTransaction instance in PRADO, may be initiated in this case:

Begin the transaction.
Execute queries one by one. Any updates to the database are not visible to the outside world.
Commit the transaction. Updates become visible if the transaction is successful.
If one of the queries fails, the entire transaction is rolled back.

$transaction=$connection->beginTransaction(); try { $connection->createCommand($sql1)->execute(); $connection->createCommand($sql2)->execute(); //.... other SQL executions $transaction->commit(); } catch(Exception $e) // an exception is raised if a query fails will be raised { $transaction->rollBack(); }

Binding Parameters

To avoid SQL injection attacks and to improve performance of executing repeatedly used SQL statements, one can "prepare" an SQL statement with optional parameter placeholders that are to be replaced with the actual parameters during the parameter binding process.

The parameter placeholders can be either named (represented as unique tokens) or unnamed (represented as question marks). Call TDbCommand.bindParameter() or TDbCommand.bindValue() to replace these placeholders with the actual parameters. The parameters do not need to be quoted: the underlying database driver does it for you. Parameter binding must be done before the SQL statement is executed.

// an SQL with two placeholders ":username" and ":email" $sql="INSERT INTO users(username, email) VALUES(:username,:email)"; $command=$connection->createCommand($sql); // replace the placeholder ":username" with the actual username value $command->bindParameter(":username",$username,PDO::PARAM_STR); // replace the placeholder ":email" with the actual email value $command->bindParameter(":email",$email,PDO::PARAM_STR); $command->execute(); // insert another row with a new set of parameters $command->bindParameter(":username",$username2,PDO::PARAM_STR); $command->bindParameter(":email",$email2,PDO::PARAM_STR); $command->execute();

The methods bindParameter() and bindValue() are very similar. The only difference is that the former binds a parameter with a PHP variable reference while the latter with a value. For parameters that represent large block of data memory, the former is preferred for performance consideration.

For more details about binding parameters, see the relevant PHP documentation.

Binding Columns

When fetching query results, one can also bind columns with PHP variables so that they are automatically populated with the latest data each time a row is fetched.

$sql="SELECT username, email FROM users"; $dataReader=$connection->createCommand($sql)->query(); // bind the 1st column (username) with the $username variable $dataReader->bindColumn(1,$username); // bind the 2nd column (email) with the $email variable $dataReader->bindColumn(2,$email); while($dataReader->read()!==false) { // $username and $email contain the username and email in the current row }